Parent query in relation

Eager loading relationships with "with" has been really good for performance, so far. I have been making my own custom relations by extending base class, so i can eager load some more complexly tied tables, and I've been following similar philosophy in finding related row set: by using combination of whereIn for addEagerConstraints implementation. However, in times of huge parent result set, whereIn can be really really slow, because it sends lots of data as part of subquery (hundreds of thousands of lines).

I see a path that this can be optimized, and i would like to use parent's original query to fetch required relationships, instead of iterating trough array of parent models in mu addEagerConstraints.

Is it even possible to pass full parent query inside of relation object when i chain "with" method?

jlrdw

2 years ago

Level 75

I usually just double paginate, parent and child:

Next company link

Paginated Receivables for that parent company here

See the generated sql, the two different queries.

Just example.

Edit:

I suggest also learn the regular sql and pdo that produces the results. Eloquent converts to regular sql at runtime.

1 like

peca_divljak

2 years ago

Level 1

Thanks for the reply. Let me paraphrase question with example so its easier to understand what I'm after:

On two tables, Posts and Comments, we have foreign key post_id in comment table. Post model is gonna have a defined

public function comments(): HasMany 
{
    return $this->hasMany(Comments:class); 
}

simple as that.

Now, eager loading allows me to speed up loading related objects, and i understands that when i say

Posts::query()->with("comments")->get();

what is done in sql is actually two queries and binding with parents is done after that

SELECT * FROM `posts`;
SELECT * FROM `comments` WHERE `id` IN (1,2,3,4,5....);

So here is the deal. To get where in condition for second query, eloquent iterates through every result from first query. Then it makes array of values that are used for second query. When enough rows in first query are present, with large or custom made ids for comment table, the string that needs to be sent to sql server is large enough to cause performance issues. Especially if query is remote call.

So, here's my thinking. Both performance issues (iterating through parents, and doing large queries) are solved if i modify my queries as thus

SELECT * FROM `posts`;
SELECT * FROM `comments` WHERE `id` IN (SELECT `post`.`id` FROM `posts`);

This will dramatically reduce latency. What I'm asking is is it possible to get query that was done on parent table in my class extending Relation, so i use that to find rows for bindings, in a more optimized way. Extending base Relation class and implementing addEagerConstraints gives me access only to array of parent models, the way i see it.

I'm interested in this, because I'm willing to give way to slight performance hits if all my tables and relations definitions can be done at one place (in model classes), but what I'm after is the way they can be used more efficiently.

So, if I write my own

class CustomHasMany extends Relation
{
    // ...
    public function addEagerConstraints(array $models) {
        // here i want to access the original query that was used to get $models array
    }
}

is it possible to do without some kind of "hacks"?

jlrdw

2 years ago

Level 75

@peca_divljak read this: https://laravel-news.com/laravel-n1-query-problems

Also if you have a parent record that has hundreds or thousands of child records, don't use get() rather use paginate.

1 like

Please or to participate in this conversation.