Using active record, I want to perform a lookup that returns a collection of items that have ALL matching id's.

Given that the below example matches on ANY id in the array, I am trying to figure out the syntax so that it will match when ALL of the id's match. (given that in this example there is a many to many relationship).

The array length of the id's is also variable which prohibits chaining .where()

x.where(id: [1,2])

Note: this question got removed before and there are a lot of answers for performing a sql "where in" but this question is about performing a sql "where and"

CodePudding user response：

You can use exec_query and execute your own bound query:

values          = [1, 2]
where_condition = values.map.with_index(1) { |_, index| "id = $#{index}" }.join(" AND ")
sql             = "SELECT * FROM table WHERE #{ where_condition }"
binds           = values.map { |i| ActiveRecord::Relation::QueryAttribute.new(nil, i, ActiveRecord::Type::Integer.new) }

ActiveRecord::Base.connection.exec_query(sql, nil, binds)

CodePudding user response：

I completely agree with @muistooshort's comment

where(id: [1,2]) doesn't make sense unless you're joining to an association table and in that case,..."where in" combined with HAVING [solves your problem].

But for the sake of answering the question and the assumption that id was just and example.

While @SebastianPalma's answer will work it will return an ActiveRecord::Result whereas most of the time the desire is an ActiveRecord::Relation.

We can achieve this by using Arel to build the where clause like so: (I modified the example to use description rather than id so that it makes more logical sense)

table = MyObject.arel_table 
values = ['Jamesla','Example']
where_clause = values.map {|v| table[:description].matches("%{v}%")}.reduce(&:and)
MyObject.where(where_clause)  

This will result in the following SQL query:

SELECT 
  my_objects.* 
FROM 
  my_objects
WHERE 
  my_objects.description LIKE '%Jamesla%' 
  AND my_objects.description LIKE '%Example%'