rails union hack, how to pull two different queries together
activerecord, ruby, ruby-on-rails, sql, union
Solution
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.
Problem
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...