2

I have two classes

Quiz{
  String name
  static hasMany[tags:Tag]
}

Tag{
    String tag
}

How can I write the following query in grails using criteria builder

select count(tag_id),tag  from quiz_tag 
left outer join tag on tag_id=id
group by tag_id,tag
tim_yates
  • 167,322
  • 27
  • 342
  • 338
Sap
  • 5,197
  • 8
  • 59
  • 101

3 Answers3

3

You could do this with a criteria query if you map the many-to-many table with a domain class, like explained here.

class QuizTag {
    Quiz quiz
    Tag tag

    ...
}

def counts = QuizTag.withCriteria {
    projections {
        groupProperty('tag')
        rowCount()
    }
}

The counts variable will be a List of [Tag object, count of the Tag] lists.

schmolly159
  • 3,881
  • 17
  • 26
  • He schmolly, I have been trying to do this but I am getting this error "NULL not allowed for column "QUIZ_ID"; SQL statement: insert into quiz_tag (id, version, date_created, last_updated, tag_id, quiz_id, tags_idx) values (null, ?, ?, ?, ?, ?, ?) [23502-164]" Any idea? I have hasMany = [quizTags:quizTag] – Sap Oct 08 '12 at 01:46
  • Can you beleive it I fixed it minutes after I posted the last message:) – Sap Oct 08 '12 at 01:51
  • Hey Schmolly I tried doing exactly what you said here but now I am getting an error somewhere else in my code! can you please have a look at my other question, just in case you may have an answer? http://stackoverflow.com/questions/12808800/grails-a-different-object-with-the-same-identifier-value-was-already-associated – Sap Oct 11 '12 at 19:12
0

It seems that you want to find out how many times is each Tag used in Quizzes. By defining your domain classes like you did, you got a middle table quiz_tag and by using row SQL you can find out the result to this question.

But it seems that this is not possible with critera builder, that is Hibernate, because from Tag you can not access joined table. You could do it from the Quiz though, because Quiz has hasMany[tags:Tag]

You can access joined table from Tag if you modify it like this:

Tag{
    String tag

    static belongsTo = Quiz
    static hasMany[quizes:Quiz]
  }

Your database model remains the same, but now Hibernate can go from Tag to joined table. You can try this HQL query which will give you Tag and number of occurances in quizzes :

Tag.executeQuery("SELECT t, count(q) FROM Tag t JOIN FETCH t.quizes q GROUP BY t")

I think it is easier just with HQL, but I guess you could do it with criteria builder too, maybe by using sqlGroupProjection for grouping if it is supported.

MBozic
  • 1,132
  • 1
  • 10
  • 22
  • Any chance you know how to do this with criteriaBuilder? Only reason is HQL is not supported in lots of "datastores" mainly NoSql. I am not sure if criteriaBuilder will be supported or not but chances are higher! By the way you are right about the purpose of the query, just like SO I want to be able to show which tag is used the most and display it in the order of "popularity" – Sap Oct 05 '12 at 13:50
  • I get this exception if I try the relationship you suggested No owner defined between domain classes [class com.easytha.Quiz] and [class com.easytha.Tag] in a many-to-many relationship. Example: static belongsTo = com.easytha.Tag ->> – Sap Oct 06 '12 at 08:20
0

I had a similar problem where Customers have one ar many associations to Stores. It was possible to use criteria to count the stores using the following code.

class Customer {
    static hasMany = [stores : Store]
    ... 
}

class Store {
    Customer customer
    ...
}

def c = Store.createCriteria()
def results = c.list {
    customer {
        'in'("customerNumber",['1-1','1-2','1-3'])
    }
    projections {
        sqlGroupProjection 'customer_id, count(customer_id) as numberOfStores', 'customer_id', ['customer_id', 'numberOfStores'], [INTEGER, INTEGER]
    }
}

Result: [[1, 3], [2, 3]]

Where returning fields are [customer.id, numberOfStores]

Daniel Bjuhr
  • 1