One to many query for 3 tables

rubyonrails-talk
1

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) [/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]

Any help would be greatly appreciated!

2

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:


>     @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:


> 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
>

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

3

Peter - big thanks for your help. You were right - I meant to say "the layouts table can have many photos"

Looks like this was the winner for me:

  @properties = Property.paginate(:page => params[:page], :per_page => 20).includes(:photos).includes(:layouts).order("properties.modify_date, layouts.modify_date").all

Cheers!

Peter Vandenabeele wrote in post #1042203:

4

TL;DR : remove the ‘.all’ at the end

In my example, I terminated with ‘.all’ so that you would get a simple Array as the result.

Once you have settled on a certain formula for the @properties, it might be better to not longer write the ‘.all’ at the end of the chain. Without the .all’ , you will get an ActiveRecord::Relation on which you can continue to chain

conditions etc. The real query will then only be done in a later stage of the calculation when the description of the ActiveRecord::Relation is complete.

If you ever test this in rails console, it will seem that the query is always done

immediately. That is because irb calls .inspect on the last result of the line.

One trick to make this work in rails console is to add ; nil at the end:

1.9.3-p0 :030 > p1 = Parent.order(“parents.name”) ; nil

=> nil 1.9.3-p0 :031 > p2 = p1.where(“NOT parents.name IS NULL”) ; nil => nil 1.9.3-p0 :032 > p3 = p2.includes(:child) ; nil => nil 1.9.3-p0 :033 > p3.all

Parent Load (0.6ms) SELECT “parents”.* FROM “parents” WHERE (NOT parents.name IS NULL) ORDER BY parents.name Child Load (0.4ms) SELECT “children”.* FROM “children” WHERE “children”.“parent_id” IN (1, 2, 3)

=> [#<Parent id: 1, name: “dad”, created_at: “2012-01-24 10:05:43”, updated_at: “2012-01-24 10:05:43”>, #<Parent id: 2, name: “dad”, created_at: “2012-01-24 10:06:59”, updated_at: “2012-01-24 10:06:59”>, #<Parent id: 3, name: “mom”, created_at: “2012-01-24 10:07:42”, updated_at: “2012-01-24 10:07:42”>]

1.9.3-p0 :034 > p4 = p3.order(“children.created_at”) ; nil => nil 1.9.3-p0 :035 > p4.all SQL (0.7ms) SELECT “parents”.“id” AS t0_r0, “parents”.“name” AS t0_r1, “parents”.“created_at” AS t0_r2, “parents”.“updated_at” AS t0_r3, “children”.“id” AS t1_r0, “children”.“name” AS t1_r1, “children”.“parent_id” AS t1_r2, “children”.“created_at” AS t1_r3, “children”.“updated_at” AS t1_r4 FROM “parents” LEFT OUTER JOIN “children” ON “children”.“parent_id” = “parents”.“id” WHERE (NOT parents.name IS NULL) ORDER BY parents.name, children.created_at

=> [#<Parent id: 1, name: “dad”, created_at: “2012-01-24 10:05:43”, updated_at: “2012-01-24 10:05:43”>, #<Parent id: 2, name: “dad”, created_at: “2012-01-24 10:06:59”, updated_at: “2012-01-24 10:06:59”>, #<Parent id: 3, name: “mom”, created_at: “2012-01-24 10:07:42”, updated_at: “2012-01-24 10:07:42”>]

HTH,

Peter