Level 75
Eloquent runs two queries, one for parent another for children. So just run child query.
Apr 17, 2022
5
Level 27
Getting query/Builder for relation loading *without* loading relation
It’s easy enough to get the Eloquent Builder object or the raw SQL query used to fetch a model without actually fetching the model – just remove ->get() or replace it with ->toSql().
But is there no way to do the same – preferably retrieving the Eloquent Builder object – for eager loaded relations on collections?
Everything I can find about this is people wondering why ->toSql() doesn’t include the eager-loading queries as joins (the reason is obvious: they’re not joins) and being told to use DB::getQueryLog() to see all the queries. This is fine if you just want to inspect what queries your database is processing, but it’s not really usable for much else.
Eager-loading relations on a collection of fetched model instances (the models) does two things:
- It performs a database query to fetch a collection of results (the
relations), scoped by the IDs in themodelscollection - It iterates over the collection of
modelsand matches each fetchedrelationinstance to the correctmodel, saving it as an attribute on themodelinstance
What I would like to figure out how to do is to do the first, but not the second – that is, to get a collection of relation instances, scoped by the IDs of the models, but without associating relation instances with model instances. Essentially, this:
$models = SomeModel::where('abc', 1)->get();
// do stuff here
if ($someCondition) {
$builder = $models->getBuilderObjectForEagerLoadingRelation('someRelation'); // <- HOW?
$relationObjects = $builder->get();
}
I know that I can of course always just do it manually by repeating the model constraints as a subquery constraint on the relation, like this:
$relations = SomeRelation::whereIn('model_id', function($query) {
$query->select('id')
->from('some_models')
->where('abc', 1) // etc. for all the SomeModel constraints used to build the collect
;
})->get();
But that’s rather repetitive and not DRY, not to mention a hell of a bother when the query to fetch the SomeModel collection is already quite complex.
Is there a dynamic way to achieve this?
Level 27
@jlrdw Finding a way to get the child query is what this is all about. That is, the query that Eloquent runs when loading the children as a relation, i.e., SELECT * FROM [child_table] WHERE [id] IN (?, ?, ?, ?…) [AND whatever other scopes are applied].
Apart from the less-than-elegant method I mentioned in my other comment below using $model::whereIn('id', $collection->modelKeys(), I still haven’t found a good way to extract the query used to fetch the children from the database.
Level 15
Each relation in your model already returns the corresponding QueryBuilder Instance:
$relationSql = $model->relation()->toSql()
So just call the relation by its method, not as a property of the model!
1 like
Level 27
@Helmchen You’re misunderstanding my question; that’s an entirely different thing. Note how I’m doing $models->load(…), not $model->load(…). I’m working on a collection here, not a model instance. The model has a relation method – the collection doesn’t. Trying to do $models->someRelation()->toSql() will throw a BadMethodCallException.
What I’m trying to do is get the query Eloquent underlyingly creates to load the relation on a collection of fetched models, not a single model. I see now that the query I blearily wrote last night doesn’t actually make sense or do what I was trying to express, so the confusion is perhaps understandable (I’ve edited it to make sense).
After some sleep, my brain is a little clearer than it was at 1:30 this morning when I wrote the question, so I can now think of much more elegant way to do this, which actually does pretty much what I’m after, as far as I can tell:
$relations = SomeRelation::whereIn('model_id', $models->modelKeys());
Still seems less elegant than getting the query straight from the collection, but it’s better than having to repeat the constraints used to build the collection in the first place.
Level 75
SELECT * FROM [child_table] WHERE [id] IN (?, ?, ?, ?…)
You kind of answered yourself.
- query for a list of parent id's you want child data
- Setup a foreach in a foreach to get the data (display)
Like looping and display detail for accounts in an report.
At least it sounds like you are wanting a "report" details format. Like crystal, jasper or access does.
Edit
I wouldn't use collections, let the sql and database work for you.
Please or to participate in this conversation.