How do I run this simple Mysql query in Laravel, from two different databases?
SELECT * FROM database1.table1, database2.table2
And how I do joins in Eloquent on different databases?
asked Nov 30, 2016 at 17:34
Rodolfo Jorge Nemer Nogueira
5,1402 gold badges44 silver badges32 bronze badges
2 Answers
As I mentioned, if the databases are on the same server and the mysql user has access to both databases, you can pass the tables in with DB::raw:
$q1 = DB::table(DB::raw('database1.table1 AS db1_tb1'))->select('*');
$results = DB::table(DB::raw('database2.table2 AS db2_tb2'))->select('*')
->union($q1)
->get();
For a join:
DB::table(DB::raw('database1.table1 AS db1_tb1'))
->join(DB::raw('database2.table2 AS db2_tb2'),'db1_tb1.first_key','=','db2_tb2.second_key')
Comments
You cannot use different connections to do that, so you should do something like:
$result = DB::connection('database1')->table('contacts')->get()
->union(
DB::connection('database2')->table('contacts')->get()
);
And if your Query Builder is does not return collections:
$result = collect(DB::connection('database1')->table('contacts')->get())
->union(
collect(DB::connection('database2')->table('contacts')->get())
);
answered Nov 30, 2016 at 17:47
Antonio Carlos Ribeiro
87.9k22 gold badges220 silver badges205 bronze badges
1 Comment
Rodolfo Jorge Nemer Nogueira
Ok, thanks! And how to do JOIN in multi databases? Just manually?
DB::raw