I have three queries grouped together by union_all, and all I want to do is to is to apply order_by in them. But I tried lots of different approaches and none of them seem to work. Most of them end having some Postgres syntax error.
So here was my initial function:
def build_relative_path_query(folder_id, workspace_id, params) do
title = Map.get(params, "title")
location = Map.get(params, "location", "main") |> String.to_atom()
order = Map.get(params, "order")
order_by = Map.get(params, "order_by")
folders_query =
FolderQuery.base()
|> FolderQuery.compose(is_deleted: false)
|> FolderQuery.compose(workspace_id: workspace_id)
|> FolderQuery.compose(name: title)
|> FolderQuery.compose(ancestor_id: folder_id)
|> group_by([f], f.id)
|> FolderQuery.compose(order, order_by: order_by)
|> FolderQuery.select()
documents_query =
DocumentQuery.base()
|> DocumentQuery.compose(location: location)
|> DocumentQuery.compose(title: title)
|> DocumentQuery.join(:folder, workspace_id)
|> DocumentQuery.join_signees()
|> join(:left, [document], dv in assoc(document, :current_version))
|> group_by([d], d.id)
|> DocumentQuery.compose(order, order_by: order_by)
|> DocumentQuery.select()
attachments_query =
AttachmentQuery.base()
|> AttachmentQuery.compose(location: location)
|> AttachmentQuery.join(:folder, workspace_id)
|> AttachmentQuery.compose(name: title)
|> group_by([a], a.id)
|> AttachmentQuery.compose(order, order_by: order_by)
|> AttachmentQuery.select()
folders_query
|> union_all(^documents_query)
|> union_all(^attachments_query)
|> order_by(desc: fragment("item_type"))
end
And this was the query generated by it:
select
f0."id",
'folder' as item_type,
f0."name",
f0."inserted_at",
false,
'{}'::json as signees,
0 as signees_count,
'' as number,
'' as status,
array[]::text[] as classifier
from
"folders" as f0
inner join "folder_paths" as f1 on
f1."descendant_id" = f0."parent_folder_id"
where
(f0."is_deleted" = $1)
and (f0."workspace_id" = $2)
and (f1."ancestor_id" = $3)
group by
f0."id"
union all (
select
d0."id",
'document' as item_type,
d0."title",
d0."inserted_at",
BOOL_AND(d3."is_draft"),
JSON_AGG(JSON_BUILD_OBJECT('id', s2."id", 'name', s2."name", 's_type', s2."signee_type") order by s2."inserted_at"),
count(s2."id"),
d0."number",
d0."status",
d0."classifier"
from
"documents" as d0
inner join "folders" as f1 on
f1."id" = d0."folder_id"
left outer join (
select
ss2."id" as "id",
sd0."id" as "document_id",
ss2."signee_type" as "signee_type",
coalesce(ss3."full_name", ss4."legal_representative_name") as "name",
ss2."inserted_at" as "inserted_at"
from
"documents" as sd0
inner join "folders" as sf1 on
sf1."id" = sd0."folder_id"
left outer join "document_versions" as sd5 on
(sd0."id" = sd5."document_id")
and (sd5."current_version" = $4)
left outer join "signees" as ss2 on
sd5."id" = ss2."document_version_id"
left outer join "signee_individuals" as ss3 on
ss3."signee_id" = ss2."id"
left outer join "signee_companies" as ss4 on
ss4."signee_id" = ss2."id"
where
(sd0."location" = $5)
and (sf1."workspace_id" = $6)
limit 2) as s2 on
true
left outer join "document_versions" as d3 on
(d3."document_id" = d0."id")
and (d3."current_version" = $7)
where
(d0."location" = $8)
and (f1."workspace_id" = $9)
group by
d0."id"
order by
d0."title" desc)
union all (
select
a0."id",
'attachment' as item_type,
a0."name",
a0."inserted_at",
false,
'{}'::json as signees,
0 as signees_count,
'' as number,
'' as status,
array[]::text[] as classifier
from
"attachments" as a0
inner join "folders" as f1 on
f1."id" = a0."folder_id"
where
(a0."location" = $10)
and (f1."workspace_id" = $11)
and (unaccent(a0."name") ilike unaccent($12))
group by
a0."id"
order by
a0."name" desc)
order by
f0."name" desc,
item_type desc
And it returns this error: ** (Postgrex.Error) ERROR 42P01 (undefined_table) missing FROM-clause entry for table "f0"
Which is exactly this line: f0."name" desc, and if I manually remove f0., it will work. But I don’t know how to do it in Ecto.
I have researched google and this forum, and I have seem some people suggesting using subquery with union_all, but it also doesn’t work for me. Which I get this error: ** (Postgrex.Error) ERROR 42601 (syntax_error) syntax error at or near "AS"
And the final query is this:
select
s0."id",
s0."item_type",
s0."name",
s0."inserted_at",
s0."is_draft",
s0."signees",
s0."signees_count",
s0."number",
s0."stat
us",
s0."classifier"
from
(
select
sf0."id" as "id",
'folder' as item_type as "item_type",
sf0."name" as "name",
sf0."inserted_at" as "inserted
_at",
false as "is_draft",
'{}'::json as signees as "signees",
0 as signees_count as "signees_count",
'' as number as "number",
'' as status A
S "status",
array[]::text[] as classifier as "classifier"
from
"folders" as sf0
inner join "folder_paths" as sf1 on
sf1."descendant_id" = sf0."parent_folder_id"
where
(sf0."is_deleted" = $1)
and (sf0."workspace_id" = $2)
and (sf1."ancestor_id" = $3)
group by
sf0."id"
union all (
select
d0."id",
'document' as item_type,
d0."title",
d0."inserted_at",
BOOL_AND(d3."is_draft"),
JSON_AGG(JSON_BUILD_OBJECT('id', s2."id", 'name', s2."name", 's_type', s2."signee_type") order by s2."inserted_at"),
count(s2."id"),
d0."number",
d0."status",
d0."classifier"
from
"documents" as d0
inner join "folders" as f1 on
f1."id" = d0."folder_id"
left outer join (
select
ss2."id" as "id",
sd0."id" as "document_id",
ss2."signee_type" as "signee_type",
coalesce(ss3."full_name", ss4."legal_representative_name") as "name",
ss2."inserted_at" as "inserted_at"
from
"documents" as sd0
inner join "folders" as sf1 on
sf1."id" = sd0."folder_id"
left outer join "document_versions" as sd5 on
(sd0."id" = sd5."document_id")
and (sd5."current_version" = $4)
left outer join "signees" as ss2 on
sd5."id" = ss2."document_version_id"
left outer join "signee_individuals" as ss3 on
ss3."signee_id" = ss2."id"
left outer join "signee_companies" as ss4 on
ss4."signee_id" = ss2."id"
where
(sd0."location" = $5)
and (sf1."workspace_id" = $6)
limit 2) as s2 on
true
left outer join "document_versions" as d3 on
(d3."document_id" = d0."id")
and (d3."current_version" = $7)
where
(d0."location" = $8)
and (f1."workspace_id" = $9)
group by
d0."id"
order by
d0."title" desc)
union all (
select
a0."id",
'attachment' as item_type,
a0."name",
a0."inserted_at",
false,
'{}'::json as signees,
0 as signees_count,
'' as number,
'' as status,
array[]::text[] as classifier
from
"attachments" as a0
inner join "folders" as f1 on
f1."id" = a0."folder_id"
where
(a0."location" = $10)
and (f1."workspace_id" = $11)
and (unaccent(a0."name") ilike unaccent($12))
group by
a0."id"
order by
a0."name" desc)
order by
sf0."name" desc,
item_type desc) as s0
I have also tried to delete the order_by in the queries, and just do it in the end, but it also doesn’t work. I’m reading the documentation and researching the internet for answers since yesterday but without any success. Any help or light would be deeply appreciated.
Thanks! 
