#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2003
    Posts
    174
    Rep Power
    12

    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!
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2003
    Posts
    174
    Rep Power
    12
    found the solution:

    @properties = Property.paginate(age => params[age], er_page => 20).includes(hotos).includes(:layouts).order("properties.modify_date, layouts.modify_date").all



    Cheers!

IMN logo majestic logo threadwatch logo seochat tools logo