criteria query « Intelligrape Groovy & Grails Blogs

Posts Tagged ‘ criteria query ’

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 »            

           

        
    

        

      

        Alphanumeric Sorting using Criteria Query (with MySQL database)        

     

            Posted by             on February 2nd, 2012          

      

        
I am working on a Grails application with MySQL database. I had a use case in which I had to implement alphanumeric sorting using Criteria Query on Grails. By alphanumeric sorting I mean if there is a class Employee with field empId then on doing :


 
Employee e1 = new Employee(empId:'emp10').save()
Employee e2 = new Employee(empId:'emp2').save()
Employee e3 = new Employee(empId:'emp1').save()
Employee e4 = new Employee(empId:'1emp').save()
Employee e5 = new Employee(empId:'10emp').save()
Employee e6 = new Employee(empId:'2emp').save()

Employee.createCriteria().list([sort:'empId'])
            /* OR */
Employee.createCriteria().list{
    order('empId')
}



should give result like [e4,e6,e5,e3,e2,e1]. But according to sql sorting the result would be [e5,e4,e6,e3,e1,e2]



One thing to note was that criteria query implemented sorting at the database end.

After googling around, I came across a script that had a SQL function which converted the field value to string that can alphanumerically sorted easily at database end.


To migrate the functions in script to database you have to unzip the script and write following command on terminal:


mysql -u username -p database_name < /path/to/unzipped/directory/natsort.install.mysql


Now only thing I had to do was to get a derived field from the Grails property that had to be sorted alphanumerically which was easily possible as the Grails had ability to create transient field using formula (thanks to wonderful Blog by Uday). Thus I had to create a new transient field using SQL function in formula. So I did something like this :


class Employee {
    String empId
    String empIdSortField           //transient field
    static mapping = {
        empIdSortField formula: 'natsort_canon(empId, "natural")'
    }
}



Now if I had to get Employee objects are sorted by empId alphanumerically, what I would do is :  


Employee.createCriteria().list {
    order('empIdSortField')
}



Hope this was helpful to you!

      

      

        Tags: , , , , , , , , 

        
 Posted in
        Database, GORM, Grails 

         

           

                           3 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 »