honktronic honktronic - 1 year ago 49
Ruby Question

Rails: Aggregate queries through association

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

has_many :through

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

# 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

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

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

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

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


Answer Source

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