在左联接子查询中使用Blaze-Persistence GROUP BY,在根查询中使用COALESCE

Question

┌─────────────────┐      ┌───────────┐     ┌─────┐
│ ALERT           │      │ ALERT_TAG │     │TAG  │
│ id              ├──────┤ alertId   ├─────┤code │
│ finalized       │      │ tag_code  │     └─────┘
│ assigneeGroupId │      └───────────┘
└─────────────────┘

criteriaBuilderFactory.create(entityManager, javax.persistence.Tuple.class)
            .from(Tag.class)
            .select("code")
            .select("COALESCE(at.tagCount, 0)")
            .leftJoinOnSubquery(AlertTagCTE.class, "at")
            .from(AlertTag.class)
            .bind("tagCode").select("tag.code")
            .bind("tagCount").select("count(tag.code)")
            .join("alert", "a", JoinType.INNER)
            .where("a.finalized").eq(false)
            .where("a.assigneeGroupId").in(userGroups)
            .groupBy("tag.code")
            .end()
            .end()
            .getQuery()
            .getResultList();

@Entity
@CTE
public class AlertTagCTE {

  @Id private String tagCode;
  private Long tagCount;

}

select t.code, nvl(atj.tag_count, 0) 
from tag t
left join (
    select alert_tag.tag_code, count(alert_tag.tag_code) as tag_count 
    from alert_tag 
    join alert ata 
    ON alert_tag.alert_id = ata.id 
    WHERE
        ata.finalized = 0 
        AND ata.assignee_group_id in (37 , 38 , 39 , 44 , 12 , 14 , 18 , 19 , 20 , 22 , 23 , 25 , 26 , 30) 
    group by alert_tag.tag_code
) atj on t.code = atj.tag_code
order by t.code;

sqlselect tag0_.code as col_0_0_, nvl(alerttagct1_.tag_count, 0) as col_1_0_ 
from tag tag0_ 
left outer join (
    select null tag_code,null tag_count from dual where 1=0 union all (
        select alerttag0_.tag_code as col_0_0_, count(alerttag0_.tag_code) as col_1_0_ 
        from alert_tag alerttag0_ 
        inner join alert alert1_ on alerttag0_.alert_id=alert1_.id 
        where 
        alert1_.finalized=false 
        and (alert1_.assignee_group_id in (37 , 38 , 39 , 44 , 12 , 14 , 18 , 19 , 20 , 22 , 23 , 25 , 26 , 30)) 
        group by alerttag0_.tag_code
    )
) alerttagct1_ on ((null is null));

1. 为什么这个工会成员？
2. 在Blaze persistence API中是否可以在没有联合的情况下获取查询？

Answer 1

您在查询生成器中缺少打开条件：

criteriaBuilderFactory.create(entityManager, javax.persistence.Tuple.class)
        .from(Tag.class)
        .select("code")
        .select("COALESCE(at.tagCount, 0)")
        .leftJoinOnSubquery(AlertTagCTE.class, "at")
            .from(AlertTag.class)
            .bind("tagCode").select("tag.code")
            .bind("tagCount").select("count(tag.code)")
            .join("alert", "a", JoinType.INNER)
            .where("a.finalized").eqLiteral(false)
            .where("a.assigneeGroupId").in(userGroups)
            .groupBy("tag.code")
        .end()
            .on("at.tagCode").eqExpression("t.code") // This was missing
        .end()
        .getQuery()
        .getResultList();

联合只是用于命名列，但它不应该是一个问题，因为它不会产生结果，优化器应该能够对其进行优化。你觉得有问题吗？这样做的主要原因是，否则命名子查询的项将需要解析和修改SQL，这是尽可能避免的。

像PostgreSQL这样的数据库支持在表别名之后使用别名。显然您使用的数据库不支持这一点，例如Oracle或MySQL？