Best way to get around unions in Ecto

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%"
SELECT title FROM posts WHERE title LIKE "%string%"
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(, ^"%#{str}%"),
    select: c

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

What is the best way to get around this restriction?

1 Like

You can run SQL queries that aren’t supported by the Ecto API with Ecto.Adapters.SQL.query/4:

You can create a UNION query by using the convenient Ecto syntax on the individual parts of the query and then use Ecto.Adapters.SQL.to_sql/3 or Repo.to_sql/2 to concatenate them, like so:

def to_union_query(queries) do
  Enum.reduce(queries, fn(query, acc) ->
    acc <> " UNION " <> YourApp.Repo.to_sql(:all, query)

Alternatively, if you have no performance concerns about running multiple queries and manually merging the results into a list, you could also use Ecto.Multi.


Currently, I’m using a SQL statement with UNION via Ecto.query. I’m not happy with it; I’d rather use something like your example above. (When it gets really hairy I’ll implement elasticsearch)

Could you provide an example of implementing this with Ecto.Multi? That’s exactly what I’m looking for, but having trouble with it.

It seems like I was wrong in assuming you could use Ecto.Multi for the task. It doesn’t support simply retrieving records. So using something like to_union_query shown above will probably be the best solution.

1 Like

Are you using PostgreSQL?

That’s odd. Can’t you put a read query in somewhere? Is it only designed for piping the results of multiple consecutive writes?

Yes, using PostgresSQL. I am looking for a database-agnostic solution though, so writing SQL doesn’t vibe with me well.

I’m mostly asking because you mentioned Elastic Search. You could use Postgres full text search and have an easier time here.

1 Like