title: join()->where() in Laravel's Eloquent ORM
body: <p>I'm essentially building a multi-site site under a single domain using subdomains for each account. Each account has a unique set of usernames, but not unique across all sites. Therefore, there could be a 'tom' on account 1 and a 'tom' on account 2, but not 2 tom's on any single account.</p>
  
  <p>My users table has a column specifying the account_id and my controller has the subdomain. So, when querying a user, how can I ensure that the users <code>account_id</code> matches an accounts <code>id</code> where the accounts <code>id = users.account_id</code> and <code>accounts.subdomain = $subdomain</code>?</p>
  
  <p>Here is what I'm trying:</p>
  
  <pre><code>$user = User::whereUsername($username)
      -&gt;leftJoin('accounts', 'accounts.id', '=', 'users.account_id')
      -&gt;where('accounts.id', '=', 'users.account_id')
      -&gt;where('accounts.subdomain', '=', $subdomain)
      -&gt;firstOrFail();
  </code></pre>
  
  <p>The problem with this is that it's taking <code>users.account_id</code> as a literal string and not a table reference.</p>
  
  <p>Secondly, in relation to this setup, is there a better way to approach this so that anytime I'm on a subdomain it will pull only relevant data without me have to be repetitive?</p>
  
  <p><strong>Update:</strong>
  I managed to get the query working by using DB::raw() but am curious if there is a better way. </p>
  
  <pre><code>$user = User::whereUsername($username)
      -&gt;leftJoin('accounts', 'accounts.id', '=', 'users.account_id')
      -&gt;where('accounts.id', '=', DB::raw('users.account_id'))
      -&gt;where('accounts.subdomain', '=', $subdomain)
      -&gt;firstOrFail();
  </code></pre>
  
  <p>Also, still interested in receiving an answer to my second question.</p>