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!

--
Posted via http://www.ruby-forum.com/.

--
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 rubyonrails-talk@googlegroups.com.
To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.

Search Discussions

  • Peter Vandenabeele at Jan 23, 2012 at 8:48 pm

    On Mon, Jan 23, 2012 at 8:39 PM, Clem Rock 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 rubyonrails-talk@googlegroups.com.
    To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com.
    For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
  • Clem Rock at Jan 25, 2012 at 12:35 pm
    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:
    On Mon, Jan 23, 2012 at 8:39 PM, Clem Rock 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."


    layouts.*, photos.*"],
    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
    --
    Posted via http://www.ruby-forum.com/.

    --
    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 rubyonrails-talk@googlegroups.com.
    To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com.
    For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
  • Peter Vandenabeele at Jan 25, 2012 at 1:29 pm

    On Wed, Jan 25, 2012 at 1:35 PM, Clem Rock wrote:

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

    --
    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 rubyonrails-talk@googlegroups.com.
    To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com.
    For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouprubyonrails-talk @
categoriesrubyonrails
postedJan 23, '12 at 7:39p
activeJan 25, '12 at 1:29p
posts4
users2
websiterubyonrails.org
irc#RubyOnRails

2 users in discussion

Peter Vandenabeele: 2 posts Clem Rock: 2 posts

People

Translate

site design / logo © 2022 Grokbase