rails union hack, how to pull two different queries together
Solution 1
Doing an UNION query is not natively possible with ActiveRecord. So there are two solutions :
- Using find_by_sql to build your query as you want it. I wouldn't advise for it.
- Using a plugin like union to do a UNION sql query.
Solution 2
I found a neat hack using select . For example if you want to make a union between User and OtherUser .
User.select('id from other_users union select id')
this will generate this SQL
"SELECT id from other_users union select id FROM users "
If you have scopes with the conditions you can use the ActiveRecord::Relation where_values method
condition = OtherUser.example_condtion_scope.where_values.join(' ')
User.select("id from other_users where #{contition}")
Solution 3
Using the union plugin, it now works beautifully thanks:
def self.ajax3(search)
Location.union( [{ :select => 'city AS keyword, country AS sideinfo',
:joins => :hotels,
:conditions => [ 'email IS NOT NULL AND city LIKE ?', "#{search}%" ]},
{ :select => 'country AS keyword, "Country" AS sideinfo',
:joins => :hotels,
:conditions => [ 'email IS NOT NULL AND country LIKE ?', "#{search}%" ]}] )
end
Solution 4
This is now possible in Rails 4,
locations = Location.arel_table
hotels = Hotel.arel_table
countries = Location
.select(locations[:country].as("keyword"))
.joins(:hotels)
.where(hotels[:email].not_eq(nil))
.where(locations[:country].matches("#{search}%"))
cities = Location
.select(locations[:city].as("keyword"))
.joins(:hotels)
.where(hotels[:email].not_eq(nil))
.where(locations[:city].matches("#{search}%"))
union = countries.union(cities)
result = Location.from(locations.create_table_alias(union, :locations).to_sql)
Related videos on Youtube
holden
Updated on June 26, 2020Comments
-
holden almost 4 years
I have a query which searches two separate fields in the same table... looking for locations which are most likely a specific city, but could also be a country... ie the need for two fields.
Table looks like:
Country City Germany Aachen USA Amarillo USA Austin
Result:
Keyword Sideinfo Aachen Germany USA Country Austin USA Germany Country
Basically I'm wondering if there is a more concise way to do this because I had to use two separate queries then add them together, sort them, etc. (which works fine):
def self.ajax(search) countries = Location.find(:all, :select=> 'country AS keyword, "Country" AS sideinfo', :joins => :hotels, :conditions => [ 'hotels.email IS NOT NULL AND country LIKE ?', "#{search}%" ], :group => :country ) cities = Location.find(:all, :select=> 'city AS keyword, country AS sideinfo', :joins => :hotels, :conditions => [ 'hotels.email IS NOT NULL AND city LIKE ?', "#{search}%" ], :group => :city ) out = cities + countries out = out.sort { |a,b| a.keyword <=> b.keyword } out.first(8) end
I couldn't find any information on how to unions using ActiveRecord...
-
Dave Schweisguth almost 10 yearsThis question discusses ways to use or fake unions in ActiveRecord: stackoverflow.com/questions/6686920/activerecord-query-union
-
-
Bill Leeper about 12 yearsUnion is 3 years old now. Anybody got a more up to date solution
-
Mudassir Ali almost 9 years@BillLeeper although your comment was posted in '12, check my answer in case you are still looking for it
-
lingceng over 8 years@BillLeeper github.com/brianhempel/active_record_union is a better gem. Use unions on ActiveRecord scopes without ugliness.