Eloquent Relations with Multiple Pivot Tables (solved)

I'm trying to set up Eloquent relations between "projects" and "assembly_districts". To query projects in a particular district requires three joins that go through two pivot tables, counties_projects and assembly_districts_counties; i.e.,

select * from projects p join counties_projects cp on p.id=cp.project_id join assembly_districts_counties adc on cp.county_id=adc.county_id join assembly_districts ad on ad.id=adc.assembly_district_id where ad.district_number = 1 and ad.decade=2

projects and counties already have reciprocal belongsToMany relations. Do I just need to set up the same thing for counties and assembly_districts, or is there some sort of direct relationship I can set up between projects and assembly_districts?

Charizard

5 years ago

Best Answer

Level 5

If you have a belongsToMany from projects to counties and then from counties to assembly districts you can define a direct function on your project model like the following:

public function assembly_districts(){
      return $this->counties->map->assembly_districts->flatten();
}

This basically maps the assembly districts function on all related counties to a project producing one single collection; and you can implement the same one from the assembly_district side

dcranmer

5 years ago

Level 6

Oh, that is slick!. Exactly what I was looking for. I was thinking only about Eloquent relation types, and never thought about using collection methods. Thank you!

1 like

Please or to participate in this conversation.