Best way to get around unions in Ecto

Phoenix Forum Questions / Help
,
1

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 get around this restriction?

1 Like
2

You can run SQL queries that aren’t supported by the Ecto API with Ecto.Adapters.SQL.query/4:
https://hexdocs.pm/ecto/Ecto.Adapters.SQL.html#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)
  end)
end

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.

6 Likes
3

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.

4

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
5

Are you using PostgreSQL?

6

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

7

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

8

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

1 Like