Joining subquery from different database in Laravel

Question

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();

In the data_source2 connection, is there a database table called data_table1? — AntonyMN
– AntonyMN, Commented Apr 5, 2024 at 15:02
there is not a table called data_table1 in data_source2 @AntonyMN — James
– James, Commented Apr 5, 2024 at 15:03

miken32 · Accepted Answer · 2024-04-20 21:09:03Z

1

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')

edited Apr 20, 2024 at 21:09

miken32

42.5k16 gold badges127 silver badges177 bronze badges

answered Apr 5, 2024 at 15:18

James

217 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Joining subquery from different database in Laravel

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related