执行self-left联接时意外离开

Question

我多次尝试在同一个表上执行自左联接。每当我执行这个查询时，我都会得到异常。

类org.hibernate.hql.internal.ast.QuerySyntaxException 消息意外标记：第13行第33列附近的LEFT

Query query = null
        def hql = $/
                SELECT
                    c1.id,
                    c1.parentId,
                    c1.navDepth,
                    c1.navOrder,
                    c1.title,
                    c1.fileName,
                    // aliasing filenamepath column as filepath.
                    REPLACE(c1.fileNamePath, 'home/', '') AS filepath
                FROM Content AS c1
                WHERE 0 = 0
                LEFT JOIN Content AS c2
                ON c1.col1 = c2.fileNameLookup
                AND c1.col1 = c2.col1
                LEFT JOIN Content AS c3
                ON c1.col2 = c3.fileNameLookup
                AND c1.col2 = c3.col2
                LEFT JOIN Content AS c4
                ON c1.col3 = c4.fileNameLookup
                AND c1.col3 = c4.col3
                LEFT JOIN Content AS c5
                ON c1.col4 = c5.fileNameLookup
                AND c1.col4 = c5.col4
                LEFT JOIN Content AS c6
                ON c1.col5 = c6.fileNameLookup
                AND c1.col5 = c6.col5
                LEFT JOIN Content AS c7
                ON c1.col6 = c7.fileNameLookup
                AND c1.col6 = c7.col6
                LEFT JOIN Content AS c8
                ON c1.col7 = c8.fileNameLookup
                AND c1.col7 = c8.col7
                LEFT JOIN Content AS c9
                ON c1.col8 = c9.fileNameLookup
                AND c1.col8 = c9.col8
                LEFT JOIN Content AS c10
                ON c1.col9 = c10.fileNameLookup
                AND c1.col9 = c10.col9
                AND c1.isNavItem = 1
                GROUP BY c1.id
                ORDER BY c1.navDepth, c1.navOrder
                /$
                //def result = Content.executeQuery(hql)
        query = Content.executeQuery(hql).setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE)
                .addScalar("filepath", new StringType())
        List<Map<String,Object>> result = query.list()
        return result

有人能告诉我我做错了什么吗？我该如何让它发挥作用？

Answer 1

1. 哪里错了：

   FROM 
   WHERE 0 = 0
   LEFT JOIN Content AS c2
   

   应该是：

   FROM
   LEFT JOIN
   LEFT JOIN
   ...
   WHERE
   GROUP BY
   ORDER BY
   

2. 查询中没有要设置的filepath参数，如果它必须在WHERE子句中而不是SELECT子句中：

   addScalar("filepath", new StringType())
   

3. 您使用LEFT JOIN和ON子句编写了一个SQL查询，但这是本机SQL查询语法。在HQL中，您加入了可导航实体协会，并且没有HQL ON子句。

   要模拟ON子句，您需要使用with指令：

   from Cat as cat
       left join cat.kittens as kitten
           with kitten.bodyWeight > 10.0
   

4. 先在没有变压器的情况下试试。只需执行查询并让它首先返回一个List。如果有效，那就试试变压器。如果没有，您的查询语法仍然存在问题。