# Complex Query?

**URL:** <https://www.ruby-forum.com/t/complex-query/163494>\
**Category:** Rails\
**Created:** [March 20, 2009, 10:47pm UTC](https://www.ruby-forum.com/t/complex-query/163494 "2009-03-20T22:47:53Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![partydrone](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/a6a055/32.png) [@partydrone](https://www.ruby-forum.com/u/partydrone)\
**Post date:** [March 20, 2009, 10:47pm UTC](https://www.ruby-forum.com/t/complex-query/163494/1 "2009-03-20T22:47:53Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![partydrone](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/a6a055/32.png) [@partydrone](https://www.ruby-forum.com/u/partydrone)\
**Post date:** [March 20, 2009, 11:36pm UTC](https://www.ruby-forum.com/t/complex-query/163494/2 "2009-03-20T23:36:33Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![partydrone](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/a6a055/32.png) [@partydrone](https://www.ruby-forum.com/u/partydrone)\
**Post date:** [March 21, 2009, 12:06am UTC](https://www.ruby-forum.com/t/complex-query/163494/3 "2009-03-21T00:06:57Z")

</div>

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 – [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)

---

<div class="post-metadata">

**Author:** ![partydrone](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/a6a055/32.png) [@partydrone](https://www.ruby-forum.com/u/partydrone)\
**Post date:** [March 23, 2009, 7:03pm UTC](https://www.ruby-forum.com/t/complex-query/163494/4 "2009-03-23T19:03:54Z")

</div>

Still haven’t quite figured this out…

---

<div class="post-metadata">

**Author:** ![partydrone](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/a6a055/32.png) [@partydrone](https://www.ruby-forum.com/u/partydrone)\
**Post date:** [March 21, 2009, 12:10am UTC](https://www.ruby-forum.com/t/complex-query/163494/5 "2009-03-21T00:10:28Z")

</div>

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…

---

<div class="post-metadata">

**Author:** ![partydrone](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/a6a055/32.png) [@partydrone](https://www.ruby-forum.com/u/partydrone)\
**Post date:** [March 23, 2009, 11:52pm UTC](https://www.ruby-forum.com/t/complex-query/163494/6 "2009-03-23T23:52:49Z")

</div>

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

> <https://gist.github.com/83829>