0

My goal is to write the query below in the cleanest, most efficient way possible and minimize hitting the DB. Appreciate any guidance in advance.

I have retrieved some records that belong to a user, like below:

english_shows = @user.shows.where(language: 'english')

Let's say the shows belong to different categories (using a foreign key), so it looks like below:

<ActiveRecord::Relation [
  <Show id: 1, category_id: 1, title: 'Rick and Morty'>,
  <Show id: 2, category_id: 2, title: 'Black Mirror'>,
  <Show id: 3, category_id: 3, title: 'Stranger Things'>,
  <Show id: 4, category_id: 3, title: 'Game of Thrones'>,
  ...
]

If I want to get the titles of the shows for each category, I know I can use select like this. The same thing can be done with where, but this would cause an additional DB call. ([Edit] Actually, both would hit the DB twice).

# Using select
cartoons = english_shows.select { |show| show.category_id == Category.find_by(name: 'cartoon').id}.pluck(:title)

# Using where
cartoons = english_shows.where(category_id: Category.find_by(name: 'cartoon').id)pluck(:title)

However, the select method would still result in multiple lines of long code (in my actual use case I have more category types). Is it cleaner to loop through the records like this (taken from this SO answer)?

cartoons, science_fiction, fantasy = [], [], []
@cartoon_id = Category.find_by(name: 'cartoon')
@science_fiction_id = Category.find_by(name: 'cartoon')
@fantasy_id = Category.find_by(name: 'cartoon')

english_shows.each do |show|
  cartoons << show if show.category_id == @cartoon_id
  science_fiction << show if show.category_id == @science_fiction_id
  fantasy << show if show.category_id == @fantasy_id
end
reesaspieces
  • 1,600
  • 4
  • 18
  • 47
  • _"This would only hit the DB once"_ – where does `select` get the records from? Is `english_shows` already an array or a relation? – Stefan Jul 02 '19 at 08:59
  • I guess this understanding was misguided - when I tried earlier, it seemed like `select` just used what was in the variable, but trying it just now I realised it still hits the DB to get the value of `english_shows` again. In that case, my question still stands - is it better to loop through, use `select`, or use `where`? – reesaspieces Jul 02 '19 at 09:04
  • 2
    where will generally result in a far quicker execution - select will iterate through each record in the collection and apply the conditional to it - where will perform all of these operations in one transaction – Mark Jul 02 '19 at 09:08

1 Answers1

3

Try this:

english_shows
  .joins(:category)
  .select('shows.*, categories.name as category')
  .group_by(&:category)
nikamanish
  • 662
  • 4
  • 17