Forum: Ruby on Rails Complex Query?

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
829f2eef24c244ea33165adde8bd36d5?d=identicon&s=25 partydrone (Guest)
on 2009-03-20 22:47
(Received via mailing list)
I need to perform a Rails find with the following data model in my
controller:


download_types  -->  downloads  -->  downloads_products  <--  products


I want to grab all downloads for a given product_id and list them by
download type. Download types is a sortable list (using a position
column in the table, and I want to make sure the download types are
sorted by that column). I'm thinking of something like this:

DownloadType.all(:include => :downloads, :conditions => ['product.id
= ?', params[:id]], :order => 'position, download.title')

I'm trying to figure out what to put in the middle, some sort
of :joins clause, but I'm not sure how it should look. In my view, it
seems the most logical to me to start looping through download types,
then loop through downloads for the given download type.

Any suggestions?

Thanks.
48af402ac1a5a73b29d0d81438aaeddb?d=identicon&s=25 Sharagoz -- (sharagoz)
on 2009-03-20 23:36
I think I'd do something like:
Download.find(:all, :include => [:products, :download_type], :conditions
=> ["products.id=?",params[:id]], :order => 'download_type.position,
title')
829f2eef24c244ea33165adde8bd36d5?d=identicon&s=25 Party Drone (Guest)
on 2009-03-21 00:06
(Received via mailing list)
I started out there, but then what's the best way to first iterate
through download_types, and then iterate through downloads for each
download_type?


On Mar 20, 4:36 pm, Sharagoz -- <rails-mailing-l...@andreas-s.net>
829f2eef24c244ea33165adde8bd36d5?d=identicon&s=25 Party Drone (Guest)
on 2009-03-21 00:10
(Received via mailing list)
I think I have a start with this:

DownloadType.all(:include => :downloads, :joins => [:downloads
=> :products], :conditions => ['product.id = ?', id], :order =>
'position, download.title')

But I get the following error:

SQLite3::SQLException: ambiguous column name: downloads.id: SELECT
"download_types"."id" AS t0_r0, "download_types"."name" AS t0_r1,
"download_types"."position" AS t0_r2, "download_types"."created_at" AS
t0_r3, "download_types"."updated_at" AS t0_r4, "downloads"."id" AS
t1_r0, "downloads"."title" AS t1_r1, "downloads"."part_number" AS
t1_r2, "downloads"."download_type_id" AS t1_r3,
"downloads"."created_at" AS t1_r4, "downloads"."updated_at" AS t1_r5,
"downloads"."download_file_name" AS t1_r6,
"downloads"."download_content_type" AS t1_r7,
"downloads"."download_file_size" AS t1_r8,
"downloads"."download_updated_at" AS t1_r9 FROM "download_types" LEFT
OUTER JOIN "downloads" ON downloads.download_type_id =
download_types.id INNER JOIN "downloads" ON downloads.download_type_id
= download_types.id INNER JOIN "downloads_products" ON
"downloads_products".download_id = "downloads".id INNER JOIN
"products" ON "products".id = "downloads_products".product_id WHERE
(product.id = 58) ORDER BY position, download.title

I think I'm almost there…
829f2eef24c244ea33165adde8bd36d5?d=identicon&s=25 Party Drone (Guest)
on 2009-03-23 19:03
(Received via mailing list)
Still haven't quite figured this out…
829f2eef24c244ea33165adde8bd36d5?d=identicon&s=25 Party Drone (Guest)
on 2009-03-23 23:52
(Received via mailing list)
Here's what I ended up doing, and it works out as expected:

http://gist.github.com/83829
This topic is locked and can not be replied to.