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 <[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."
>
>
>
>> 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 [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.
