5

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

Aseem Bansal
  • 6,722
  • 13
  • 46
  • 84

3 Answers3

2

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.

Joshua Moore
  • 24,706
  • 6
  • 50
  • 73
  • Thanks for the explanation. It cleared a few things but it is not only about namedQueries. Earlier I had written`criterias/named subqueries`. I forgot to repeat the same in the last sentence. Can you share how-to using criterias? – Aseem Bansal Mar 02 '15 at 06:12
2

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.

John
  • 10,837
  • 17
  • 78
  • 141
  • This is almost working. If I am querying only resources then it works and if I query only subscriptions then also it works. But together I am getting the count for resources only. Maybe because they are more. The queries being fired consists of inner joins not subqueries so maybe there is problem in grouping. – Aseem Bansal Mar 03 '15 at 06:22
  • Sorry, what do you mean "querying only resources" and "querying only subscriptions" please? – John Mar 03 '15 at 08:44
  • I mean keeping only `resources { count('id', 'numRes') }` or the other in the criteria. – Aseem Bansal Mar 03 '15 at 10:09
  • Hmmm, I think perhaps the 2nd argument to the `count` calls is wrong. What happens if you remove the `, 'numRes'` , `, 'numSubs'`, `groupProperty('id') ` and `collect{}` statements? – John Mar 03 '15 at 10:20
  • It gave the total count then which were also wrong. That would happen obviously as the value is not being grouped by any id. – Aseem Bansal Mar 03 '15 at 15:29
  • 1
    Replace the `count` with `countDistinct` and remove the second parameter from the `count` lines, then the code above works as expected. – John Mar 08 '15 at 23:10
  • Replacing `count` with `countDistinct` works perfectly. Please update the answer. – Aseem Bansal Mar 09 '15 at 04:52
  • A word of warning regarding this. This has a limitation as this creates join query instead of subqueries. If either of the association is not present then instead of having result zero this will not consider the complete row. This is because this creates a join instead of subquery. – Aseem Bansal Mar 09 '15 at 18:44
1

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

Community
  • 1
  • 1
victorf
  • 978
  • 2
  • 17
  • 35
  • 1
    The problem with `DetachedCriteria` seems to be that I cannot specify any parameters. But for me to be able to make a subquery with `where` I need parameters. The linked answer also faces the same problem. I tried making a subquery using DetachedCriteria but as I could not use something like `count(_detachedCriteriaWithParameter_)` I couldn't get the count. – Aseem Bansal Mar 08 '15 at 16:31