How to execute Join queries between multiple Databases that are on different server with Laravel Eloquent?

Question

This is how we handle multiple DB Connections with Laravel, which is a PHP Framework not Python (For whom thinks that this is a duplicate post)

<?php
return array(

    'default' => 'mysql',

    'connections' => array(

        # Our primary database connection
        'mysql' => array(
            'driver'    => 'mysql',
            'host'      => 'host1',
            'database'  => 'database1',
            'username'  => 'user1',
            'password'  => 'pass1'
            'charset'   => 'utf8',
            'collation' => 'utf8_unicode_ci',
            'prefix'    => '',
        ),

        # Our secondary database connection
        'mysql2' => array(
            'driver'    => 'mysql',
            'host'      => 'host2',
            'database'  => 'database2',
            'username'  => 'user2',
            'password'  => 'pass2'
            'charset'   => 'utf8',
            'collation' => 'utf8_unicode_ci',
            'prefix'    => '',
        ),
    ),
);

And this is how you connect to this database.

$user1 = User::on('mysql1')->where(/* ... */)->get()
$user2 = User::on('mysql2')->where(/* ... */)->get()

These are only SELECT queries. Therefore Eloquent works flawlessly.

However, when I want to execute a JOIN query operation between these 2 databases, this seems not possible.

You basically can't (it might be possible with the FEDERATED storage engine but I couldn't vouch for it) — GordonM, May 15 '15 at 06:39
possible duplicate of [MySQL -- Joins Between Databases On Different Servers Using Python?](http://stackoverflow.com/questions/5832787/mysql-joins-between-databases-on-different-servers-using-python) — GordonM, May 15 '15 at 06:40
@GordonM this is a PHP framework not a Phyton framework and Eloquent. I think Phyton doesn't have Eloquent as well. Therefore, it is impossible to be a duplicate. Don't write something for only purpose of writing. — SNaRe, May 15 '15 at 06:45
The high level language and/or framework aren't relevant. What is relevant is what the database engine can and can't support. If the engine supports cross-server joins then it should be possible in both PHP and Python. If not then it's not possible in either language. — GordonM, May 15 '15 at 07:01
@SNaRe, Gordon isn't wrong to have said what he said. He possibly should have given more info. The point is that if you think you can do it without Laravel/Eloquent (i.e. command line MySQL or something) *then* it's a potential issue/question in Laravel/Eloquent, but I'm pretty sure a genuine SQL `JOIN` is not possible across servers. Now if you want to know how to merge two Laravel `Collection`s (based on results from two different connections), or potentially see the results of a non-native foreign key relationship (1 table one one server, 1 on another), that's a different matter entirely. — alexrussell, May 15 '15 at 08:09

score 1 · Answer 1 · answered Sep 06 '17 at 06:39

If these 2 databases are on different servers, you can't do this. At least not simply. I read about a storage engine. But I have never tried to do this before.

BUT

If there databases are on the same server, you can do it with a simple join, IF the user has the right permission!

score 0 · Answer 2 · edited May 23 '17 at 12:01

0

Basically you can not do this using Eloquent ORM or Laravel's query builder.

However, you can try to use raw expression (https://github.com/laravel/framework/blob/8865d2276be94f0a3497ba3bd26c906ae9e91e1c/src/Illuminate/Database/Query/Expression.php#L13 ) or smth like this, but you'll have to write code to make a model from query result.

If you use laravel 4 this question can help: Laravel 4: how to run a raw SQL?

edited May 23 '17 at 12:01

Community

1
1

answered May 15 '15 at 06:52

aeryaguzov

1,143
1
10
21

http://laravel.com/docs/5.0/queries#raw-expressions I see raw method but when I use it how I will append these 2 database connection names to my raw sql code? I am okay with Raw SQL. I just gotta solve this. – SNaRe May 15 '15 at 07:12
Smth like this I think: http://stackoverflow.com/questions/17132206/laravel-4-read-config-files – aeryaguzov May 15 '15 at 07:17

score 0 · Answer 3 · answered Sep 03 '15 at 08:11

Write a raw query and use laravels DB methods to escape data.

$results = DB::select( DB::raw("SELECT Users.id, Properties.name FROM [Database1].[dbo].[Users] join [Database2].[dbo].[Properties] on Users.id = Properties.user_id where [so].[dbo].[Users].id = :user_id"), array('user_id' => Input::get('user_id', -1)));

score 0 · Answer 4 · answered Oct 24 '18 at 06:40

You can't do mysql joins between different database servers, this is a mysql limitation, not related to laravel or eloquent.

Your best bet is use the 2 different database connections as you already have, then merge and query the data through your php code.

How to execute Join queries between multiple Databases that are on different server with Laravel Eloquent?

4 Answers4