CODEWITHSUNDEEP
mysqlsqlruby-on-railsrubyuniq
Joe Morano
Joe Morano

Reputation: 1895

Calling uniq on a nested joins table

I have three tables, Show, Episode, and Character. Each Show has_many Episodes and Characters.

class Show < ActiveRecord::Base
  has_many :episodes
  has_many :characters

class Episode < ActiveRecord::Base
  belongs_to :show

class Character < ActiveRecord::Base
  belongs_to :show

Episode has the attributes :air_date and :episode_number. Show has an attribute :title.

I have a list of Characters @characters. What I want to accomplish is to print a list of the Shows associated with those Characters, ordered by the air date of the Show's second Episode.

<% @characters.includes(show: :episodes).where(episodes: {episode_number: 2}).order('episodes.air_date DESC').each do |character| %>
  <%= character.show.title %>
<% end %>

This almost works, but some Shows have multiple characters, so in the printed list, some Shows are listed multiple times.

Is it possible to apply the ruby method uniq to the Shows table in my query, so that each Show is printed a maximum of one time?

Upvotes: 0

Views: 45

Answers (2)

Marwan Sulaiman
Marwan Sulaiman

Reputation: 79

One thing I usually do in your case, is that I look at what I want to accomplish in SQL, write it out, test that it works, then translate it to ActiveRecord or in some cases keep it as a custom SQL query using find_by_sql

So here is what you want to accomplish in SQL: 

Show.find_by_sql(<<-SQL, character_ids_array)
  SELECT
    DISTINCT ON (shows.id) shows.*
  FROM
    characters
  JOIN
    shows on characters.show_id = shows.id
  JOIN
    episodes on shows.id = episodes.show_id
  WHERE
    characters.id IN #{character_ids_array} AND epsiodes.epsiode_number = 2
  ORDER BY
    episodes.air_date DESC
SQL

This is unrelated to your main question, but I would suggest doing your logic in the Controller not in ERB. For the sake of convention :)

Also, make sure when you translate to ActiveRecord your query is cashed all the way to the end to avoid N+1 queries.

Upvotes: 0

John Pollard
John Pollard

Reputation: 3899

What about a group by?

<% @characters.includes(show: :episodes).where(episodes: {episode_number: 2}).order('episodes.air_date DESC').group_by(&:show).each do |show, characters|
  <%= show.title %>
<% end %>

Upvotes: 1

Related Questions