SQL Query vs. Activerecord

double_join.sql

-- A little practice exploring a relatively complex SQL query, 
-- and comparing it to the ActiveRecord equivelent. 
-- This is close to something I implemented in a real Rails project.

-- This gets the first 10 applications in the database that have 'Games' as their primary genre.

SELECT app.title AS 'Application Title', g.name as 'Primary Genre' 
FROM   application AS app 
JOIN   genre_application AS g_app 
  ON   app.application_id = g_app.application_id 
JOIN   genre AS g 
  ON   g_app.genre_id = g.genre_id 
WHERE  g_app.is_primary = true 
AND    g.name = 'Games'
LIMIT  10;

-- +----------------------------------+---------------+
-- | Application Title                | Primary Genre |
-- +----------------------------------+---------------+
-- | PAC-MAN                          | Games         |
-- | Cro-Mag Rally                    | Games         |
-- | Solitaire City (Deluxe)          | Games         |
-- | Video Poker                      | Games         |
-- | Paper Football Signature Edition | Games         |
-- | iMahjong Premium                 | Games         |
-- | Blackjack                        | Games         |
-- | BeeCells                         | Games         |
-- | CubeRise 3D                      | Games         |
-- | ColorRise 3D                     | Games         |
-- +----------------------------------+---------------+

ruby_join.rb

# Here is the ActiveRecord equivelent to the SQL statement:

Applications.games.limit(10)

# However, this is a bit misleading, because there is a lot of background work getting this to work. 
# First, the associations needs to be made (Genre and GenreApplication associations not shown).

class Application < ActiveRecord::Base

  has_many :genre_application
  has_many :genre, through: :genre_application
  ...
end

# Then GenreApplication needs a scope to pick out games as primary genres. 
# Here I actually cheat a little - I hard coded the genre_id for games so we don't have to cross-reference with the Genre table to get the name.
# However, in a more dynamic execution, we would need to use a .joins() statement or a smarter scope.

class GenreApplication < ActiveRecord::Base

  # genre_id 6014 is the genre id for Games under the parent genre of Mobile Apps
  scope :games, lambda { where(is_primary: true, genre_id: 6014) }
  ...
end

# Lastly, the Application class needs a method to tie all this together:

class Application < ActiveRecord::Base
  ...
  
  def self.games
    joins(:genre_application).merge(GenreApplication.games)
  end
  
  ...
end

# And there you have it, the general equivalency. 
# It's not really an apples-to-apples comparison, 
# because very likely you won't have an application where you could choose either or, 
# but it's definitely interesting to see the two next to each other.