createCriteria « Intelligrape Groovy & Grails Blogs

Posts Tagged ‘ createCriteria ’

Querying Domain class with hasMany String Type

Posted by on September 25th, 2012

In my recent project there was a use case where I had to query domain’s hasMany but relationship was not with any domain class but rather String type.

Consider example

class Blog {

static hasMany = [tags: String]

}

Now to find all the blogs with tag “grails” , our normal way of querying hasMany relationships will not work in this scenario. In this scenario hql can be usefull:

Blog.findAll("from Blog b where :tag in elements(b.tags)", [tag: "grails"])

This will return you all the blogs with “grails” tag.

Hope this helps.

Puneet
puneet@intelligrape.com

Tags: , ,
Posted in Grails
No Comments »

Using aliases in aggregate functions of criteria queries

Posted by on February 13th, 2012

When using criteria queries, many times we encounter a use case where we need to order our result not on any field of domain class but on our result set, in these cases aliases can be used. 



In my project,  use case was to find most liked products from the domain:


ProductStat{
Date dateCreated
Product product
}



To find the result, the query I used was :


ProductStat.createCriteria().list() {
            projections {
                groupProperty("product")
                count("product", "totalProducts")  // alias totalProducts
            }
            order("totalProducts", "desc")
}





In this way using alias “totalProducts” I was able to order the result according to the count of products.



      

      

        Tags: , , , 

        
 Posted in
        Grails 

         

           

                           2 Comments »            

           

        
    

        

      

        Criteria Query and pagination params        

     

            Posted by             on July 14th, 2010          

      

        
I have been using the following code to get paginated result and the total number of results returned irrespective of the pagination params.





 def result=SampleDomain.createCriteria().list(){
// multiple restrictions
   maxResults(params.max)
   firstResult(params.offset)
} // Return type is ArrayList
 
Integer  totalResult=SampleDomain.createCriteria().count(){
// multiple restrictions
// maxResults(params.max)
// firstResult(params.offset)
}





Clearly duplicating the same closure except for the pagination restrictions was not a good solution.


After a little googling and reading mailing lists I got the solution. Passing pagination params to createCriteria.list() returns result of type PagedResultList which provides many useful methods. The getTotalCount() method of PagedResultList class returns the actual number of results returned irrespective of the pagination restrictions(maxResults and firstResult). Also the result contains only those records fulfiling maxResults and firstResult restrictions.





def result=SampleDomain.createCriteria().list(max:params.max, offset:params.offset){
// multiple/complex restrictions
   maxResults(params.max)
   firstResult(params.offset)
} // Return type is PagedResultList





Thanks to all the active users of Grails mailing list.


Here are few useful links:

http://www.pubbs.net/grails/200912/2269

http://www.grails.org/doc/1.0.x/api/grails/orm/PagedResultList.html

http://jira.codehaus.org/browse/GRAILS-2672


Bhagwat Kumar

bhagwat(at)intelligrape(dot)com





Cheers!

~~Bhagwat Kumar~~

bhagwat(at)intelligrape(dot)com

http://twitter.com/bhagwatkumar

http://in.linkedin.com/in/bhagwatkumar

      

      

        Tags: , , , , 

        
 Posted in
        Grails 

         

           

                           4 Comments »            

           

        
    

        

      

        Grails criteria query example for unidirectional one-to-many relationships        

     

            Posted by             on June 14th, 2010          

      

        
Following is an example of unidirectional one-to-many relationship:



class Employee {
    String name
    static hasMany = [roles: Role]
}
 
class Role {
    String name
}



How can we find all the employees with a particular role – “Grails Developer”?



Role role = Role.findByName("Grails Developer")



One way of doing this can be:



Employee.list().findAll{role in it.roles}



This is an inefficient way since, we are fetching all the records from the database every time. Also, in case we are showing paginated view of results, we will need to manage pagination ourself. 


A better way of this will be using createCriteria() query:



List<Employee> employees = Employee.createCriteria().list{
    roles{
        eq('id', role.id)
    }	
    firstResult(20)
    maxResults(20)	
}



I am wondering what will be the query in case Role.groovy is an enum.


–

~Aman Aggarwal

aman@intelligrape.com


http://www.IntelliGrape.com/

      

      

        Tags: , , , , 

        
 Posted in
        Database, Grails 

         

           

                           1 Comment »