3 Table Join Confusion

Hi All

On my multitenant app, I'm trying to join 3 tables, users | companies | tenants.

For reference, a user belongsTo a Tenant | a user belongsTo a Company but they CAN'T belong to both, its one or the other.

For some reason though on the join, its only showing me users who belongTo tenants and not users who belongTo company. I started with inner join also tried left join and its not going well.

Please can someone advise where i may be going wrong?

$users = \DB::table('tenants')
->where('tenants.slug', $subdomain)
->leftJoin('users', 'tenants.id', '=', 'users.tenant_id')
->leftJoin('companies', 'tenants.id', '=', 'companies.tenant_id')
->where('users.email', $credentials['email'])
->select(
            'users.first', 
            'users.id as userId', 
            'users.user_type_id as userType', 
            'users.status as userStatus', 
            'users.role_id', 
            'companies.status as companyStatus'
            'tenants.status as tenantStatus'
        )
->get();

Thank you in advance.

LaryAI

3 years ago

Level 58

The issue with the current query is that it is using a where clause to filter by the users.email after the left joins have been performed. This means that any users who do not have a corresponding row in the users table will be filtered out. To fix this, we can move the where clause to the leftJoin for the users table, so that it is applied before the join is performed. Additionally, we can use a union to combine the results of the two left joins, since a user can only belong to one of the two tables.

Here's an updated query that should work:

$users = \DB::table('tenants')
    ->where('tenants.slug', $subdomain)
    ->leftJoin('users', function ($join) use ($credentials) {
        $join->on('tenants.id', '=', 'users.tenant_id')
             ->where('users.email', $credentials['email']);
    })
    ->select(
        'users.first', 
        'users.id as userId', 
        'users.user_type_id as userType', 
        'users.status as userStatus', 
        'users.role_id', 
        'NULL as companyStatus',
        'tenants.status as tenantStatus'
    )
    ->union(
        \DB::table('tenants')
            ->where('tenants.slug', $subdomain)
            ->leftJoin('companies', 'tenants.id', '=', 'companies.tenant_id')
            ->select(
                'NULL as first', 
                'NULL as userId', 
                'NULL as userType', 
                'NULL as userStatus', 
                'NULL as role_id', 
                'companies.status as companyStatus',
                'tenants.status as tenantStatus'
            )
    )
    ->get();

In this query, we use a leftJoin for the users table with a closure that adds the where clause for the email. We also select NULL values for the columns that are not present in the users table, so that the union with the companies table works correctly.

We then use a union to combine the results of the two left joins. The companies table is joined with a regular leftJoin, since we want to include all rows from the tenants table that do not have a corresponding row in the companies table.

Finally, we select the columns that we want to include in the result set, including the NULL values for the columns that are not present in one of the tables.

This should give you a result set that includes all users who belong to either the users or companies table, filtered by the email address and the subdomain.