How to union two tables with different columns and order by insert time


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.text,"",a.inserted_at}

iex(2)> query_b = Query.from b in “audio”, where: b.user_id==1,select: {,"",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.url(), &0.inserted_at()}` in query:

how can I fix? Thanks a lot.

Try shaping your select as a map rather than a tuple, e.g.

query_a = 
  from a in “text”, 
  where: a.user_id==1,
  select: %{id:, text: a.text, url: "", inserted_at: a.inserted_at} # <- This bit

and the same for query_b

Then the two data structures from each subquery are compatible and can be unioned


it works!
never thought select result can be a Map… Ecto is amazing

1 Like