join()->where() in Laravel's Eloquent ORM

Question

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.

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 account_id matches an accounts id where the accounts id = users.account_id and accounts.subdomain = $subdomain?

Here is what I'm trying:

$user = User::whereUsername($username)
    ->leftJoin('accounts', 'accounts.id', '=', 'users.account_id')
    ->where('accounts.id', '=', 'users.account_id')
    ->where('accounts.subdomain', '=', $subdomain)
    ->firstOrFail();

The problem with this is that it's taking users.account_id as a literal string and not a table reference.

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?

Update: I managed to get the query working by using DB::raw() but am curious if there is a better way.

$user = User::whereUsername($username)
    ->leftJoin('accounts', 'accounts.id', '=', 'users.account_id')
    ->where('accounts.id', '=', DB::raw('users.account_id'))
    ->where('accounts.subdomain', '=', $subdomain)
    ->firstOrFail();

Also, still interested in receiving an answer to my second question.

Answer 1

I managed to get the query working by using DB::raw() but am curious if there is a better way.

You have the method whereRaw.

$user = User::whereUsername($username)
->leftJoin('accounts', 'accounts.id', '=', 'users.account_id')
->whereRaw('accounts.id = users.account_id')
->where('accounts.subdomain', '=', $subdomain)
->firstOrFail();

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?

You can use globalScopes: https://laravel.com/docs/5.3/eloquent#query-scopes