How to count ocurrences in 1 to many relationship in gorm - grails

Question

Hi, I have 2 domain classes

class A {
  ....
  static hasMany = [bs:B]
}
class B {
  int code
  ....
}

How can I list the number of ocurrences of all codes in B in all the A table?

Something like

b.each { thisb ->
  int ocurrences = A.bs.findAll{it == thisb}.size()
  ...
}

Thanks

Answer 1

I think the reason that I'm a little confused by this question is that technically it's actually a many-to-many relationship, not really a one-to-many. Grails will create a join table ("a_b") for this relationship (because B doesn't have a belongsTo relationship to A).

The way you have your A domain constructed the hasMany relationship is a set, so B will only appear a single time in the "bs" collection. So, I believe, all you're asking is how many As have a B.

If thats true, you can use HQL to answer your question (you can also use criteria builders, but I prefer hql). Here's an example (using the build-test-data plugin to construct objects with buildLazy and adding a String name to A):

def a1 = A.buildLazy(name: "one")
def a2 = A.buildLazy(name: "two")
def a3 = A.buildLazy(name: "three")
def a4 = A.buildLazy(name: "four")

def b1 = B.buildLazy(code: 888)
def b2 = B.buildLazy(code: 999)

a1.addToBs(b1)
a2.addToBs(b1)
a3.addToBs(b1)
a4.addToBs(b1)

a1.addToBs(b2)

println "Number of As that have each B = " + 
    A.executeQuery("select count(b), b.code from A as a join a.bs as b group by b.code")

println "Number of As with a specific B = " + 
    A.executeQuery("select count(*) from A as a join a.bs as b where b = :b", [b: b1])

results in:

Number of As that have each B = [[1, 999], [4, 888]]
Number of As with a specific B = [4]