How to Query Distant Relations in Laravel?

try using hasManyThrough https://laravel.com/docs/5.1/eloquent-relationships

You should really de-normalize this. Or at least carry the country_id in all the sub-tables. You can ask the what country question to any of them? Yes?

Hi jimmck! Yes, but not just the country. You should be able to query the following:

1. all congregations that belong to a country, or region, or sub region, or greater district, or district
2. all districts that belong to a country, or region, or sub region, or greater district
3. all greater districts that belong to a country, or region, or sub region
4. all sub regions that belong to a country, or region

Aside from mapping the users to these models (so they will only have access to their specific area of assignment -- that may be country level, region level, etc.), these are also used for report generation such as "how many members are there in a given country grouped per region, or sub region, or district, or congregation".

So the table relationships and hierarchy need to be established properly:

1. country level
2. region level
3. sub region level
4. greater district level
5. district level
6. congregation level

I hope I am clearly conveying the idea of the project.

Ok so its time to write some queries! You understand the problem. I hope you are not expecting someone to write the actual model code for you.

I have decided to do away with the hasManyThroughs since I could not get them to work. I used join instead to query the list of congregations under a country. Here is a sample code:

$listAll = DB::table('countries')
    ->join('regions', 'countries.id', '=', 'regions.country_id')
    ->join('sub_regions', 'regions.id', '=', 'sub_regions.region_id')
    ->join('greater_districts', 'sub_regions.id', '=', 'greater_districts.sub_region_id')
    ->join('districts', 'greater_districts.id', '=', 'districts.greater_district_id')
    ->join('congregations', 'districts.id', '=', 'congregations.district_id')
    ->select('congregations.*', 'congregations.name')
    ->where('countries.id', '=', 17)
    ->get();    
        
foreach ($listAll as $item) {
    echo '<ul><li>' . $item->name . '</li></ul>';
}

De-normalizing is an option for better performance, but the setup of the districts and congregations are very dynamic and often change in 3 to 6 months. It would entail a lot of maintenance.

If you have a better way to do it... feel free to post it here.

Thanks firstplanb for posting your results! I am trying to do something very similar and this looks much better than nested whereHas statements.

I created a HasManyThrough relationship with unlimited levels: Repository on GitHub

After the installation, you can use it like this:

class Country extends Model {
    use \Staudenmeir\EloquentHasManyDeep\HasRelationships;

    public function congregations() {
        return $this->hasManyDeep(Congregation::class,
            [Region::class, SubRegion::class, GreaterDistrict::class, District::class]
        );
    }
}