Hibernate HQL - Use CASE WHEN in COUNT Statement like IF in MySQL

Question

I am trying to port a MySQL Query that works to Hibernate HQL, this is all very new to me, so I am open to any kind of hint (Wrong Way, Wrong Structure, change all... ;) )

Two tables A and B. (Structure broken down, only the relevant parts)

A contains entrys, each with a unique ID. B references those IDs and holds a boolean-like marker (TINYINT(1)).

I want to know how many rows there are in B for each row in A with the Id from A's Row and Marker == True (1).

My MySQL query was like this:

SELECT A.id, COUNT( IF( B.marker = 1, 1, NULL ) ) AS markerTrue, COUNT( IF( B.marker =0, 1, NULL ) ) AS markerFalse FROM A LEFT JOIN B ON B.a_id = A.id GROUP BY A.id

It works and I ported it to this (HQL):

SELECT A.id, COUNT(CASE WHEN B.marker = 1 THEN 1 ELSE NULL END) as markerTrue, COUNT(CASE WHEN B.marker = 0 THEN 1 ELSE NULL END) as markerFalse FROM A LEFT JOIN B WITH B.a_id = A.id GROUP BY A.id

This throws an Exception:

org.hibernate.hql.ast.QuerySyntaxException: unexpected token: CASE near ...

In the logs, there is also

org.hibernate.hql.ast.ErrorCounter - line 1:19: unexpected token: CASE antlr.NoViableAltException: unexpected token: CASE

But thats just the same internal Error.

Is there a way to do this in HQL? Is there another better way, like restructuring the tables, what is an experts opinion on this?

Solution 1

I am by no means an expert — when HQL stymies me, I rarely have qualms about bypassing the problem by switching to straight SQL — so I can't tell you if there is a better, more HQL-ish way to do this. But in your specific instance, where B.marker is always either 0 or 1, I suppose you could change

COUNT(CASE WHEN B.marker = 1 THEN 1 ELSE NULL END)

to

SUM(B.marker)

and

COUNT(CASE WHEN B.marker = 0 THEN 1 ELSE NULL END)

to

COUNT(*) - SUM(B.marker)

(though you may also need to wrap your SUMs in COALESCE(..., 0) — I'm not sure).

Solution 2

A rewrite in SQL. I hope it's more easily converted to HQL:

SELECT A.id
     , COALESCE(markerTrue, 0) AS markerTrue
     , COALESCE(markerFalse, 0) AS markerFalse 
FROM A 
  LEFT JOIN 
    ( SELECT a_id
           , COUNT(*) AS markerTrue
      FROM B 
      WHERE marker = 1
      GROUP BY a_id
    ) AS BT
    ON BT.a_id = A.id
  LEFT JOIN 
    ( SELECT a_id
           , COUNT(*) AS markerFalse
      FROM B 
      WHERE marker = 0
      GROUP BY a_id
    ) AS BF
    ON BF.a_id = A.id