JPQL TREAT AS / LEFT OUTER JOIN [英] JPQL TREAT AS /LEFT OUTER JOIN

问题描述

我正在尝试JPA 2.1（eclipselink）中的TREAT AS功能，我遇到了JPA的错误：

I'm trying the "TREAT AS" function from JPA 2.1 (eclipselink) and I'm stuck with an error from JPA:

异常说明：ReportQuery结果尺寸不合。期待[263]，但检索[197]

Exception Description: ReportQuery result size mismatch. Expecting [263], but retrieved [197]

这是我的JPQL查询（我将某些部分更改为更明确）：

Here is my JPQL query (I change some parts to be more explicit):

String jpql = "select distinct s, accountAD "
            + "from SheetAccountUser s "
            + "left join fetch s.userTarget target "
            + "left join TREAT(target.accounts AS ADAccount) accountAD ";

ADAccount是AbstractAccount的子类（@Inheritance（strategy = InheritanceType.JOINED）。
用户有一个AbstractAccount列表。

ADAccount is a subclass from AbstractAccount (@Inheritance(strategy = InheritanceType.JOINED). A User have a list of AbstractAccount.

我想用userTarget的AD帐户选择工作表。如果没有userTarget或userTarget没有，我想要null AD帐户（左连接）。

I want to select sheets with the AD account of the userTarget. I want null if there is no userTarget or if the userTarget has no AD account (left join).

问题来自处理运算符.SQL生成的查询在AbstractAccount表和ADAccount表之间有一个左连接。
这导致每个帐户类型的targetUser检索一行。

The problem comes from the treat operator. The SQL generated query have a left join between the AbstractAccount table and the ADAccount table. This cause the retrieve of one line per account type of the targetUser.

这是生成的SQL查询：

Here is the SQL query generated:

SELECT DISTINCT 
t0.Id, --etc
t6.Id, t6.name, --etc
t7.userId --etc
FROM sheet t0 
LEFT OUTER JOIN user t6 ON (t6.Id = t0.userTargetId),
account t7 LEFT OUTER JOIN ad_account t8 ON ((t8.userId = t7.userId) AND (t8.idApp = t7.idApp))
WHERE (t7.userId = t6.Id) AND (t7.DTYPE = 'ADAccount');

我们可以在帐户和ad_account之间看到左外连接。
此外，select子句中不存在ad_account表。
（idApp字段是主键的一部分，并保持唯一（userId，idApp）约束）。
我不知道我的理解或JPA是否有问题。

We can see the left outer join between account and ad_account. Also, the ad_account table is not present in the select clause. (The idApp field is a part of the primary key and maintain the unique (userId, idApp) constraint). I don't know if it's a problem with my understanding or JPA.

感谢您帮助我！

推荐答案

 "select distinct s, accountAD "
                + "from SheetAccountUser s "
                + "left join fetch s.userTarget target "
                + "left join target.accounts accountAD where TYPE(accountAD) = ADAccount";

此请求不会返回包含没有AD帐户的目标的工作表。

This request doesn't return sheet that have a target without AD Account.

 "select distinct s, accountAD "
                + "from SheetAccountUser s "
                + "left join fetch s.userTarget target "
                + "left join target.accounts accounts "
                + " join TREAT(accounts AS ADAccount) accountAD";

这个生成与我的第一个jpql请求相同的SQL并生成Eclipselink错误。

This one generate the same SQL that my first jpql request and generate an Eclipselink error.

如果目标有一个AD帐户和至少一个其他帐户类型，我还有一个工作表的多行：一行设置了AD帐户属性，另一行设置为null值（这些不同的值会阻止distinct子句，DTYPE值也是如此）。

I also have multiple lines for one sheet if the target has one AD Account and at least one other Account type: One line with the AD Account attributes setted and others with null values (these different values block the distinct clause, so do the DTYPE value).

幸运的是，我只需要2个关于AD帐户的信息：它的存在和一个布尔停用 。

Fortunately, I just need 2 informations about AD Account: Its existence and one boolean "desactivated".

经过多次反思，我有了一个想法：

After more reflexion I had an idea:

    "select u, "
                // 0 if line with no target or no ADAccount or with another account type, else 1 (one 1 by sheet/target) 
                + "sum( "
                + "     case "
                + "     when accountAD.desactivated is not null then 1 "
                + "     else 0 "
                + "     end "
                + ") as ADAccountExists, "
                // the target have an AD Account desactivated
                + "sum( " 
                + "     case "
                + "     when compteAD.desactivated = 1 then 1 " 
                + "     else 0 "
                + "     end" 
                + ") as ADAccountDesactivated " 
                + "from SheetAccountUser s "
                + "left join fetch s.userTarget target "
                + "left join treat(target.accounts as ADAccount) accountAD "
                + "group by s,target " //the group by maintains unicity of the sheets

它工作正常，但非常难看。我有一天会跳过另一种方法。

It's working fine but it's very ugly. I hoppe someday to find another method.

这篇关于JPQL TREAT AS / LEFT OUTER JOIN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！