Gaurav Sharma « Intelligrape Groovy & Grails Blogs
Subscribe via E-Mail:

Gaurav Sharma

Posts by Gaurav Sharma:

  • Clear Time function for MySQL

    12 Apr 2012 in Database

    Hi all,

    Here is a simple function that can allow you to clear time from the DATETIME field of your database table

    DROP FUNCTION IF EXISTS cleartime;
delimiter //
CREATE FUNCTION cleartime(dt datetime) RETURNS DATETIME
  NO SQL
  DETERMINISTIC
BEGIN
  DECLARE t    varchar(15);               -- the time part of the dt
  DECLARE rdt  datetime default dt;    	  -- the datetime after time part is cleared

  SET t = TIME(dt);
  SET rdt = SUBTIME(dt,t);

  RETURN rdt;
END //
delimiter ;

    To import this function to your database just copy and paste above code in MySQL command prompt with your database selected.

    The implementation is shown in following example:

    select cleartime(datetime_field) from table_name;     -- to view field with its time cleared
                     --OR
update table_name set datetime_field=cleartime(datetime_feild) from table_name;
    • Share/Bookmark

  • Alphanumeric Sorting using Criteria Query (with MySQL database)

    02 Feb 2012 in Database& GORM& Grails

    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!

    • Share/Bookmark