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.