CODEWITHSUNDEEP
ruby-on-railsrubyruby-on-rails-4activerecord
ruipin
ruipin

Reputation: 157

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
end

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

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

class UsersShowsJoin
  belongs_to :user
  belongs_to :show
end

class UsersEpisodesJoin
  belongs_to :user
  belongs_to :episode
end

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 filter_specials. 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?
end
# 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
end

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

Upvotes: 1

Views: 50

Answers (1)

Michael Gaskill
Michael Gaskill

Reputation: 8042

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})

or:

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

For the second query, you can use this:

user.shows.includes(:episodes).all

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:

user.shows.includes(:episodes).order("shows.title")

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.

Upvotes: 2

Related Questions