active record query that will check the existence of specific fields through a has_many relationship?

Question

class GameSystem < ActiveRecord::Base
  has_many :cartridges
end

class Cartridge < ActiveRecord::Base
  belongs_to :game_system
end

I want to be able to do:

 GameSystem.joins(:cartridges)
   .where({:cartridge => { :name => 'Dragons Lair', :publisher => 'Cinematronics' })
   .where({:cartridge => { :name => 'Zaxxon', :publisher => 'Sega' })
   .limit(1)

In other words, tell me if there exists a game system where there was a Dragon's Lair cartridge by Cinematronics, AND a cartridge named Zaxxon by Sega...

Is there an activerecord friendly way to do this?

score 1 · Answer 1 · edited May 23 '17 at 11:52

You need to do two separate JOINS, or a sub query. If you want to be more Activerecord Friendly, you can do it with an automatic JOIN and one written by yourself, like this:

GameSystem.joins(:cartridges)
      .joins('JOIN cartridges AS c2 ON c2.game_system_id = game_systems.id')
      .where('cartridges.name = ? AND cartridges.publisher = ?', 'Dragons Lair', 'Cinematronics')
      .where('c2.name = ? AND c2.publisher = ?', 'Zaxxon', 'Sega')

Or with a sub query it would look like this:

GameSystem.joins(:cartridges)
.where('cartridges.name = ? AND cartridges.publisher = ?', 'Dragons Lair', 'Cinematronics')
.where(id: GameSystem.joins(:cartridges)
  .where('cartridges.name = ? AND cartridges.publisher = ?', 'Zaxxon', 'Sega'))

The sub query is more readable and clear, but sometimes it can be less performant depending on your DB engine. You can read about it on this very complete thread, and decide which one you prefer: Join vs. sub-query

K M Rakibul Islam · Accepted Answer · 2015-09-29T20:20:16.480

With a little touch of SQL, you can try this:

GameSystem.joins('JOIN cartridges AS cartridges1 ON cartridges1.game_system_id = game_systems.id')
    .joins('JOIN cartridges AS cartridges2 ON cartridges2.game_system_id = game_systems.id')
    .where('cartridges1.name = ? AND cartridges1.publisher = ?', 'Dragons Lair', 'Cinematronics')
    .where('cartridges2.name = ? AND cartridges2.publisher = ?', 'Zaxxon', 'Sega').limit(1)

score 0 · Answer 3 · answered Sep 29 '15 at 19:54

0

Since you want 2 different types of cartridge names you need an OR.

GameSystem.joins(:cartridges)
    .where("(cartridges.name = ? AND cartridges.publisher = ?) OR (cartridges.name = ? AND cartridges.publisher = ?)", 'Dragons Lair', 'Cinematronics', 'Zaxxon', 'Sega')

answered Sep 29 '15 at 19:54

patrickh003

168
1
5

I don't want an OR. It's supposed to match a game system where it has BOTH conditions met. – patrick Sep 29 '15 at 21:23
yeah. your right. in order to make this work I would need to group by gamesystem field having count(distinct name) > 1 and count(distinct publisher) > 1. not activerecord pretty – patrickh003 Sep 29 '15 at 21:53

dimakura · Answer 4 · 2015-09-29T20:02:37.403

To filter with two conditions on child records you should use two joins. You can't achieve this with single join (even with plain SQL). ActiveRecord is just a wrapper around standard SQL.

So it should looks something like:

GameSystem.joins('JOIN cartridges c1 ON c1.game_system_id = game_systems.id')
          .joins('JOIN cartridges c2 ON c2.game_system_id = game_systems.id')
          .where('c1.name = ? AND c1.publisher = ?', 'Dragons Lair', 'Cinematronics')
          .where('c2.name = ? AND c2.publisher = ?', 'Zaxxon', 'Sega')
          .any?
          # => true/false output
          # you can also use .to_a
          # or continue filtering as well

active record query that will check the existence of specific fields through a has_many relationship?

4 Answers4