How to build Criteria Hibernate Subquery when subquery uses query field for comparison

150 views Asked by At

I'm trying to build a criteria query using subqueries. I read a lot about detachedcriteria for creating subqueries but can't find anything about selecting another select (instead the subquery on where clause etc).

Any time an user interacts to the app, the app inserts a row to Interaction table with some data.

The query I would like to achieve is:

Count all interactions grouped by day, in a specific month and count all specific interaction user in this same specific month, grouped by day.

The query I could build in SQL is

select DAY(intsys.dateReg) all_days ,
 count(intsys.id) all_interactions, 
 (select count(us.id) from Interaction us where us.user_id = 2 and MONTH(us.dateReg) = 7 and DAY(us.dateReg) = DAY(intsys.dateReg)) user_interactions 
from Interaction intsys
where MONTH(intsys.dateReg) = 7 group by DAY(intsys.dateReg);

The result is exactly as expected:

enter image description here

Can anyone help me to reproduce this query using Criteria API?

0

There are 0 answers