City belongsThroughCountyTo Province association, how to simplify the code?

Question

Tables:

Province hasMany County, County belongsTo Province, County hasMany City, City belongsTo County

So basically something like: City belongsThroughCountyTo Province

Situation:

In a search form I have a select drop down menu with provinces.

The "code":

When I list the results, I first get ids of counties that belong to the specified province, and then do a City.county_id IN (array_of_counties_ids_here).

Question:

My question is, could I be doing it in a better way? Without first accessing the counties table. A simple three way join should do the trick, but I don't have an idea on how to implement it in Cake.

Adding a province_id field to the cities table isn't a solution in my case (can't alter tables).

Answer 1

You should avoid this by creating a view in SQL that directly links City to Province.

Assuming you've got:

tblCity
  city_id
  county_id
  name --etc
tblCounty
  county_id
  province_id
  name --whatever else
tblProvince
  province_id
  --whatever else

create or replace view CityToProvince as 
  select c.city_id, p.province_id from tblCity c
  join tblCounty co on co.county_id = c.county_id
  join tblProvince p on p.province_id = co.province_id;

Once that is created, you can:

select province_id from CityToProvince where city_id = [whatever];