Eloquent whereHas on different database servers

1
votes

I have a very specific situation regarding databases in Laravel. We have two DIFFERENT servers, one with SQL SERVER and one MySQL.

The regular join (belongsTo, hasMany) between two different connections and different servers is working flawlessly and I can get all the data that I want.

The problem occurs when I want to add WHERE parameters to the relationship - Laravel will add subquery "and exists" into the query - which will, of course, fail because we have two different servers.

Both models have correction table and connection specified in model properties and as I said regular belongsTo and hasMany is returning the correct results from both servers. Only WHERE conditions are failing the query.

What are the ways to solve this problem and how do you usually deal with this?

Much appreciated!

4
phpmysqllaraveleloquentdatabase-connection
Interesting problem, can I ask why you have the data split between MySQL and SQL Server? – Spholt
@Spholt We are using one external accounting software that has its own requirements and server. Unfortunately, I cannot add my own software to that server. – Zaay
I can't dig into it right now but issue can be solved by tracking down where method on relationship model and overload the required code. Eventually I believe you could use laravel mutators as well to query the database specifically for one custom attribute: laravel.com/docs/master/eloquent-mutators – user8555937
I'm not sure to fully understand your question, but since you can't issue a SQL query involving both MySQL and SQL Server, you should have 2 separate models, and query each one separately. – Olivier

4 Answers

2
votes

I'm unsure if that's solving your problem but you could try the package laravel-cross-database-subqueries by hoyvoy.

Just extend your models with Hoyvoy\CrossDatabase\Eloquent\Model.

1
votes

I had a similar requirement a while ago. The solution I settled on was to replicate the data via a cron job from the MSSQL server into my MYSQL server, but this was largely because the MSSQL server was extremely slow in comparison to the MYSQL server. This allowed me to query correctly.

Alternatively, if you don't want to replicate the data and can get good response times from the MSSQL server, then you may want to look at "temporary tables". If you query the MSSQL server first and then create a temporary table in the MYSQL server with this data to query against, this may suit as a work around.

If none of the above suit, you will need to query the two separately and use PHP to manipulate the data because you can't query MYSQL and MSSQL in the same query.

0
votes

I see 2 options, the first one is duplicate the database, but you might run behind so perhapse not the best method.

The second option create a seperate query for the relationship filter and use the result of that query to fetch the data

0
votes

First create connection on config/database.php

return [
    'default' => 'first_db_connections',
    'connections' => [
        'first_db_connections' => [
            'driver'    => 'sqlsrv',
            'host'      => 'localhost',
            'database'  => 'database1',
            'username'  => 'user1',
            'password'  => 'pass1'
            'charset'   => 'utf8',
            'collation' => 'utf8_unicode_ci',
            'prefix'    => '',
        ],

        'second_db_connection' => [
            'driver'    => 'mysql',
            'host'      => 'localhost',
            'database'  => 'database2',
            'username'  => 'user2',
            'password'  => 'pass2'
            'charset'   => 'utf8',
            'collation' => 'utf8_unicode_ci',
            'prefix'    => '',
        ],
    ],
],

So your models use the normal default connection, then create model second connection to your models

class myModel extends Model {

    protected $connection= 'second_db_connection';

    protected $table = 'myTable';

    public function post() {
        return $this->belongsTo('App\Post');
    }
}