honktronic honktronic - 4 months ago 12
Ruby Question

Rails: Aggregate queries through association

In rails, is there an ActiveRecord way of doing an aggregate query through a

has_many :through
association?

EDIT: I'm looking for a database-driven solution, not a bunch of Ruby iterating/munging.

EDIT #2: I screwed this up. The Venue model is also many-to-many with Show.

For instance. Imagine something (contrived) like this:

# Venue<->Show is many-to-many.
# Show<->Performer is many-to-many.

class Venue < ActiveRecord::Base
has_many :bookings
has_many :shows, through: :bookings
end

# Note "show" here would be "The Lion King," not a specific
# performance on a specific date.
class Show < ActiveRecord::Base
has_many :bookings
has_many :venues, through: :bookings

has_many :engagements
has_many :performers, through: :engagements
end

class Performer < ActiveRecord::Base
has_many :engagements
has_many :shows, through: :engagements
end

# Just a simple join model.
class Engagement < ActiveRecord::Base
belongs_to :show
belongs_to :performer
end

# Also a join model.
class Booking < ActiveRecord::Base
belongs_to :venue
belongs_to :show
end


How might one perform a query on
Venue
which returns a unique set of all
Performer
s that have ever performed there?

Thanks!

Answer

Since you want a collection of Performers at the end, start with that model. We know we're looking for a specific Venue, so let's see which associations we can use to get there.

# Performer
has_many :shows, through: :engagements

# Show
has_many :venues, through: :bookings

Bingo! We can go from Performers to Shows to Venues (Rails will figure out the joins from Engagements to Shows and Bookings to Venues automatically).

ActiveRecord supports joining on associations using the association name (Note: this only works for INNER joins): http://guides.rubyonrails.org/active_record_querying.html#using-array-hash-of-named-associations

After reading that querying guide, we know we can join on nested associations using a Hash syntax. That gets us to

Performer.joins(shows: :venues)

But we can't stop there because then we'll get all Performers that have played at least one Show at at least one Venue (with duplicates of any Performers in more than one Show, Engagement, Booking or Venue—we'll deal with these momentarily). In order to narrow the collection down, we have to add a WHERE clause. Section 12.3 of the linked guide explains that we can add Hash conditions to joined tables. Narrowing down the venue looks like

venue = Venue.find(params[:venue_id])
Performer.joins(shows: :venues).where(venues: {id: venue.id})

Now we have all Performers that have played in all Shows at this Venue! But wait, we still have duplicate Performers if they've played in more than one Show (or Engagement). In order to remove the duplicates, we use uniq.

Putting it all together gives us

venue = Venue.find(params[:venue_id])
Performer.joins(shows: :venues).where(venues: {id: venue.id}).uniq