mercredi 26 décembre 2018

Rails: query for 2-step relation through join-table + has many relation

I am working on improving my database scheme. Currently I use a has_many through structure with a land model/table a shippingservice model/table and a postzone model/table, which connects land and shipping tables.

Has many through

I can query this way:

@shippingservices = @cart.available_shipping_services.includes(:lands, :postzones).where('postzones.land_id = ?', params[:id])

And get all shipping services for a given land (country).

But this way I end up with a lot of repetition in the postzone table and maintenance is quite difficult. It would be much easier if I could collect lands in zones with many lands and than connect shipping services to a zone. Just like in this scheme:

Join table + has many

How would I query in this case for all shipping services for a given land?

For a join table I would do something like:

@shippingservices = @cart.available_shipping_services.joins(:lands => :zones).where('lands_zones_join.land_id = ?', params[:id])

Which does not work, since I get:

Mysql::Error: Unknown column 'lands_zones_join.zone_id' in 'where clause': SELECT `shippingservices`.* FROM `shippingservices` 
INNER JOIN `zones` ON `zones`.`id` = `shippingservices`.`zone_id` 
INNER JOIN `lands_zones` ON `lands_zones`.`zone_id` = `zones`.`id` 
INNER JOIN `lands` ON `lands`.`id` = `lands_zones`.`land_id` 
INNER JOIN `lands_zones` `zones_lands_join` ON `zones_lands_join`.`land_id` = `lands`.`id` 
INNER JOIN `zones` `zones_lands` ON `zones_lands`.`id` = `zones_lands_join`.`zone_id` 
WHERE (weightmin <= 50 AND weightmax >= 50 AND height >= 3 AND shippingservices.shippingcarrier = '1') AND (length >= 210 AND width >= 149) AND (lands_zones_join.zone_id = '2')

Is this even possible? How can I get this to work?

Thank you in advance!

Aucun commentaire:

Enregistrer un commentaire