Chain whereHas to traverse through a self-referencing model

Using Laravel 5.4....

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.

from Newest questions tagged laravel-5 - Stack Overflow http://ift.tt/2oiktVx
via IFTTT