Forum: Ruby on Rails Optimise this association?

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
73c04e9ef9ca435c5b19a2e765ae6d20?d=identicon&s=25 Max Williams (max-williams)
on 2009-03-25 14: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" -

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
This topic is locked and can not be replied to.