rails union hack, how to pull two different queries together

Question

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...

Answer 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.

Answer 2

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