Forum: Ruby on Rails Optimise this association?

Announcement (2017-05-07): is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see and for other Rails- und Ruby-related community platforms.
Max W. (Guest)
on 2009-03-25 15:12
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" -

  has_many :elements, :dependent => :destroy, :order => :position
  has_many :assets, :through => :elements, :order => :position

  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 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?

This topic is locked and can not be replied to.