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

Answer 1

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