Laravel - How to join 2 tables from different db connection?

Question

I have an application that use 2 databases. I need to create a query that joins a table from one database with a table form another but I don't know how to do that.

So, I have a connection name mysql and phc. I can communicate with both with no problems.

Now, I'm trying to do this query:

$artigos = DB::connection('phc')->table('st')
        ->join('mysql.cart', 'mysql.cart.id_item', '=', 'st.ststamp')
        ->select('st.ststamp', 'st.ref', 'st.design', 'st.imagem', 'mysql.cart.qtt')
        ->where('mysql.carts.id_user','=',Auth::id())
        ->paginate(10);

But returns me : General error: 20018 Invalid object name 'mysql.cart'

I want to access connection mysql table cart and connection phc table st.

How can I solve this?

Thank you

I think this is not realy, because you need select database in begin connect. In mysql cli application you type `use database_name` and work in this database, for work in other database you need `use other database_name2` and work this. I think yout need run directly query with out eloquen. for example see this question https://stackoverflow.com/questions/11426929/can-we-use-join-for-two-different-database-tables — Naumov, Oct 04 '18 at 11:04
check here https://stackoverflow.com/questions/41423603/join-two-mysql-tables-in-different-databases-on-the-same-server-with-laravel-elo — Meera Tank, Oct 04 '18 at 11:51
checkout this answer [here](https://stackoverflow.com/a/63051014/10539212), hope it helps. — Phantom1412, Jul 23 '20 at 09:32

score 4 · Answer 1 · edited Oct 04 '18 at 11:54

4

Try to remove the connection('phc') and prefix the php tables as you do with mysql tables. You are choosing the connection, so the query builder should understand that it would prefix your tables with the connection names. So, when you type 'mysql.cart' the query builder will do 'phc.mysql.cart'.

$artigos = DB::table('phc.st')
        ->join('mysql.cart', 'mysql.cart.id_item', '=', 'phc.st.ststamp')
        ->select('phc.st.ststamp', 'phc.st.ref', 'phc.st.design', 'phc.st.imagem', 'mysql.cart.qtt')
        ->where('mysql.carts.id_user','=',Auth::id())
        ->paginate(10);

edited Oct 04 '18 at 11:54

Sreejith BS

1,183
1
9
18

answered Oct 04 '18 at 11:10

Fábio Nunes

86
4

If I try do that returns me Base table or view not found: 1146 Table 'mysql.cart' doesn't exist @FábioNunes – user3242861 Oct 04 '18 at 13:37
Is your databases on the same instance? – Fábio Nunes Oct 05 '18 at 13:03

score -2 · Answer 2 · answered Oct 04 '18 at 12:21

-2

this is my code for joining two table:

$departmetns = DB::table('department')
      ->select('department.*', 'faculty.name as f_name')
      ->join('faculty','department.faculty_id', '=' ,'faculty.id')
       ->get();

in your case

USE DB;

DB::table('st')
    ->select('st.ststamp', 'st.ref', 'st.design', 'st.imagem', 'cart.qtt')
    ->join('cart', 'st.ststamp', '=', 'cart.id_item')
    ->where('carts.id_user','=',Auth::id())
    ->paginate(10);

answered Oct 04 '18 at 12:21

Khalil DaNish

547
5
18

1

This is not between different databases @KhalilDaNish – user3242861 Oct 04 '18 at 13:38
1

I know this is for two table from the same database. – Khalil DaNish Oct 06 '18 at 07:03

Laravel - How to join 2 tables from different db connection?

2 Answers2