Is it possible within Laravel's Query Builder to left join one table with two keys where if one exists, it takes presedence?

Question

I am trying to left join a table in Eloquent Query Builder but the table has two potential foreign keys. I only want to join once but if a potential other column is not null, then it should take presedence.

DB::connection('...')->table('some_table')
    ->leftJoin('table_that_should_take_presedence as ttstp', 'ttstp.id', '=', 'some_table.ttstp_id')
    ->leftJoin('some_other_table', function ($otherTable) {
        $otherTable->on('some_other_table.id', '=', 'ttstp.some_other_table_id');
            //->.... if ttstp.some_other_table_id does not exist then
            //       some_table.some_other_table_id should be used
    });

So far, my solution is to join the table twice and use a coalesce to enable that presedence:

DB::connection('...')->table('some_table')
        ->selectRaw('coalesce(sot.example, sot2.example, null) as example')
        ->leftJoin('table_that_should_take_presedence as ttstp', 'ttstp.id', '=', 'some_table.ttstp_id')
        ->leftJoin('some_other_table as sot', 'sot.id', '=', 'ttstp.some_other_table_id');
        ->leftJoin('some_other_table as sot2', 'sot2.id', '=', 'some_table.some_other_table_id');

This doesn't seem slick, I feel the optimal solution is joining once. Any help appreciated. Thanks in advance.

Answer 1

As I understand you correct, you want this:

$q = DB::connection('...')
    ->table('some_table')
    ->selectRaw('sot.example')
    ->leftJoin('table_that_should_take_presedence as ttstp', 'ttstp.id', '=', 'some_table.ttstp_id')
    // Try to join by ttstp.some_other_table_id column, if it's null then use some_table.some_other_table_id column
    ->leftJoin('some_other_table as sot', 'sot.id', '=', DB::raw('COALESCE(ttstp.some_other_table_id, some_table.some_other_table_id)'));

dump($q->toSql());