[SOLVED] Joining subquery from different database in Laravel

Joining subquery from different database in Laravel

I'm attempting to join a subquery into a main query using the Laravel Query Builder in a context similar to the example below. I assumed that the main query would recognize this by specifying the database and table used by the subquery. However, Laravel throws the following SQL error:

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'data_source2.data_table1' doesn't exist

I've tried prefixing the source query with "data_source1", but I still encounter the same error as shown above.

$source_query = DB::connection('data_source1')
    ->table('data_table1')
    ->select(DB::raw('
        SUM(data_table1.value) as value
    '))
   
$data = DB::connection('data_source2')
    ->table('data_table2')
    ->joinSub($source_query, 'source_query', function ($join) {
        $join->on('data_table2.hour', '=', 'source_query.hour');
    })
    ->where('date', '=', $date)
    ->select(DB::raw('
        source_query.value as value,
    '))
    ->get();

Solution

I have ended up figuring it out so for anyone else who comes across this, if you want to use joinSub() when the queries use base tables in different databases

use the following in the subquery you will be joining in:

$source_query = DB::table('data_source1.data_table1')

rather than:

$source_query = DB::connection('data_source1')
    ->table('data_table1')