rpinheiro rpinheiro - 2 years ago 70
Ruby Question

Active Record: Query different associations that belong to the same parent

I have a Rails application where users may select the shows they are watching, and which episodes they have already watched. The ActiveRecord associations look somewhat like this:

class User
has_many :episodes_joins, :dependent => :delete_all, class_name: 'UsersEpisodesJoin'
has_many :episodes, through: :episodes_joins
has_many :shows_joins, :dependent => :delete_all, class_name: 'UsersShowsJoin'
has_many :shows, through: :shows_joins

class Show
has_many :episodes, dependent: :destroy
has_many :users_shows_joins, :dependent => :delete_all
has_many :users, through: :users_shows_joins

class Episode
belongs_to :show
has_many :users_episodes_joins, :dependent => :delete_all
has_many :users, through: :users_episodes_joins

class UsersShowsJoin
belongs_to :user
belongs_to :show

class UsersEpisodesJoin
belongs_to :user
belongs_to :episode

I wish to allow users to filter "special episodes" for each show individually ("special episodes" have episode.special? set to true). For this, I was thinking of adding a boolean column in the UsersShowsJoins table named
. I now wish to do something like this:

episodes = user.episodes.where(special: false).to_a
user.episodes.where(special: true) do |epi|
show_join = UsersShowsJoins.where(user_id: user.id, show_id: epi.show.id).first
episodes << epi if show_join.nil? || !show_join.filter_specials?
# do something with 'episodes',
# which contains all non-special episodes,
# as well as special episodes of shows for which the user is not filtering these

I know this is a very hackish and slow implementation that will do tons of DB queries, and I'm sure there's a much better way to do it, possibly even with a single query.

Additionally, I want to be able to query the DB for all shows a user has selected, and pre-load the corresponding episodes that have a UsersEpisodesJoins row for the same user. Something like:

shows = user.shows.all
h = {}
shows.each do |show|
episodes = user.episodes.where(show_id: show.id).all
h[show] = episodes

# do something with h,
# which contains a user's watched episodes for each selected show

How can I write these queries efficiently, so that I don't incur N+1 problems for complex queries?

Answer Source

You should be able to write the special episodes filter like this:

show_joins = UsersShowsJoins.joins(:shows, :users).where(episodes: { special: true})

This will join both shows and users by their common relationship, and filter for the episodes that have special set to true.

There are variations that you could write, based on what type of object you wanted to work with as the main object:

shows = Show.joins(:users, :episodes).where(episodes: {special: true})


episodes = Episode.joins(shows: :users).where(special: true)

For the second query, you can use this:


That should pre-load the episodes for the shows that the user is watching. You could add where conditions, group-ing, and even order conditions if you choose, like so:


to order the results by the show title (assuming that there is a show title field).

The Active Record Query Interface guide has some great examples in the Joining Tables section. It's worth reading through to get some ideas about how to efficiently do complex querying like this.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download