Doing SQL Union in Rails 4 and Rails 5

The Universe recently conspired to make me write a sql union query. This details the story.

In Set theory, the union of a collection of sets is the set of all elements in the collection

In SQL, union is used to combine the result from multiple SELECT statements into a single result set.

It just says: “I want results that satisfy conditionA AND results that satisfy conditionB AND results that satisfy conditionC etc”.

Example:

select id, username from users where company = 'bbc'
union
select id, username from users where city = 'London';

Notice that the columns are required to be same (in data type only), but the columns in conditions can be different.

Realize that this can be written using sql ‘OR’ too.

select id, username from users where company = 'bbc' or city = 'London';

If you are new to programming in general, you might tend to expect this query above returns can return only results that satisfy either of the condition, even if there are records that satisfy both the contions. After all, in programming, we expect a || b to return only a or b and not both a and b. But the sql’s OR is different from programming OR. The sql’s OR represents a set UNION operation. So you get results that satisfy both conditions.

In Rails, there’s no support for direct UNION operation. But we have support for the OR operation since Rails 5.

Here’s an example.

Let’s say we have an Offer model with a variety of different scopes.

# Offers for specific person in specific place
Offer.for_person_and_place(person, place)

# Offers for a specific place that applies to all people there (person_id is nil)
Offer.for_any_person(place)

# Offers for a specific person that applies to any place (place_id is nil)
Offer.for_any_place(person)

# Offers for any person in any place (both keys are nil)
Offer.for_anyperson_any_place

So if the requirement is to write a funtion that takes a person and place as inputs (that can be nil) and returns all possible offers that satisfy any of the conditions, then it would look like this:

In Rails 5:

def offers(person, place)
  Offer.for_person_and_place(person, place)
    .or(Offer.for_any_person(place))
    .or(Offer.for_any_place(person))
    .or(Offer.for_anyperson_any_place)
end

But since Rails 4 or 3 doesn’t have the OR support, we can write the query using UNION like so:

def offers(person, place)
  sqls = []
  sqls << Offer.for_person_and_place(person, place).to_sql
  sqls << Offer.for_any_person(place).to_sql
  sqls << Offer.for_any_place(person).to_sql
  sqls << Offer.for_anyperson_any_place.to_sql
  Offer.from("(#{sqls.join(' UNION ')}) AS offers")
end

So that’s how you do sql Union in Rails.

Thoughts on the post are welcome! Mail me at prasanna@npras.in or .