CODEWITHSUNDEEP
phpmysqllaravel
RandomUser
RandomUser

Reputation: 137

Laravel join two tables in different databases

I have already defined my connections in database.php :

'mysql' => [
            'driver' => 'mysql',
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'db1'),
            'username' => env('DB_USERNAME', 'root'),
            'password' => env('DB_PASSWORD', ''),
            ...
        ],

        'mysql2' => [
            'driver' => 'mysql',
            'host' => env('DB_HOST_2', '127.0.0.1'),
            'port' => env('DB_PORT_2', '3306'),
            'database' => env('DB_DATABASE_2', 'db2'),
            'username' => env('DB_USERNAME_2', 'root'),
            'password' => env('DB_PASSWORD_2', ''),
         ...
        ],

I want to join table user from db2 with table ticket from db1.Please help is appreciated.I tried the one below but since ticket table is from db1 is not recognized

$user= DB::connection("mysql2")->table('User')
        ->join('ticket', 'User.Id', '=','ticket.user_id')
        ->select('User.*')
        ->where('ticket.id', '=', 1)
        ->get();

Thanks in advance

Upvotes: 1

Views: 5844

Answers (3)

RandomUser
RandomUser

Reputation: 137

the answer that I wanted was:

$user=DB::table(DB::raw('sarida_test.user AS db1_tb1'))
            ->join(DB::raw('task_flow.tickets AS db2_tb2'),'db1_tb1.Id','=','db2_tb2.user_id')

Upvotes: 2

ArtisticPhoenix
ArtisticPhoenix

Reputation: 21661

MySql allows you to access and even join from two database on the same host using the same connection and user. I actually have a cron job that does this every night. We have 2 copies of the same data, one is live for out clients one is for management to edit, at night the two are compared and changes are pushed to the live copy. This way our clients are protected if someone makes a mistake in the updates as it's not immediately live. These are 2 separate database that with the magic of a full outer join are synced up. (this is something that fits our specific needs and I wouldn't recommend it for everyone)

This is important because the user must have access to both databases, This should be obvious but it is often overlooked as one just assumes it's the case, but often times its not. Furthermore Mysql's error reporting on no access is really poor and it often takes the form of no "Seeing" the other database and just acts like its not their (this may or may not be the correct behaviour depending who you ask and in what context)

Likely your problem is one of privilege to the second database.

Upvotes: 2

Ulises
Ulises

Reputation: 416

So if they are on the same same server just make a view like :

CREATE VIEW `D2`.`example` AS SELECT * FROM `D1`.`T1`;

Upvotes: 0

Related Questions