Subqueries for querying associations in GORM

grails, grails-orm, hibernate, subquery

Solution

Your query is "give me the topics matching a given list of topics and their respective number of resources and subscriptions."

(Edited to reflect the comments) I think this may work for you:

def myTopicIds = ['1', '2']  // search for ids 1 and 2
def criteria = Topic.createCriteria()
def results = criteria.list() {
    'in'('id', myTopicIds)  // restrict results to only those matching your given ids
    projections {
        property("id")
        resources {
            countDistinct('id')   
        }
        subscriptions {
            countDistinct('id')   
        }
       groupProperty('id')
    }
}.collect {
        [
            topicId: it[0],
            numRes: it[1],
            numSubs: it[2]
        ]
    }

The collect changes the results collection and allows you to refer to the results as a map, where each item has 3 keys with the names shown, otherwise you'll have to refer to just nameless array items.

Problem

I have the following domains in GORM. ``` class Topic { static hasMany = [resources: Resource, subscriptions: Subscription] } class Resource { static belongsTo = [resourceOf: Topic] } class Subscription { static belongsTo = [subscriptionOf: Topic] } ``` I have been unable to find the syntax for running subqueries using criterias/named subqueries. For example how can I write the below query in GORM using criterias. ``` select topic.id, (select count(*) from Resource where resourceOf.id = topic.id) as numRes, (select count(*) from Subscription where subscriptionOf.id = topic.id) as numSubs from topic where topic.id in (<My topic ids>) group by topic.id; ``` This is very basic thing but I have unable to find the documentation for the same. Does anyone know how this can be done using namedQueries in GORM? My grails version is 2.4.4

Original source