CODEWITHSUNDEEP
ruby-on-railsactiverecord
patrick
patrick

Reputation: 9722

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

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?

Upvotes: 1

Views: 58

Answers (4)

JuanBoca
JuanBoca

Reputation: 724

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

Upvotes: 1

K M Rakibul Islam
K M Rakibul Islam

Reputation: 34318

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)

Upvotes: 0

dimakura
dimakura

Reputation: 7655

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

Upvotes: 0

patrickh003
patrickh003

Reputation: 168

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

Upvotes: 0

Related Questions