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:
Can anyone help me to reproduce this query using Criteria API?
