Paginating union_all query

I have 3 tables of which I am trying to do a UNION_ALL query. This is the query.

compares =
  Compare
  |> join(:left, [comp], u in assoc(comp, :user))
  |> join(:left, [comp], c in assoc(comp, :camera))
  |> select([comp, u, c], %{
    title: comp.name,
    exid: comp.exid,
    table: "compares",
    requested_by: u.email,
    status: comp.status,
    camera_id: c.exid,
    created_at: comp.inserted_at
  })

timelapses =
  Timelapse
  |> join(:left, [tl], u in assoc(tl, :user))
  |> join(:left, [tl], c in assoc(tl, :camera))
  |> select([tl, u, c], %{
    title: tl.title,
    exid: tl.exid,
    table: "timelapses",
    requested_by: u.email,
    status: tl.status,
    camera_id: c.exid,
    created_at: tl.inserted_at
  })

Archive
|> join(:left, [a], u in assoc(a, :user))
|> join(:left, [a], c in assoc(a, :camera))
|> select([a, u, c], %{
  title: a.title,
  exid: a.exid,
  table: "archives",
  requested_by: u.email,
  status: a.status,
  camera_id: c.exid,
  created_at: a.created_at
})
|> union_all(^compares)
|> union_all(^timelapses)

This gives me all the results but because of a lot of data, I want to paginate it, I tried using scrivener_ecto but it failed with such an error

* (Postgrex.Error) ERROR 42601 (syntax_error) each UNION query must have the same number of columns 

    query: SELECT count('*') FROM "archives" AS a0 LEFT OUTER JOIN "users" AS u1 ON u1."id" = a0."requested_by" LEFT OUTER JOIN "cameras" AS c2 ON c2."id" = a0."camera_id" UNION ALL (SELECT c0."name", c0."exid", 'compares', u1."email", c0."status", c2."exid", c0."inserted_at" FROM "compares" AS c0 LEFT OUTER JOIN "users" AS u1 ON u1."id" = c0."requested_by" LEFT OUTER JOIN "cameras" AS c2 ON c2."id" = c0."camera_id") UNION ALL (SELECT t0."title", t0."exid", 'timelapses', u1."email", t0."status", c2."exid", t0."inserted_at" FROM "timelapses" AS t0 LEFT OUTER JOIN "users" AS u1 ON u1."id" = t0."user_id" LEFT OUTER JOIN "cameras" AS c2 ON c2."id" = t0."camera_id")
    (ecto_sql 3.6.1) lib/ecto/adapters/sql.ex:749: Ecto.A

is there any possibility to apply pagination to the union_all query to have such data

%{
  from: 1,
  items: [],
  limit: 50,
  page: 1,
  to: 50,
  total: 3618
}

any help would be grateful, if you see anything in the query which is alarming or could be changed to something better then please do suggest as well. Thank you.

this has many other problems as well

such as ordering or sorting. Can you suggest me something else If I want to get data from 3 tables? instead of doing union_all?

Ecto.Query has limit/3 and offset/3. Have you tried these yet? There are some examples in the documentation of using these together for pagination.

1 Like

You can turn your query into a subquery with subquery/2. Scrivener can paginate these subqueries without problem, and you can apply sorting as usual. I’ve used union/2 instead of union_all/2, so I’m not sure if that will make any difference, but I’d guess not.

# ...
Archive
|> join(:left, [a], u in assoc(a, :user))
|> join(:left, [a], c in assoc(a, :camera))
|> select([a, u, c], %{
  title: a.title,
  exid: a.exid,
  table: "archives",
  requested_by: u.email,
  status: a.status,
  camera_id: c.exid,
  created_at: a.created_at
})
|> union_all(^compares)
|> union_all(^timelapses)
|> subquery()
# Apply sorting
|> Repo.paginate(params)
2 Likes

Hey friend! I had the same scenario: a query with two union_all. I didn’t find anything to solve this, but then I discover a way to solve it:

q = from i in subquery(query_with_unions)