Query to count boolean property values within Grails

Question

I have a grails domain like this

Point {
  User user
  Date assignedDate = new Date()
  consumed = false
}

I want to do a query that return rows with user, count(consumed=true), count(consumed=false)

for example with data

| USER | DATE | CONSUMED |
___________________________
|   1  | ...  | true     |
|   1  | ...  | true     |
|   1  | ...  | false    |
|   2  | ...  | true     |
|   2  | ...  | false    |

the query must return:

| USER | CONSUMED | NOT CONSUMED |
_________________________________
|   1  | 2        | 1            |
|   2  | 1        | 1            |

I have to do this in a single query because I need pagination. Best if done with gorm criteria or hibernate HQL.

I have tried playing with projections but without success.

Any idea? Thank you

Workaround

As a workaround I have used hibernate formula mapping with formula suggested by Michael J. Lee.

I have added two mapped fields to


    Point {
          User user
          Date assignedDate = new Date()
          consumed = false
          free formula: "CASE WHEN consumed = 0 THEN 1 ELSE 0 END"
          notFree formula: "CASE WHEN consumed = 1 THEN 1 ELSE 0 END"
        }

And user a criteria query like:


    Point.withCriteria{ 
      projections {
          groupProperty('user')
          sum('free')
          sum('notFree')
      }
    }

Michael J. Lee Michael J. Lee · Accepted Answer · 2012-04-19T12:45:20

The way you have your data structured and the query that you want to return doesn't lend nicely to things like criteria queries and dynamic finders. I would suggest these two options depending on your level of experience and how much data you need to summarize.

1.) Create a stored procedure or use a named query (better with lots of data)...

SELECT
   user_id as 'USER',
   SUM(CASE WHEN consumed = 1 THEN 1 ELSE 0 END) as 'CONSUMED'
   SUM(CASE WHEN consumed = 0 THEN 0 ELSE 1 END) as 'NOT CONSUMED'
FROM 
   point
GROUP BY
    user_id

2.) Use groovy's collection closures to do the heavy lifting in grails (use with caution if you have lots of data)

def someControllerMethod() {
    def points = Point.list().groupBy{ it.user } //<-- or use a findAllBy or criteria
    def data = [];
    points.each{ point,values ->
        data << ['USER':point.user, 'CONSUMED':values.count{ it.consumed }, 'UNCONSUMED':values.count{ !it.consumed } ]
    }

   println("this is what it looks like ${data}");
   return data //<-- is a list of maps that should be easy to render in your gsp
}

Please note that I just did the coding off the top of my head and didn't verify the syntax

Hope this helps.

Query to count boolean property values within Grails

Workaround

2 Answers