Count one-to-one relationship in grails

Question

I have a problem doing a query. That I want to do is access with a query to the data in my "String code" in MyDomainA through a query from MyDomainB. The relationship between the tables is unidirectional one to one.

Is possible use a gorm way to do this??

Domain A:

class LicenceType {

    String code
    String description
    Double price

    static constraints = {
    }

}

TABLE DOMAIN A

code description
A    this is A
B    this is B
C    this is C

Domain B: (have the unidirectional relationship)

class VoiceUser {

LicenceType licenceType

String username
String email
String nameID

}

TABLE DOMAIN B

User 
1    
2    
3    
4  

That I want to do is know how many users have the same code(code is a column of DomainA and both tables have a unidirectional relationship as I indicated before).

This is that I'm trying to do that is wrong... Controller:

        def resulta = VoiceUser.executeQuery('SELECT a.code, COUNT(b.nameID) FROM VoiceUser AS b INNER JOIN b.licenceType AS a GROUP BY a.code')

def resultCount = resulta[0]

This is some example result that I hope...

Users with code A = 2
Users with code B = 2
Users with code C = o

Answer 1

The trick is to do a group by on the code and a count() on the user. You can do this using either HQL or a criteria query.

HQL

Here's an example in HQL:

VoiceUser.executeQuery('SELECT licence.code, COUNT(user) FROM VoiceUser AS user INNER JOIN user.licenceType AS licence GROUP BY licence.code')

If you're familiar with SQL, most of this should make sense right away. An important difference is the syntax for joining domain classes. HQL deals with domain classes, not tables.

Criteria query

And here's the equivalent criteria query.

VoiceUser.withCriteria {
    projections {        
        licenceType {
            groupProperty('code')
        }

        count('id')
    }
}

Alternative queries

The queries shown above return a List<List> like this:

[
    ['A', 2], 
    ['B', 2], 
    ['C', 0]
]

If you provide a LicenceType (or its code) as input to the query, then you can get the count for just that LicenceType. For instance, here are examples which retrieve the user count for licence code 'A'.

HQL

def result = VoiceUser.executeQuery('SELECT COUNT(user) FROM VoiceUser AS user INNER JOIN user.licenceType AS licence WHERE licence.code = :code', [code: 'A'])[0]

Criteria query

def result = VoiceUser.createCriteria().get {
    licenceType {
        eq('code', 'A')
    }

    projections {        
        count('id')
    }
}

Additional resources

I've got a series of articles which explain HQL, criteria, and where queries in detail; such as how to use projections and joins. Feel free to check them out.