Laravel join two tables in different databases

Question

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

As far i know you can't do this, but if the databases are on the same server you cant create a View on the database you actually connect or you need to do is FEDERATED Storage Engine, hope it helps — Ulises
– Ulises, Commented May 25, 2018 at 18:30
@UlyssesMarx - Mysql does allow you to query two Databases at the same time, it's one of the only DB engines that does. One should note though, that the DB user must have privilege to both databases, this is likly the issue you face. — ArtisticPhoenix
– ArtisticPhoenix, Commented May 25, 2018 at 18:42

ArtisticPhoenix · Accepted Answer · 2018-05-25 18:49:49Z

2

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.

edited May 25, 2018 at 18:49

answered May 25, 2018 at 18:46

ArtisticPhoenix

21.7k2 gold badges27 silver badges38 bronze badges

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

5 Comments

RandomUser Over a year ago

I have found out the sql query I need to access the two methods,the prbi is when I create a $data=DB::raw("..."); and then return view('view') ->with('user',$user); it doesnt return nothing

ArtisticPhoenix Over a year ago

I suggest checking if the user you are using has access to the other database. For example query only that database with that user and see if they can access it.

RandomUser Over a year ago

it would be much easier if you could add the way you do it.It would help me a lot.simply add your example I have already created a mysql2 conn but dont know how to use it in my actual select query

ArtisticPhoenix Over a year ago

You don't use it, you use one connection with the same user that has access to both databases. That is if you want to do a join on the tables. Then you just add database.table instead of table it's that simple. But the user has to be able to access both tables. In Laravel I am not sure how you do it, but its trivial with raw sql

Ulises Over a year ago

You can't do it on Laravel, the easy way is you need to connect by shell or your admin an run the sql script that i wrotte you: CREATE VIEW db1.ticket` AS SELECT * FROM db2.ticket;`

RandomUser · Accepted Answer · 2018-05-27 17:35:38Z

2

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

answered May 27, 2018 at 17:35

RandomUser

1372 silver badges15 bronze badges

Comments

Ulises · Accepted Answer · 2018-05-25 18:32:58Z

0

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

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

answered May 25, 2018 at 18:32

Ulises

4168 silver badges24 bronze badges

3 Comments

RandomUser Over a year ago

I am using laravel how do I add this to my code,this is only a sql function

Ulises Over a year ago

Man how can you ask this if you are asking for MySQL info, this is no problem of laravel, you need to connect to mysql by shell or your admin and modify the CREATE VIEW for your own settings.

RandomUser Over a year ago

where do I ask for mysql info?

Collectives™ on Stack Overflow

Laravel join two tables in different databases

3 Answers 3

5 Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related