Complex Query?

Rails
1

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.

2

I think I’d do something like:
Download.find(:all, :include => [:products, :download_type], :conditions
=> [“products.id=?”,params[:id]], :order => ‘download_type.position,
title’)

3

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 – [email protected]

4

Still haven’t quite figured this out…

5

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…

6

Here’s what I ended up doing, and it works out as expected: