Rails has_and_belongs_to_many join across 3 tables
- by Birdman
I have a three models: listing, category, and site. There is a many to many relationship between listing and site and there is a many to many relationship between listing and category. A listing thus belongs to one or more sites and one or more categories (a listing can appear on multiple sites and multiple categories).
Given a site id and a category id, I want a set of all the listings. I understand how to get the listings from a site id (listings = site.listings) and how to get the listings from a category id (listings = category.listings), but how do I get the triple join of the set of listings from both a site id and category id?
The sql would look something like (sit_id and cat_id are input):
select l.name from listings l, categories_listings cl, listings_sites, ls where
cl.category_id = cat_id and ls.site_id = sit_id and cl.listing_id = l.id and ls.listing_id = l.id
Thanks in advance
