Performing Unions with Ecto

Question

I'm running a search feature on three tables in my Phoenix app, and I want to join them using something like SQL's UNION operator.

I have three tables:

mix phx.gen.json Accounts User users handle:string email:string
mix phx.gen.json Content Post posts title:string content:string
mix phx.gen.json Content Category categories name:string

Let's assume there are no foreign keys or linking tables.

If I wanted to run a search across these in SQL, I would do something like this:

SELECT handle FROM users WHERE handle LIKE "%string%"
UNION
SELECT title FROM posts WHERE title LIKE "%string%"
UNION
SELECT name FROM categories WHERE name LIKE "%string%"

However, Ecto 2 doesn't seem to support unions. I want to do something like this:

query1 =
  from u in User,
    where: ilike(u.handle, ^"%#{str}%"),
    select: u

query2 =
  from p in Post,
    where: ilike(p.title, ^"%#{str}%"),
    select: p

query3 =
  from c in Category,
    where: ilike(c.name, ^"%#{str}%"),
    select: c

union = Ecto.SomethingLikeAUnion([query1, query2, query3])
result = Repo.all(union)

What is the best way to do this?

Evansville

Not what you were asking, but... This is a case where I often use something like Elasticsearch. It does a much better job of searching and the UNION would be a non-issue. Totally understandable if you hesitate to add an extra technology to your app, though. – Deryl

Answer 1

Ecto doesn't support unions at the moment. Until Ecto adds support for unions, the best way would be to use raw SQL with Repo.query/2:

MyApp.Repo.query("""
  SELECT handle FROM users WHERE handle LIKE $1
  UNION
  SELECT title FROM posts WHERE title LIKE $1
  UNION
  SELECT name FROM categories WHERE name LIKE $1
""", ["%#{str}%"])

Bedrock

Answer 2

Looks like UNION and UNION ALL were added here.

and are documented here.

supplier_query = from s in Supplier, select: s.city
from c in Customer, select: c.city, union: ^supplier_query

Adorne

This needs to be updated as the accepted answer – Dormie