CODEWITHSUNDEEP
sqlruby-on-rails
user2167582
user2167582

Reputation: 6368

How to use rails scope or where clause to query OR AND conditions

so I'm trying to make queries that look like this

Car Model
has_many :colors
scope :for_cars, ->(color) { Car.join(:color).where(colors: { name: color}) }

for cars of green OR blue i can do this.

Car.for_cars(['green', 'blue'])

and I want to get cars that are green AND blue, how do i do that?

I'm using postgres.

Upvotes: 0

Views: 2620

Answers (3)

chumakoff
chumakoff

Reputation: 7034

Car.for_cars('green').merge(Car.for_cars('blue'))

This will generate a single query with two joins and return intersection. https://apidock.com/rails/v4.2.7/ActiveRecord/SpawnMethods/merge

But this approach gets ineffective as the number of desired colors grows (too many joins).

You can also do something like this:

wanted_colors = ['green', 'blue', 'red', 'yellow']

Car.for_cars(wanted_colors).group("cars.id").having("COUNT(colors.id) = #{wanted_colors.size}")

This approuch might defer depending on RDBMS you use.

Upvotes: 1

David Aldridge
David Aldridge

Reputation: 52346

One way to achieve this, without a scope, would be to have methods on Car:

def color_names
  colors.map(&:name)
end

def has_all_color_names(has_color_names = [])
  (color_names & has_color_names).size == has_color_names.size
end

def self.has_all_color_names(has_color_names = [])
  includes(:colors).select { |car| car.has_all_color_names has_color_names }
end

... and then to ...

Car.has_all_color_names %w(Green Blue)

Upvotes: 0

Shamsul Haque
Shamsul Haque

Reputation: 2451

Query in this way:-

scope :for_cars, ->(color) { Car.joins(:colors).where("colors.name" =>  color) }

Or in this way much simpler:-

scope :for_cars, ->(color) { Car.joins(:colors).where("colors.name":  color) }

Or in this way also if this scope is in Car model:-

scope :for_cars, ->(color) { joins(:colors).where("colors.name":  color) }

Note: -Tested in Rails 4.2.9

Check the Rails guides on joins

Upvotes: 2

Related Questions