CODEWITHSUNDEEP
hibernategrailssubquerygrails-orm
Aseem Bansal
Aseem Bansal

Reputation: 6962

Subqueries for querying associations in GORM

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

Upvotes: 5

Views: 2276

Answers (3)

John
John

Reputation: 11831

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.

Upvotes: 2

victorf
victorf

Reputation: 1048

Why don't you try something like this...

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

...and then attach this subquery at your main query.

Take a look at this, possibly it will help you: Can't get "count" and "groupBy" with Grails DetachedCriteria

Upvotes: 1

Joshua Moore
Joshua Moore

Reputation: 24776

The problem you are facing is that namedQueries aren't suppose to be used with projections (summaries). Take a step back, and reset your expectations for what can and should be done when you use namedQueries.

Named queries are a convenient way to create a query that identifies a subset of a larger set. Think of it as progressively filtering the data. If you look at the examples in the documentation you will see this being demonstrated. This makes namedQueries very powerful, but in that specific use case.

Example of this from the documentation:

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

Another example might be:

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

In other use cases, such as working with summaries and projections they fall short. Not because they are poorly designed or incapable, but because that's not what they are designed to do.

Projections and summaries really should be created through createCriteria or HQL itself.

While you might be able to hack namedQueries to do projections and summaries you'd be fighting the framework to do so. Use the right tool for the right job, and get more done.

Updated However, all of that said, this blog post explains how you can use projections with named queries (and criteria). It should give you a better understanding of how projections work in GORM.

Upvotes: 2

Related Questions