# Optimise this association?

**URL:** <https://www.ruby-forum.com/t/optimise-this-association/163848>\
**Category:** Rails\
**Created:** [March 25, 2009, 2:12pm UTC](https://www.ruby-forum.com/t/optimise-this-association/163848 "2009-03-25T14:12:29Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![cmaxvv](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/85f322/32.png) [@cmaxvv](https://www.ruby-forum.com/u/cmaxvv)\
**Post date:** [March 25, 2009, 2:12pm UTC](https://www.ruby-forum.com/t/optimise-this-association/163848/1 "2009-03-25T14:12:29Z")

</div>

hey all

In my schema, Lesson is many to many with Asset (through join table  
‘elements’), which are also many to many with Resource (through join  
table ‘items’). This is all done with totally standard “has\_many  
:through” -

Lesson  
has\_many :elements, :dependent =\> :destroy, :order =\> :position  
has\_many :assets, :through =\> :elements, :order =\> :position

Asset  
has\_many :items, :dependent =\> :destroy, :order =\> :position  
has\_many :resources, :through =\> :items, :order =\> :position

What i want to do now is set up an association for Lesson that gets “the  
distinct resources that are associated with any of its assets”. (I  
don’t care about position in this case)

I can think of ways to do it, eg

has\_many :resources, :finder\_sql =\> ‘select \* from resources where  
resources.id in (select items.resource\_id from items where  
items.asset\_id in (select elements.asset\_id from elements where  
elements.lesson\_id = #{id}))’

But this feels dirty and in fact doesn’t even work - it’s complaining  
about the brackets which seem to be getting broken when it inserts a  
‘count(\*) from elements’ segment in there.

What’s the ‘nicest’ way to do it?

thanks  
max