导航查询时，在PG数据库中异常

[exy3351]

问题描述及重现代码：

导航查询在 sqlite 这中正常，但在Pg下报错；另外，若层级关系不一致时，查询的结果不是预期的，通过通过调试代码发现，是由于生成的Sql语句中添加了 cte_level 字段导致的，是否可以开放一个参数，用来控制是否需要添加 cte_level ？？？

附件中是Demo程序，及转出的 Sqlite 数据，需要导入的 Pg 中测试
Uploading ConsoleApp2.zip…

错误: 在查询列表中必须出现SELECT DISTINCT, ORDER BY表达式
LINE 16: ORDER BY a.cte_level desc, a."code"

生成的Sql语句

WITH RECURSIVE "as_tree_cte"
as
(
SELECT DISTINCT 0 as cte_level, a."id", a."parentid", a."code", a."name", a."colorcode"
FROM "t_ge_class" a
WHERE (((a."code") in ('22020300','22030200','22020400','22030300','22020300','22030200','22020400','22030300')))

union all

SELECT DISTINCT wct1.cte_level + 1 as cte_level, wct2."id", wct2."parentid", wct2."code", wct2."name", wct2."colorcode"
FROM "as_tree_cte" wct1
INNER JOIN "t_ge_class" wct2 ON wct2."id" = wct1."parentid"
)
SELECT DISTINCT a."id", a."parentid", a."code", a."name", a."colorcode"
FROM "as_tree_cte" a
ORDER BY a.cte_level desc, a."code"

将最后的 a.cte_level desc, 删除后就能正常执行了

// c# code
public List<EntityClass> GetList()
    {
        List<string> codes = new() { "22020300", "22030200", "22020400", "22030300", "22020300", "22030200", "22020400", "22030300" };
        var cls = fsql.Select<EntityClass>()
               .Distinct()
               .Where(t => codes.Contains(t.Code))
               //.IncludeMany(t => t.Layers)
               .AsTreeCte(null, true, "")
               .Distinct()
               .OrderBy(f => f.Code)
               .ToTreeList();

        return cls;
    }

数据库版本

pg

安装的Nuget包

3.2.800

.net framework/. net core？ 及具体版本

net 7

[exy3351]

ConsoleApp2.zip

[2881099]

是因为用了 Distinct() 导致的

解决方法1：

v3.2.801-preview20230915 以下SQL正常：

               .AsTreeCte(null, true, "")
               .WithTempQuery(f => f)
               .Distinct()
               .OrderBy(f => f.Code)

WITH RECURSIVE "as_tree_cte"
as
(
SELECT DISTINCT 0 as cte_level, a."id", a."parentid", a."code", a."name" 
    FROM "entityclass" a 
    WHERE (((a."code") in ('22020300','22030200','22020400','22030300','22020300','22030200','22020400','22030300')))

union all

SELECT DISTINCT wct1.cte_level + 1 as cte_level, wct2."id", wct2."parentid", wct2."code", wct2."name" 
    FROM "as_tree_cte" wct1 
    INNER JOIN "entityclass" wct2 ON wct2."id" = wct1."parentid"
)
SELECT DISTINCT * 
FROM ( 
    SELECT a."id", a."parentid", a."code", a."name" 
    FROM "as_tree_cte" a 
    ORDER BY a.cte_level desc ) a 
ORDER BY a."code"

[2881099]

解决方法2：将自带的 ORDER BY a.cte_level desc 禁用，方法如下：

v3.2.801-preview20230915

               .AsTreeCte(null, true, "")
               .Distinct()
               .OrderBy(null) //清除 orderby
               .OrderBy(f => f.Code)
               .ToTreeList();

WITH RECURSIVE "as_tree_cte"
as
(
SELECT DISTINCT 0 as cte_level, a."id", a."parentid", a."code", a."name" 
    FROM "entityclass" a 
    WHERE (((a."code") in ('22020300','22030200','22020400','22030300','22020300','22030200','22020400','22030300')))

union all

SELECT DISTINCT wct1.cte_level + 1 as cte_level, wct2."id", wct2."parentid", wct2."code", wct2."name" 
    FROM "as_tree_cte" wct1 
    INNER JOIN "entityclass" wct2 ON wct2."id" = wct1."parentid"
)
SELECT DISTINCT a."id", a."parentid", a."code", a."name" 
FROM "as_tree_cte" a 
ORDER BY a."code"