Grails projections group by and count

Question

Following are my two classes

class Users {
    String emailAddress
    String password
    //    String filename
    String firstName
    String lastName
    Date dateCreated
    Date lastUpdated
}

and

class SharedDocuments {
    Users author
    Users receiver
    Documents file
    static constraints = {

    }
}

I want to run a query similar to this one, essentially i want to get list of all the users along with count of documents they have authored

SELECT author_id, COUNT(SharedDocuments.id )FROM SharedDocuments 
  INNER JOIN users ON author_id = users.id
  GROUP BY author_id

This is what I have so far

def sharedDocumentsInstanceList = SharedDocuments.createCriteria().list(params){
    createAlias("author","a")
    eq("receiver.id",session.uid)  
    projections{
        groupProperty "author"
        count "id",'mycount'

    }
     order('mycount','desc')
    maxResults(params.max)
}

I have this 90% working, if i get rid of count or countDistinct I get list of distinct authors but what i want is authors along with the counts of documents. So when i add the count or countDistinct clause to this criteria I just get array of long!! like [2,3,4] what I want is [[author1,2],[author2,3] ...] How can i achive this I have already seen Grails: Projection on many tables?, Grails criteria projections - get rows count, Grails groupProperty and order. How it works?, but none of the answers seem to be solving my issue!

Nguyen Le · Accepted Answer · 2013-05-18T19:14:26.337

1

Projections with pagination has a bug where it only return the last field in the projections block. The current grails 2.1.5 has this bug fixed. Here is the reported bug http://jira.grails.org/browse/GRAILS-9644

edited May 18 '13 at 19:14

answered May 18 '13 at 17:23

Nguyen Le

58
6

score 0 · Answer 2 · answered Apr 19 '12 at 05:28

0

Couldn't you just use countBy* - like

    SharedDocuments.countByAuthorAndReceiver(user, receiver)

answered Apr 19 '12 at 05:28

Kelly

3,709
4
20
31

Hussain Fakhruddin · Answer 3 · 2012-04-21T05:38:37.543

0

If you want the result as as you described in your query then you need the id of the author or any other specific property like email.


def sharedDocumentsInstanceList = SharedDocuments.createCriteria().list(params){
    eq("receiver.id",session.uid)  
    projections{
        groupProperty "author"
        count "id",'mycount'

    }
     order('mycount','desc')
    maxResults(params.max)
}

edited Apr 21 '12 at 05:38

answered Apr 19 '12 at 08:06

Hussain Fakhruddin

3,202
4
25
36

no matter what I do my result set just has count no other information. Printing sharedDocumentsInstanceList in the code you have given me produces '[2, ]' output – Sap Apr 19 '12 at 14:49
I'm doing somewhat exactly the same thing but with a named query and without maxResults it works fine for me :(. BTW I'm not even doing author.id just grouping on author should work too – Hussain Fakhruddin Apr 21 '12 at 05:38

Grails projections group by and count

3 Answers3