Geek asks and answers

Query for counting logical property values ​​in Grails

I have a grails domain like this

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

I want to make a query returning rows with user, count (consumed = true), count (consumed = false)

e.g. with data

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

The request should return:

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

I have to do this in one query because I need pagination. Best if done using gorm criteria or hibernate HQL.

I tried to play with projections, but to no avail.

Any idea? Thanks you

Bypass

As a workaround, I used a hibernate formula mapping with a formula proposed by Michael J. Lee.

I added two displayed 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"
        }

, :

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

, , , . , .

1.) ( )...

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.) groovy ( , )

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
}

,

, .

0

GROUP BY . COUNT select

-1

More articles:


All Articles