Edmund Sulzanok Edmund Sulzanok - 6 months ago 30
MySQL Question

Laravel Conditionally join tables based on specified class

I have three tables

customers
,
organizations
and
contacts
. Table
customers
table has columns
customer_class
(the class indicates table use
Organization
,
Contact
) and
customer_id
(corresponds to column
id
in either
organizations
or
contacts
).

So what I did was

$interactions = DB::table('sales_interactions')
->join('customers', 'customers.id', '=', 'sales_interactions.customer_id')
->join('organizations', function($join)
{
$join->on('customers.customer_id', '=', 'organizations.id')
->where('customers.customer_class', '=', 'Organization');
})
->join('contacts', function($join)
{
$join->on('customers.customer_id', '=', 'contacts.id')
->where('customers.customer_class', '=', 'Contact');
})

->select([
'organizations.title as customer',
'contacts.name as customer',
'sales_interactions.id',
'sales_interactions.created_at',
'sales_interactions.title',
'status',
'deadline',
'value',
]);


It worked when I joined organizations only. But joining contacts returns zero results.

Answer

You need a union query:

    $contactsQuery = DB::table('sales_interactions')
        ->join('customers', 'customers.id', '=', 'sales_interactions.customer_id')
        ->join('contacts', function($join)
        {
            $join->on('customers.customer_id', '=', 'contacts.id')
                ->where('customers.customer_class', '=', 'Contact');
        })
        ->select([
            'contacts.name as customer',
            'sales_interactions.id',
            'sales_interactions.created_at',
            'sales_interactions.title',
            'status',
            'deadline',
            'value',
        ]);

    $interactions = DB::table('sales_interactions')
        ->join('customers', 'customers.id', '=', 'sales_interactions.customer_id')
        ->join('organizations', function($join)
        {
            $join->on('customers.customer_id', '=', 'organizations.id')
                ->where('customers.customer_class', '=', 'Organization');
        })
        ->select([
            'organizations.title as customer',
            'sales_interactions.id',
            'sales_interactions.created_at',
            'sales_interactions.title',
            'status',
            'deadline',
            'value',
        ])
        ->union($contactsQuery)
        ->get();