Hi,
I try to union rows select from two tables, which have diff cols, and order by insert time.
Table text:
id: integer,
text: string,
inserted_at, date,
user_id, integer
Table audio
id: integer,
url: string,
inserted_at, date,
user_id, integer
here is how I build union query:
iex(1)> query_a = Query.from a in “text”, where: a.user_id==1,select: {a.id,a.text,“”,a.inserted_at}
iex(2)> query_b = Query.from b in “audio”, where: b.user_id==1,select: {b.id,“”,b.url,b.inserted_at}
iex(71)> union_query = union_all(query_a, ^query_b)
iex(73)> Query.from(q in subquery(union_query)) |> GMessage.Repo.all()
but I got error like this,
** (Ecto.SubQueryError) the following exception happened when compiling a subquery.
** (Ecto.QueryError) subquery/cte must select a source (t), a field (t.field) or a map, got: `{&0.id(), "", &0.url(), &0.inserted_at()}` in query:
how can I fix? Thanks a lot.