laravel adjacency list - get subquery exists depth

Question

rootCategory is HasOne relation:

public function rootCategory()
{
    return $this->hasOne(PartnerCategory::class, 'partner_id', 'id')->where('partner_category_main', 1);
}

then:

$categories = Partner::restaurants()->with('rootCategory')->get()->pluck('rootCategory')

then I need to know if rootCategory has childs with depth >1 and doing in CategoryResource:

$this->descendants()->whereDepth('>', 1)->exists()

but it making much more queries. How I can to query this same time with rootCategory relation?

I tried with(['rootCategory' => function($q) {return $q->descendants();}]) but it not working

Is whereDepth a query scope or is it where('depth', ...)? Instead of querying the existence inside the CategoryResource, you could load a count. (withCount or withAggregate). — IGP
– IGP, Commented Apr 14, 2023 at 16:58
withCount works like this: $categories = Partner::restaurants()->with(['rootCategory' => function(HasOne $q) { $q->withCount(['descendants as is_deep_tree' => function ($q) { $q->whereDepth('>',1); }]); }]) — rst630
– rst630, Commented Apr 14, 2023 at 17:20
how it would look withAggreaget? withCount get me 2x faster result - thanks — rst630
– rst630, Commented Apr 14, 2023 at 17:21
withAggregate is similar to withCount. The query would be the same since withCount just uses withAggregate in the background. withAggregate(['descendants as is_deep_tree' => function ($q) { ... }], '*', 'count'); — IGP
– IGP, Commented Apr 14, 2023 at 18:20

rozsazoltan · Accepted Answer · 2023-04-14 17:06:49Z

0

Count SubQuery - WhereCount

You can get subquery row count with the "whereCount" function.

https://laravel.com/docs/10.x/eloquent-relationships#counting-related-models

Example with your code:

$categories =
  Partner::restaurants()
    ->withCount('rootCategory', function (Builder $query) {
       $query->descendants()->whereDepth('>', 1)->exists();
    })
    ->get()
    ->pluck('rootCategory');

edited Apr 14, 2023 at 17:06

answered Apr 14, 2023 at 16:42

rozsazoltan

18.8k8 gold badges52 silver badges148 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

rst630 Over a year ago

I don't need filter, I need bool field exists or not

rozsazoltan Over a year ago

Oh, I see. Now I understand. I will modify my answer.

Collectives™ on Stack Overflow

laravel adjacency list - get subquery exists depth

1 Answer 1

Count SubQuery - WhereCount

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Count SubQuery - WhereCount

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related