rails union hack, how to pull two different queries together

sql ruby-on-rails ruby activerecord union
holden picture holden · Oct 18, 2009 · Viewed 11.3k times · Source

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

Damien MATHIEU picture Damien MATHIEU · Oct 18, 2009

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.

Related questions

Rails 4 LIKE query - ActiveRecord adds quotes

I am trying to do a like query like so def self.search(search, page = 1 ) paginate :per_page => 5, :page => page, :conditions => ["name LIKE '%?%' OR postal_code like '%?%'", search, search], order => 'name' end …

Read More
ActiveRecord Arel OR condition

How can you combine 2 different conditions using logical OR instead of AND? NOTE: 2 conditions are generated as rails scopes and can't be easily changed into something like where("x or y") directly. Simple example: admins = User.where(:kind => :admin) …

Read More
How Do I Search Between a Date Range, Using the ActiveRecord Model?

I am new to both Ruby and ActiveRecord. I currently have a need to modify and existing piece of code to add a date range in the select. The current piece goes like this: ReportsThirdparty.find(:all, :conditions => {:site_…

Read More