Alternatively, is there a different approach to read permissions that isn't limited by these constraints?
Optimizing joining in global scopes in Eloquent
I use global scopes to filter what a user can see. These chain together, so for example B can depend on A being readable with a simple $query->has('A') while A's scope does the heavy lifting.
My issue is that for one model, I have to chain together 6 tables. A simplified SQL equivalent of the query would be:
SELECT a.id
FROM a
JOIN b ON a.b_id=b.id
JOIN c ON c.b_id=b.id
WHERE
c.property = true;
I wrote the above in Eloquent like this (not implemented as a scope yet):
\App\Models\A::
whereHas('b', function(Builder $query) {
$query->whereHas('c', function(Builder $query) {
$query->where('c.property', '=', true);
});
});
})->
get();
Chaining all these whereHas methods is affecting performance. For the actual Eloquent query I'm using it results in 64 steps according to SHOW PROFILE; while the SQL one only requires 22. Of course, the Eloquent way adds the models' individual global scopes which I want and accept the complexity they add, but three times the steps feels unnecessary. How can I optimize this?
Also, I can't use the join method in a scope because that will make the joined table's column names conflict with the column names of the original table when Laravel does things like select on column names.
Please or to participate in this conversation.