I'm having a difficulty to join two tables, one is a table from a postgres database which is my default database in laravel and the other one is a table from mysql database on another server.

I already define mysql connection inside config/database.php

My problem is that i want to join the tables with one common column which is the ID.

I've done my research. But most of them said it is impossible to join two tables from two different network.

What i'm trying to achieve is that i want to join both tables because i want to make a search filter using Yajra Datatable in laravel.

So my question is. Is it really impossible to join tables of two different database postgres and mysql on different network. Or is there some trick i could use to achieve that

CodePudding user response：

Honestly I didn't used this before, but let it be here for later. Thanks for this answer. Here are some filtered and useful resources about Postgres<->MySQL usages:

• Connect MySQL to PostgreSQL: 2 Easy Methods
• Интеграция PostgreSQL с другими СУБД: делаем запросы в MySQL Cool article (sorry that in russian, I understand, but somehow you can translate)
• PostgreSQL Accessing MySQL as a Data Source Using mysql_fdw
• Setup Mysql Foreign Data Wrapper in Postgresql
• Foreign Data Wrappers and Parallel Query
• How to work with multiple Postgres databases in psycopg2
• Joining tables from Postgres to MySQL using mysql_fdw
• Postgres foreign data wrappers weird join behavior
• How to Set Up a Foreign Data Wrapper in PostgreSQL

CodePudding user response：

You can do so easily with Eloquent models. It is not proper SQL joins but you can achieve relationship querying across multiple database or event servers.

1. In your config/database.php file, declare as much connection as needed
2. Create the models you want for each table
3. Specify the $connection attribute in the models

For example:

class Model1 extends Model
{
    public $connection = 'mysql_database';
    
    public function model2()
    {
        return $this->belongsTo(Model2::class);
    }
}

class Model2 extends Model
{
    public $connection = 'postgre_database';
    
    public function model1s()
    {
        return $this->hasMany(Model1::class);
    }
}

You can then use the relations between those models as normal Eloquent relationship