I am working on a Grails application with MySQL database. I\u00a0had 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 :<\/p>\n
[plain]
\nEmployee e1 = new Employee(empId:’emp10′).save()
\nEmployee e2 = new Employee(empId:’emp2′).save()
\nEmployee e3 = new Employee(empId:’emp1′).save()
\nEmployee e4 = new Employee(empId:’1emp’).save()
\nEmployee e5 = new Employee(empId:’10emp’).save()
\nEmployee e6 = new Employee(empId:’2emp’).save()<\/p>\n
Employee.createCriteria().list([sort:’empId’])
\n \/* OR *\/
\nEmployee.createCriteria().list{
\n order(’empId’)
\n}
\n[\/plain]<\/p>\n
should give result like [e4,e6,e5,e3,e2,e1]<\/strong>. But according to sql sorting the result would be [e5,e4,e6,e3,e1,e2]<\/strong><\/p>\n One thing to note was that criteria query implemented sorting at the database end. To migrate the functions in script to database you have to unzip the script and write following command on terminal:<\/p>\n [shell]mysql -u username -p database_name < \/path\/to\/unzipped\/directory\/natsort.install.mysql[\/shell]<\/p>\n 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<\/a>). Thus I had to create a new transient field using SQL function in formula. So I did something like this :<\/p>\n [groovy] Now if I had to get Employee objects are sorted by empId alphanumerically, what I would do is :<\/p>\n [groovy] Hope this was helpful to you!<\/p>\n","protected":false},"excerpt":{"rendered":" I am working on a Grails application with MySQL database. I\u00a0had 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 : [plain] Employee e1 = new Employee(empId:’emp10′).save() Employee e2 = new […]<\/p>\n","protected":false},"author":39,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"iawp_total_views":24,"footnotes":""},"categories":[7],"tags":[761,269,763,29,4840,762,76,760,505],"class_list":["post-5051","post","type-post","status-publish","format-standard","hentry","category-grails","tag-alphanumeric-sort","tag-criteria-query","tag-formula","tag-gorm","tag-grails","tag-human-sort","tag-mysql","tag-natural-sort","tag-sorting"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t
\nAfter googling around, I came across a script<\/a> that had a SQL function which converted the field value to string that can alphanumerically sorted easily at database end.<\/p>\n
\nclass Employee {
\n String empId
\n String empIdSortField \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \/\/transient field
\n static mapping = {
\n empIdSortField formula: ‘natsort_canon(empId, "natural")’
\n }
\n}
\n[\/groovy]<\/p>\n
\nEmployee.createCriteria().list {
\n order(’empIdSortField’)
\n}
\n[\/groovy]<\/p>\n