Subqueries for Association Requests in GORM

Question

I have the following areas in GORM.

class Topic {
static hasMany = [resources: Resource, subscriptions: Subscription]
}

class Resource {
static belongsTo = [resourceOf: Topic]
}

class Subscription {
 static belongsTo = [subscriptionOf: Topic]
}

I could not find the syntax for running subqueries using the / named criteria subqueries. For example, how can I write below a query in GORM using criteria.

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 a very simple thing, but I can not find the documentation for it.

Does anyone know how this can be done using namedQueries in GORM?

My version of grails is 2.4.4

Answer 1

Your request: "Give me topics that correspond to this list of topics and their number of resources and subscriptions."

(Edited based on comments) I think this might 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]
        ]
    }

, 3 , .

Answer 2

, , , namedQueries (). reset , , namedQueries.

- , . . , , . namedQueries , .

:

// get all old publications with more than 350 pages
// and the word 'Grails' in the title
def pubs = Publication.oldPublicationsLargerThan(350).findAllByTitleLike('%Grails%')

:

def divisions = Division.locatedIn('Sweden').havingNoOustandingBalance().havingMoreThanXEmployees(50).currentlyOpen().notOnFire()

, , . , , , , .

createCriteria HQL.

namedQueries, , , . .

, , , ( ). , GORM.

Answer 3

- ...

def exampleSubQuery = new grails.gorm.DetachedCriteria(Resource).build {
   // your criteria here, "normal" GORM
   // and the same for Subscription 
}

... .

, , : "count" "groupBy" Grails DetachedCriteria