Using Laravel Query Builder, how to join tables from two different databases

Question

I have two different tables in sql server database on the same host that I need to join together in a query.

I would like to use the laravel QueryBuilder to do so.

I've tried so far:

return DB::table('users')
    ->select([
        'users.id',
        'Resources.FirstName'
    ])
    ->join('Resources.dbo.ID', 'Resources.UserID', '=', 'users.id');

It results in the following error: General error: 1 near ".": syntax error (SQL: select "users"."id", "Resources"."FirstName" from "users" inner join "Resources"."dbo"."ID" on "Resources"."ID" = "users"."id")

If I copy the query in my dabatase script editor and run it, it runs correctly and give the expected result.

I have also tried this

return DB::table('users')
    ->select([
        'users.id',
        'Resources.FirstName'
    ])
    ->join(DB::raw('Resources.dbo.ID'), 'Resources.UserID', '=', 'users.id');

return DB::table('users')
    ->select([
        'users.id',
        'Resources.FirstName'
    ])
    ->join(DB::raw('Resources.ID'), 'Resources.UserID', '=', 'users.id');

->join('Resources', function($q) {
            $q->connection('tcollect')->on('Resources.ID', '=', 'users.id');
        });

Is there a way to achieve this?

Ahmet Firat Keler · Accepted Answer · 2022-06-27 17:26:36Z

1

Please try that

DB::table('Database1.Resources as dt1')-> join('Database2.users as dt2', 'dt2.id', '=', 'dt1.UserID')->select(['dt1.*','dt2.*'])->get();

answered Jun 27, 2022 at 17:26

Ahmet Firat Keler

4,2502 gold badges16 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

naghal Over a year ago

I have tried this also, but it return the sql error

SQLSTATE[HY000]: General error: 1 no such table: Beta8.Resources (SQL: select "dt1"."id", "dt2"."id" from "Beta8"."Resources" as "dt1" inner join "ArmPay_staging"."users" as "dt2" on "dt2"."id" = "dt1"."UserID")

evenf if the table does exists in the given database.

Ahmet Firat Keler Over a year ago

Is the second database on the same server? Do you have the necessary privileges to access it?

naghal Over a year ago

Yes I have access to both database, they are on the same server. I have found also that the query does work if it is run in tinker, but not if it is run from the test. Maybe it has something to do with the fact that the phpunit.xml file overides some env variable with the following? <env name="DB_CONNECTION" value="sqlite"/> <env name="DB_DATABASE" value=":memory:"/>

Ahmet Firat Keler Over a year ago

Hey, can you check this out? stackoverflow.com/questions/44392039/…

Collectives™ on Stack Overflow

Using Laravel Query Builder, how to join tables from two different databases

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related