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.
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.
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
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')
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.
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)
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)
If you have an array of activerecord relations and want to merge them all, you can do
array.inject(:merge)
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")
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
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.
