SQL to ecto: custom order by getting (Postgrex.Error) ERROR 42601 (syntax_error) syntax error at or near "AS"

[Terbium-135]

What am I missing?
I am trying to ‘translate’ following SQL query to ecto:

SELECT g.groupname, g.id FROM groups AS g
LEFT OUTER JOIN unnest ('{class3, class1, class11, latecomer}'::text[]) WITH ORDINALITY sort(sort_order, ord)
ON g.groupname = sort_order
ORDER BY ord

Result:

My try using ecto:

from g in Backend.Schema.Group,
  left_join:
    sort in fragment(
      "unnest ('{kindergarden, class1, class2, class3, class4}'::text[]) WITH ORDINALITY sort(name, ord)"
    ),
  on: g.groupname == sort.name,
  order_by: sort.ord,
  select: {g.groupname, g.id}

Gives the following:

** (Postgrex.Error) ERROR 42601 (syntax_error) syntax error at or near "AS"

    query: SELECT g0."groupname", g0."id" FROM "groups" AS g0
 LEFT OUTER JOIN unnest ('{kindergarden, class1, class2, class3, class4}'::text[]) WITH ORDINALITY sort(name, ord) AS f1
 ON g0."groupname" = f1."name" ORDER BY f1."ord"

I tried to use as: :sort but this generates the same query…

[zenchild]

This isn’t exactly what you are doing, but @LostKobrakai has a post on using unnest for sorting that you might want to check out and see if it unblocks you.

[Terbium-135]

Thanks for the link. Had a quick look and @LostKobrakai 's solution looks very similar to mine.

I will have a closer look soon!

[Terbium-135]

I had to add select * from to my fragment to avoid that error if ecto adds an AS to the generate query.

So my finally solution is:
from g in Backend.Schema.Group,
  where: g.groupname in ^sort_order,
  join:
    fragment(
      "select * from unnest ('{kindergarden, class1, class2, class3, class4}'::text[]) WITH ORDINALITY sort(name, ord)"
    ),
  on: g.groupname == sort.name,
  order_by: sort.ord,
  select: {g.groupname, g.id}