In an earlier tip, I showed how eager loading can silently query the wrong database when you use multiple connections. A reader followed up with a great question: what about subqueries?
For example, imagine you want to find all orders for active customers.
The natural way to write this in Eloquent is with whereHas:
Order::whereHas('customer', function ($query) {
$query->where('active', true);
})->get();
If Order and Customer use different database connections, this will fail with a "table not found" error.
whereHas, like all subquery methods, compiles everything down into a single SQL statement.
A single statement can only run on one connection, so the other database's tables are not accessible.
Interestingly, if you rewrite the same logic using whereIn instead of whereHas, it does work:
Order::whereIn('customer_id', Customer::select('id')->where('active', true))->get();
Note that this isn't running two queries, it's still a single query with a subquery.
But in this case, whereIn goes through a different internal code path that detects when the subquery targets a different database and rewrites the FROM clause to include the database name, like my_app.customers.
MySQL can resolve that kind of cross-database reference as long as the user has permission.
The caveat is that this only works when both databases live on the same server. A single SQL statement simply cannot span two servers. This is a MySQL constraint, not a Laravel one.
In that case, the only option is to split it into two queries: fetch the IDs from one connection, then use those IDs in a whereIn on the other.
It means two round trips and pulling a set of IDs into memory, but for most applications that is perfectly fine.
Here to help,
Joel
P.S. Are you trying to figure out some confusing framework behavior? Let's pair on it. Schedule a Get Unstuck call.
Slack