Problem with aliasing expression for "a depth-first" query using Esqueleto's "withRecursive"

13 Views Asked by At

I'm trying to run the following query with a recursive common table expressions using Esqueleto's withRecursive but received an unexpected error at runtime.
Is there a way to fix this?

select $ do
        cte <- withRecursive
            (do
                  x <- from $ table @SignTag
                  where_ $ isNothing_ $ x ^. SignTagGroup
                  let level = val (0 :: Int)
                  return (level,x)
            )
            unionAll_
            (\parent -> do
                  (l,_) :& x <- from $ parent
                      `innerJoin` table @SignTag `on` (\((_, p) :& x) -> just (p ^. SignTagId) ==. x ^. SignTagGroup)
                  let level = l +. val 1
                  orderBy [desc level]
                  return (level,x)
            )
        from cte

Runtime error:

[Error#yesod-core] SQLite3 returned ErrorError while attempting to perform prepare "WITH RECURSIVE \"cte\" AS (SELECT ? AS \"v\", \"sign_tag\".\"id\" AS \"v2_id\", \"sign_tag\".\"name\" AS \"v2_name\", \"sign_tag\".\"descr\" AS \"v2_descr\", \"sign_tag\".\"group\" AS \"v2_group\"\nFROM \"sign_tag\"\nWHERE \"sign_tag\".\"group\" IS NULL\nUNION ALL\nSELECT \"cte\".\"v\", \"sign_tag2\".\"id\", \"sign_tag2\".\"name\", \"sign_tag2\".\"descr\", \"sign_tag2\".\"group\"\nFROM \"cte\" INNER JOIN \"sign_tag\" AS \"sign_tag2\" ON \"cte\".\"v2_id\" = \"sign_tag2\".\"group\"\nORDER BY \"cte\".\"v\" + ? DESC\n)\nSELECT \"cte\".\"v\", \"cte\".\"v2_id\", \"cte\".\"v2_name\", \"cte\".\"v2_descr\", \"cte\".\"v2_group\"\nFROM \"cte\"\n": 1st ORDER BY term does not match any column in the result set

The generated query is:

WITH RECURSIVE "cte" AS (
SELECT ? AS "v"
  , "sign_tag"."id" AS "v2_id"
  , "sign_tag"."name" AS "v2_name"
  , "sign_tag"."descr" AS "v2_descr"
  , "sign_tag"."group" AS "v2_group"
FROM "sign_tag"
WHERE "sign_tag"."group" IS NULL
UNION ALL
SELECT ("cte"."v" + ?)
     , "sign_tag2"."id"
     , "sign_tag2"."name"
     , "sign_tag2"."descr"
     , "sign_tag2"."group"
FROM "cte" INNER JOIN "sign_tag" AS "sign_tag2" ON "cte"."v2_id" = "sign_tag2"."group"
ORDER BY "cte"."v" + ? DESC
)
SELECT "cte"."v", "cte"."v2_id", "cte"."v2_name", "cte"."v2_descr", "cte"."v2_group"
FROM "cte"

with 0 and 1 as arguments.

0

There are 0 best solutions below