On Mon, Jan 23, 2012 at 8:39 PM, Clem Rock <[email protected]> wrote:
> 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. Is this a correct statement or a typo? "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) > [/code] > > 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 > [/code] > Which paginator are you using? Which version of Rails? Taking abstraction of the effect the paginator may have ... Look in http://guides.rubyonrails.org/active_record_querying.html Start with the simplest cases in `rails console` and build up your query. Probably a style of @properties = Property.includes(:photos). includes(:layouts). where("properties.property_status='available'"). order("layouts.rent"). all will get you close (or maybe .joins(layouts) if you really need the INNER JOIN on layouts). Try a solution where you do _not_ set the select manually first. By the design of SQL querying language, the raw SQL will always return the property for each photo (if it is all executed in 1 SQL query). But Activerecord will consolidate the different returned records in 1 record in the @properties list (which then will :have_many photos). HTH, Peter -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
