Framework: Error when quering relationship existence (Model::has()) while using multiple connections

[iraklisg]

• Laravel Version: 8.4.0
• PHP Version: 7.4.1
• Database Driver & Version: MySQL 8.0

Description:

Querying relationship existence using has() method result to an error if the relationship references a model with a different connection than the parent.

Steps To Reproduce:

Assume the following setup:

config/database.php

'connections' => [
    'main_connection' => [
        ...
        'host' => 'localhost',
        'database' => 'main',
        ...
    ],
    'personnel_connection' => [
        ...
        'host' => 'localhost',
        'database' => 'personnel',
        ...
    ],
]

App\Models\Unit

class Foo extends Model
{
    protected $connection = 'main_connection';
    ...
}

App\Models\Bar

use App\Models\Unit;

class Employee extends Model
{
    protected $connection = 'personnel_connection';
    ...

    public function foo(): \Illuminate\Database\Eloquent\Relations\BelongsTo
    {
        return $this->belongsTo(Unit::class);
    }
    ...
}

MySQL "main" database

| units |
+-------+
| id    |
| name  |
| ...   |
+-------+

MySQL "personnel" database

| employees |
+-----------+
| id        |
| name      |
| unit_id   |
| ...       |
+-----------+

When querying the relationship existence, the following error occurs:

Employee::has('foo')->get();
// Illuminate/Database/QueryException with message 'SQLSTATE[42S02]: Base table or view not found: 1146 Table 'personnel.units' doesn't exist (SQL: select * from `employees` where exists (select * from `units` where `employees`.`unit_id` = `units`.`id`))'

However if I do:

Employee::whereHas('unit', fn ($query) => $query->from('main.units'))->get();
// OK!

The error occurs only when using the has method. For instance, eager-loading the relationship works as expected:

Employee::with('unit')->get();
// OK!

Using has method to query relationship existence when the two models using the same connection works as expected too

[taylorotwell]

I really wish people wouldn't split relationships across databases 😬

[taylorotwell]

Laravel doesn't provide built-in support for querying relationships across database. Generally I just think it's a bad idea and don't even want to encourage people to build applications like that. It seems you already found a work around.

[driesvints]

I took an even deeper dive into this and have come to the conclusion that it's indeed not supported but even that it is absolutely impossible to ever do this. Take the currently generated query from below:

select * from `employees` where exists (select * from `units` where `employees`.`unit_id` = `units`.`id`)

This fails because "units" lives on a different connection. As you cannot connect two tables from a different connection in a single query there's no way that something like has can ever work in a "join" fashion. To solve this you'd have to gather identifiers up front and compare them to a table on a different connection but that could lead to very expensive queries. We "could" look into just checking if the identifier was "null" in the parent connection query:

select * from `employees` where `employees`.`unit_id` is not null

But that's a totally different query and doesn't gives the assurance that the relation effectively exists in the other connection. There's also no re-assurance from foreign keys as these don't exists between connections. The fact that with works is simply because a separate query is run in isolation for the eager load.

I'll send in a PR to document this constraint of using multiple connections in combination with has.

[driesvints]

Docs PR: https://github.com/laravel/docs/pull/6604

[iraklisg]

I took an even deeper dive into this and have come to the conclusion that it's indeed not support but even that it is absolutely impossible to ever do this. Take the currently generated query from below:

select * from `employees` where exists (select * from `units` where `employees`.`unit_id` = `units`.`id`)

This fails because "units" lives on a different connection. As you cannot connect two tables from a different connection in a single query there's no way that something like has can ever work in a "join" fashion. To solve this you'd have to gather identifiers up front and compare them to a table on a different connection but that could lead to very expensive queries. We "could" look into just checking if the identifier was "null" in the parent connection query:

select * from `employees` where `employees`.`unit_id` is not null

But that's a totally different query and doesn't gives the assurance that the relation effectively exists in the other connection. There's also no re-assurance from foreign keys as these don't exists between connections. The fact that with works is simply because a separate query is run in isolation for the eager load.

I'll send in a PR to document this constraint of using multiple connections in combination with has.

@driesvints Thank you for pointing this out!

[rodrigopedra]

Hi @driesvints as you said you are going to add it to the docs, I just want to give you an example.

One can create two different connections Laravel-wise with same credentials but just different databases for convenience.

So when performing a query that should be from two different connections, the RDBMS can actually query the two different schemas/databases as the connection credentials on the running query builder are the same as the other one which will become implicit.

I know MySQL and SQL Server support it (I guess Postgres would also support, but I never used this feature with it). For example let's say in your example the units table lives under a coporative database, and the employees table lives under a sales database, both on the same RDBMS server, as the different configured Laravel connections are actually the same when you consider the credentials, this query is possible:

~sql
select *
from sales.employees -- database.table notation
where exists (
select *
from corporative.units -- database.table notation
where employees.unit_id = units.id
)
~

SQL Server can also run that query even if the databases are on different servers as long there is a linked server configured between them and the user credentials is allowed to access that linked server.

In other words the query is run from a single PDO connection, but if that connection's credentials have access to both databases it will work.

The workaround cited by @iraklisg might be working due to that being the case, and as the table name is forced by calling ->from(...) the end query is valid and can be run. Just for reference this is the workaround I am referring to:

~php
Employee::whereHas('unit', fn ($query) => $query->from('main.units'))->get();
~

I agree splitting relations between databases is not the ideal scenario, and I didn't built the app I saw that being used but joined a project where that was already place.

But I also think there are some situations where the developer is building an app around an existing database and that situation can happen. Which as the reason the project I referred above needed to use that setup, it was built to replace a in-house reporting solution built previously in plain PHP. The many databases schemas were from different CRMs and other systems it needed to generate reports from.

As you said you are adding it to the docs I think it is more accurate to say this setup is considered to be an edge case and support across all query builder features for such setup is not guaranteed and might not work as expected.

Hope this helps.

[driesvints]

Hey @rodrigopedra. Thanks for the thorough reply as usual! The problem I see with this is that this will still fail if the connection credentials are different. And from what I can tell from the internals they are currently totally unequipped to handle multiple connections in where statements for these sorts of queries.

[rodrigopedra]

Hi @driesvints , thanks for reading all up as usual, I am too wordy and need to work on that =)

The setup I was trying to illustrate is when you have multiple databases in the same server, which I've seen in several projects. I agree is not the ideal one, but it happens often.

As a eloquent model has a dedicated $connection property, but doesn't have a dedicated $database property, when dealing with tables from different databases, but in the same server, it is common that people creates different connection configurations in their ./config/database.php file to the same server, with same configurations, changing only the 'database' key. So using models that live in different databases becomes easier.

Think in the multi-tenant using different databases per-tenant (I know it is a controversial example). It is common in those setup to have a shared database with tenant and user login data, and maybe also some shared lookup tables, for example a roleswhen role is not tenant-configurable. And then we have one database per tenant.

Most probably the developer will have two database connection configurations in their ./config/database.php, so they can keep the shared database untouched, and just switch the tenant specific database name once per request/entry point.

Then each model will have their $connection property to their corresponding Laravel connection, which uses the same database credentials (I've seen usage of different credentials per tenant, but in such setup all tenant had read access to the tables in the shared database).

MySQL, for example, is capable of running a SQL statement joining tables from these two different databases, similar to the one in my last response. Or for example some SQL that joins the users table from the "shared" database to any tenant-specific table.

Of course when such SQL statement is executed, it is executed just from one PDO connection, PHP doesn't parse the SQL statement to check if all referenced tables exist in that connection. But as the credentials used on that PDO connection can read from both the shared and the tenant-specific database, MySQL (as an example, other RDBMS are able too) also doesn't care if that statement is referencing tables that live in different database within the same server as long the credentials used for running that statement has access grants to each database and the statement uses proper notation.

Again, I know this is not an ideal scenario. Ideally an app should join tables from the same database for opitmal performance. But my intention was to highlight that such situation is not impossible, and most of the time Eloquent handles it fine, as it just builds the query and delegates execution to MySQL, for example, as long the proper notation on tables' names is used, and the connection credentials are capable of running such SQL statement.

In my opinion it would be better if the comment added to docs told something in the lines of: "Hey building queries for dealing with tables from different connections is considered an edge case and might not give you the results you expect when using the query builder".

Saying its "impossible" is what I was suggesting to avoid. For example the workaround proposed in the first post works fine as it "forces" the database.table_name notation to the query builder on run time.

Sorry if my first response lead to any confusion, I need to work on being more concise. I hope this is clearer. And thanks again for your time.

[driesvints]

@rodrigopedra maybe it's best that you attempt a PR to the docs yourself?

[rodrigopedra]

Hi @driesvints , I can give a try, but I am not confident on my English skills. I apologize if I made this seems harder than it should be, wasn’t my intention.

I am fine with your current PR to the docs. This is an edge case anyway, I was just thinking of changing the word “impossible” to something else, but not sure how to better phrase it. But I might be being picky on this one.

I am not at my office right now, and it should take some time until I get back. I can give it a try when I am back.

Thanks again for your time and patience.

[driesvints]

Okay, I've changed it to "not possible at this time" instead 👍

[rodrigopedra]

Thanks @driesvints