iambaz's avatar

Chain whereHas to traverse through a self-referencing model

Imagine I have the following models:

App\Location

This has a self referencing hierarchy, and has the following data

UK
  |---North West
  |             |----Liverpool
  |             |----Manchester
  |---North East
                |----Newcastle
                |----Sunderland

In this model I have a self relation

public function parent()
{
    return $this->belongsTo('App\Location', 'location_id');
}

and a recursive relation...

public function parentRecursive()
{
   return $this->parent()->with('parentRecursive');
}

App\Shop

The shop model has a 'location' relation.

public function location()
{
    return $this->belongsTo('App\Location', 'location_id');
}

What I want to do is to get all of the shops within a category. So if I have a shop called "ACME" that is related to "Liverpool", I can easily get it by sending the ID for "Liverpool" (as $value) in the following condition....

->whereHas('location', function($q) use ($value) {
            $q->where('id', $value);
    })

But technically, this shop is also in "North West" and in "UK".

So if I send the ID for the UK Location to that query, it will not return the ACME shop as it is not directly related to North West or UK ID.

I can get it working by sending the UK id ($value) to this...

$this->builder->whereHas('location', function($q) use ($value) {
        $q->where('id', $value);
    })->orWhereHas('location.parent', function($q) use ($value) {
            $q->where('id', $value);
    })->orWhereHas('location.parent.parent', function($q) use ($value) {
            $q->where('id', $value);
    });

So is there a better way to write the above which is ugly and would only work for a finite number of 'jumps' in the relation tree. I need it to traverse all of the locations until it reaches the top of the tree.

0 likes
1 reply
nikeddarn's avatar

Define relation 'parents' in Model:

return $this->parent()->with('parent')

In php collect ids all nodes of brunch with recursive function

$ids = [];

.........

while($paren) {

$ids[] = $paren->id

$paren = $paren->parent;

call this function recursively($paren);

}

Then use ids with whereIn();

Please or to participate in this conversation.