How to connect 2 different databases and Join 2 tables that are on different servers?

You want to perform a join for databases on two different server ? I don't think this is possible.

@SNaRe if you have same database user for both databases, I suggest you make a view on both the databases with you inner join. And then you can use the view as any other normal table. You can create a model for it and use it just like that.

You can make a view with a join involving databases on two different servers ?

@pmall @SachinAgarwal I do. This is totally normal if two tables share some relation.

@pmall not if both on different servers. But if on same server it is possible.

@SachinAgarwal this is what op mentioned he want to perform its join over two different servers. Don't think it is possible.

Yeah then not possible. And @SNaRe Joining 2 tables from 2 databases is fine. But joining 2 tables from 2 databases on different servers is not at all good. You can make an api for the server and use the request if you want data from other server.

Honestly, these servers are created with Vmware, which I should have mentioned. Basically they are on the same server but different virtual machine. Therefore, we can consider it as local I think.

Lets think of I did this

<?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 so now I have mysql1 and mysql2. How will I connect both of them and join them?

$users1 = DB::connection('mysql1 - there must be some kind of mysql2')-... ( inner join ... ) 

They have been talking about it here but no solution.

http://fideloper.com/laravel-multiple-database-connections

We can have query for each connection but cannot join. As I said let's assume they are on the same server.

You cant do this with eloquent. But you might achieve this with raw sql : http://stackoverflow.com/questions/11114197/join-tables-from-two-different-server

@pmall @SachinAgarwal in your previous post you said that this could have been achieved if databases were in same servers. I think even this is not possible? If it is can you show me an example.

@SNaRe It is possible if databases are in same server and both the databases should have same database user with admin privileges. That is create, edit, delete etc. I am currently using it in my project. And as for example.

CREATE OR REPLACE VIEW `users` AS 
select `database1`.`users`.`id` AS `id`,`database1`.`users`.`email` AS `email`,`database1`.`users`.`password` AS `password`
UNION
select `database2`.`users`.`id` AS `id`,`database2`.`users`.`email` AS `email`,`database2`.`users`.`password` AS `password`

@SachinAgarwal which I cannot explain myself is I want to use LEFT JOIN, not only JOINING 2 tables. Also can you share the Eloquent or Raw Sql code of your code which generates this query?

@SNaRe For joining you can do it like this:

CREATE OR REPLACE VIEW `users` AS 
select `database1`.`users`.`id` AS `id`,`database1`.`users`.`email` AS `email`,`database1`.`users`.`password` AS `password` , `database2`.`table`.`column` AS `column` JOIN `database2`.`table` ON (your condition)

@SNaRe why do you need to have two databases on two different servers?

@SachinAgarwal

When you execute a union you do like this

emails1 = DB::connection('db1')->table('contacts_1')
             ->selectRaw('mail_address as email, date as created_at')->get();

$emails2 = DB::connection('db2')->table('contacts_2')
             ->select('email', 'created_at')->get();

and union it with array_merge or something.

I just want to write your last query with either Eloquent or Raw Expression like http://laravel.com/docs/5.0/queries#raw-expressions If you can just show an example it would be great.

@pmall I have around 400 GB total of database. It has more than 50m rows. When I first started my project I had 1 products. Now I have other 1000 product which shares some information between them. In order to increase consistency, manage and improve uptime I spitted some of my tables into different databases. Some of my databases are in different locations.

@SNaRe So this is not a good way of approching this. I don't know what they are but there are techniques to manage such amount of data. I'm sure there is some server guy here who will help you if you create a thread about this.

@SNaRe I do it like this:

$query = {My Raw SQL QUERY}

DB::Statement($query);

@SachinAgarwal

In this case first of all both databases 'database1' and 'database2' must be initialized.

Do you do this to initialize? After just doing this database1 and database 2 be global?

DB::connection('database1');
DB::connection('database2');

Or do you do something like this?

$database1 = DB::connection('database1');
$database2 = DB::connection('database2');

Or do you say that since all databases are in same server and we are runnig raw sql don't we need any initilization for 2nd database?

I think we are coming to somewhere if I got my answer. Thank you for both of you for your interest @pmall @SachinAgarwal

@SNaRe really man I think you are approaching this the wrong way. Learn how to make your mysql server scale to this amount of data. Forget about cross server join there is no way it can have decent performance or used with eloquent.

@pmall I have started to migrate my database to MySQL Cluster. I have been migrating one by one. However, at least for now I don't want to postpone my Laravel Development.

@SNaRe You do not need to initialize the database2. When you run DB::statement it will be run on your default database driver you mention in your config file.

@SNaRe And if it is just about large amount of data, Then as @pmall said, Its not good to split database to handle this. But seeing you dnt want to delay things. Implement this slpit database for now, But keep working on A more better solution. That you can work with only 1 database.
I use this Split database because I have 1 database for each client. I dont do it for managing large data. Its just my clients do not want there data to be mixed with other clients. So Just separate each clients data I use this approach.

@SNaRe can you describe your final setup once you have mysql cluster up and running? It sounds really interesting, thanks.

Hello friend,

Did you get any solution??