Complex Query?

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 => [' = ?', 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?


I think I'd do something like: Download.find(:all, :include => [:products, :download_type], :conditions => ["",params[:id]], :order => 'download_type.position, title')

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?

I think I have a start with this:

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

But I get the following error:

SQLite3::SQLException: ambiguous column name: 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 = INNER JOIN "downloads" ON downloads.download_type_id = INNER JOIN "downloads_products" ON "downloads_products".download_id = "downloads".id INNER JOIN "products" ON "products".id = "downloads_products".product_id WHERE ( = 58) ORDER BY position, download.title

I think I'm almost there…

Still haven't quite figured this out…

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