I need statistics about alert tagged by codes. Alerts are visible for users with group based restrictions. In case when some tag code is only in alerts which are invisible for user then 0 should be displayed in statisitics.
Tables structure:
┌─────────────────┐ ┌───────────┐ ┌─────┐ │ ALERT │ │ ALERT_TAG │ │TAG │ │ id ├──────┤ alertId ├─────┤code │ │ finalized │ │ tag_code │ └─────┘ │ assigneeGroupId │ └───────────┘ └─────────────────┘
I’m using Blaze-Persistence and try to make GROUP BY
in LEFT JOIN SUBQUERY
with COALESCE
in root query. Reason of using Blaze-Persistence has support for subquery in left join.
This is my code:
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; }
During execution I expect to get this query:
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;
but I get this one:
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));
Results for both queries are different.
- Why this union apper?
- Does it’s possible to get query without union in Blaze persistence API?
Advertisement
Answer
You are missing your on-condition in the query builder:
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();
The union is just used for naming the columns but it shouldn’t be an issue since it doesn’t produce a result and the optimizer should be able to optimize it away. Do you see problems with that? The main reason for doing this is, that naming the items of the subquery would otherwise require a parsing and adapting of the SQL which is avoided as much as possible.
Databases like e.g. PostgreSQL support the use of aliases after the table alias. Apparently you are using a database that does not support this like e.g. Oracle or MySQL?