Combine two ActiveRecord::Relation objects
Ruby on-RailsRails ActiverecordArelRuby on-Rails Problem Overview
Suppose I have the following two objects:
first_name_relation = User.where(:first_name => 'Tobias') # ActiveRecord::Relation
last_name_relation = User.where(:last_name => 'Fünke') # ActiveRecord::Relation
is it possible to combine the two relations to produce one ActiveRecord::Relation
object containing both conditions?
Note: I'm aware that I can chain the wheres to get this behavior, what I'm really interested in is the case where I have two separate ActiveRecord::Relation
objects.
Ruby on-Rails Solutions
Solution 1 - Ruby on-Rails
If you want to combine using AND
(intersection), use merge
:
first_name_relation.merge(last_name_relation)
If you want to combine using OR
(union), use or
†:
first_name_relation.or(last_name_relation)
† Only in ActiveRecord 5+; for 4.2 install the where-or backport.
Solution 2 - Ruby on-Rails
Relation objects can be converted to arrays. This negates being able to use any ActiveRecord methods on them afterwards, but I didn't need to. I did this:
name_relation = first_name_relation + last_name_relation
Ruby 1.9, rails 3.2
Solution 3 - Ruby on-Rails
merge
actually doesn't work like OR
. It's simply intersection (AND
)
I struggled with this problem to combine to ActiveRecord::Relation objects into one and I didn't found any working solution for me.
Instead of searching for right method creating an union from these two sets, I focused on algebra of sets. You can do it in different way using De Morgan's law
ActiveRecord provides merge method (AND) and also you can use not method or none_of (NOT).
search.where.none_of(search.where.not(id: ids_to_exclude).merge(search.where.not("title ILIKE ?", "%#{query}%")))
You have here (A u B)' = A' ^ B'
UPDATE: The solution above is good for more complex cases. In your case smth like that will be enough:
User.where('first_name LIKE ? OR last_name LIKE ?', 'Tobias', 'Fünke')
Solution 4 - Ruby on-Rails
I've been able to accomplish this, even in many odd situations, by using Rails' built-in Arel.
User.where(
User.arel_table[:first_name].eq('Tobias').or(
User.arel_table[:last_name].eq('Fünke')
)
)
This merges both ActiveRecord relations by using Arel's or.
Merge, as was suggested here, didn't work for me. It dropped the 2nd set of relation objects from the results.
Solution 5 - Ruby on-Rails
There is a gem called active_record_union that might be what you are looking for.
It's example usages is the following:
current_user.posts.union(Post.published)
current_user.posts.union(Post.published).where(id: [6, 7])
current_user.posts.union("published_at < ?", Time.now)
user_1.posts.union(user_2.posts).union(Post.published)
user_1.posts.union_all(user_2.posts)
Solution 6 - Ruby on-Rails
This is how I've "handled" it if you use pluck to get an identifier for each of the records, join the arrays together and then finally do a query for those joined ids:
transaction_ids = @club.type_a_trans.pluck(:id) + @club.type_b_transactions.pluck(:id) + @club.type_c_transactions.pluck(:id)
@transactions = Transaction.where(id: transaction_ids).limit(100)
Solution 7 - Ruby on-Rails
If you have an array of activerecord relations and want to merge them all, you can do
array.inject(:merge)
Solution 8 - Ruby on-Rails
Brute force it:
first_name_relation = User.where(:first_name => 'Tobias') # ActiveRecord::Relation
last_name_relation = User.where(:last_name => 'Fünke') # ActiveRecord::Relation
all_name_relations = User.none
first_name_relation.each do |ar|
all_name_relations.new(ar)
end
last_name_relation.each do |ar|
all_name_relations.new(ar)
end
Solution 9 - Ruby on-Rails
Hopefully this is useful to someone out there -- you can make a second query to find the matches by id:
ids = last_name_relation.ids + first_name_relation.ids
User.where(id: ids)
I realize this may not be the most efficient with 3 database requests, but it gets the job done and is simple to understand.
Solution 10 - Ruby on-Rails
In the case where using or is not compactible, I use something like this to get the ActiveRecord_Relation object
User.from("(#{complex_raw_query} UNION #{complex_raw_query}) AS users")