How to combine two different tables in different database using SQL in laravel

Question

Expect:

select robotid, number from tool where is_active = 1
select * from robot_team where id = robotid( robotid from tool)
foreach id(from robot_team), number(from tool) , status_za(from robot_team), status_zb(from robot_team), status_zm(from robot_team), status_qs(from robot_team)

Result:

cannot show success through postman

Code:

public function robotStatus()
    {
        $arr=[];
                
        $robotlist = DB::table('robot_team AS u')
                ->Join(DB::connection('sql')->table('tool AS m'), 'u.id', '=', 'm.robotid')
                ->select('u.*', 'u.id AS uid', 'm.number AS mnumber')
                ->where('m.is_active', '=', 1)
                ->orderBy('u.id', 'desc')
                ->get();
        if(count($robotlist)>0){
        foreach ($robotlist as $v) {

            $robot_arr = [
            "id" => $v->uid,
            "number" => $v->mnumber,
            "FA" => $v->status_fa,
            "FC" => $v->status_fc,
            "FM" => $v->status_fm,
            "PS" => $v->status_ps
            ];
            $arr[]=$robot_arr;
        }}

        return $arr;


    }

Comment:

Hello all,

Laravel : join two tables of different db connections

I try to combine using the above code but it is fail.

I have read the above link but it cannot reach my purpose as two table in different db in mysql.

Please help me to solve the above problems.

Thank you all.

The answer of the question in your provided link said **No** it's not possible — Farhan Ibn Wahid, Apr 07 '21 at 02:55
it is possible to query from two mysql db but `join` is NOT possible — Farhan Ibn Wahid, Apr 07 '21 at 03:03
It's possible to query (with join) from two MySQL db as long as its in the same connection. It's not possible if it different connection. — FanoFN, Apr 07 '21 at 03:05
But is it possible to select the table in variable or array. then combine using join? — Timothy Tang, Apr 07 '21 at 03:12
https://stackoverflow.com/questions/11426929/can-we-use-join-for-two-different-database-tables I read the above link but how to apply in laravel? — Timothy Tang, Apr 07 '21 at 03:46

score 1 · Answer 1 · answered Apr 08 '21 at 08:13

When using ->Join you are asking the database to join data tables before the result set hits your application code. For that to work regardless of programming language and framework the two databases must reside within the same database server. It is unclear from your question if that is the case here.

Even if the two databases reside on the same server I'm not sure that Laravel supports joins across different connections. The Laravel documentation doesn't mention it, but it might be supported anyway. Perhaps the actual error message (rather than "but it is fail") would shed some light on the problem.

If your data sets are fairly small I would recommend you to combine the data sets manually using PHP code. If your data sets are big I would explore the possibility to replicate data from the one database into the other database (to have access to both tables in the same database).

How to combine two different tables in different database using SQL in laravel

1 Answers1