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

This question discusses ways to use or fake unions in ActiveRecord: http://stackoverflow.com/questions/6686920/activerecord-query-union — Dave Schweisguth, Aug 05 '14 at 02:17

score 8 · Accepted Answer · answered Oct 18 '09 at 11:12

8

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.

answered Oct 18 '09 at 11:12

Damien MATHIEU

31,924
13
86
94

4

Union is 3 years old now. Anybody got a more up to date solution – Bill Leeper Apr 19 '12 at 22:24
@BillLeeper although your comment was posted in '12, check my answer in case you are still looking for it – Mudassir Ali Jun 26 '15 at 07:45
@BillLeeper https://github.com/brianhempel/active_record_union is a better gem. Use unions on ActiveRecord scopes without ugliness. – lingceng Oct 09 '15 at 02:35

score 3 · Answer 2 · answered Nov 30 '12 at 20:31

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}")

score 2 · Answer 3 · answered Oct 19 '09 at 08:50

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

score 2 · Answer 4 · answered Jun 26 '15 at 07:44

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)

rails union hack, how to pull two different queries together

4 Answers4

Linked