January 23rd, 2012, 01:38 PM
|
|
Contributing User
|
|
Join Date: Jul 2004
Posts: 31
Time spent in forums: 3 h 30 m 32 sec
Reputation Power: 0
|
|
|
One to many query for 3 tables
Hello,
I am looking for a join query between 3 tables that will give me the results I'm looking for.
I have a properties tables that has many layouts and many photos as well. The layouts table can also, have many layouts. When I run the query, I want to show a list of properties w/ one main photo from the photos table, and a list of layouts per property. Right now, the query I have will show a list of properties, but properties will be displayed multiple times if there are more than one photos. Here's my current query in rails form:
Code:
@properties = Property.paginate(:page => params[:page],
:select => ["properties.*, layouts.*, photos.*"],
:joins => ["INNER JOIN layouts ON layouts.property_id = properties.property_id LEFT JOIN photos ON photos.property_id = properties.property_id"],
:per_page => 20)
or, in raw sql:
Code:
SELECT properties.*, layouts.*, photos.photo_file_name FROM `properties` INNER JOIN layouts ON layouts.property_id = properties.property_id LEFT JOIN photos ON photos.property_id = properties.property_id WHERE (properties.property_status='available') ORDER BY layouts.rent LIMIT 20 OFFSET 0
Any help would be greatly appreciated!
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
