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

Arella

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 #11427429 – Immediacy

and sorry for my English – Immediacy

Are your databases located on different servers? – Nephrotomy

check here #41424103 – Sporocarp

checkout this answer here, hope it helps. – Entrain

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

Duff

If I try do that returns me Base table or view not found: 1146 Table 'mysql.cart' doesn't exist @FábioNunes – Arella

Is your databases on the same instance? – Term

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

Mange

This is not between different databases @KhalilDaNish – Arella

I know this is for two table from the same database. – Mange