Optimise this association?


#1

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