CODEWITHSUNDEEP
javahibernategrailshql
Micor
Micor

Reputation: 1542

Order by substring of ID as integer in HQL with Grails

Is there a way to order by substring of ID casted to integer using HQL or/and Criteria?

For example:

class Foo {
  String id
  String name
}

Sorted Dataset would look like:

90-1  David
90-4  Mike
101-1 Jack
101-2 Peter
105-1 Jon
105-7 Jane

With MySQL database and native sql it would be done like:

order by 
CAST(substring_index(id, '-', 1) AS UNSIGNED),
CAST(substring(id, length(substring_index(id, '-', 1)) + 2) AS UNSIGNED)

I tried using formula as suggested but that did not work because UNSIGNED was treated as alias and generated SQL had _this.UNSIGNED

Integer fooFirstID
Integer fooSecondID

static mapping = {
    fooFirstID formula: "CAST(substring_index(id, '-', 1) AS UNSIGNED)"
    fooSecondID formula: "CAST(substring(id, length(substring_index(id, '-', 1))
                           + 2) AS UNSIGNED)" 
}

I see that Hibernate supports substring as well as cast to Hibernate type but cannot find equivalent of a substring_index.

Thanks.

Upvotes: 3

Views: 3069

Answers (2)

Gepsens
Gepsens

Reputation: 673

Your problem is that the type does not get recognized as an Hibernate type. 'As' is treated as the alias binder no matter what you do. This is strange, because as the Hibernate documentation states :

cast(... as ...), where the second argument is the name of a Hibernate type, and extract(... from ...) if ANSI cast() and extract() is supported by the underlying database

Even though you are using mysql, you can try the postgre cast syntax : 

substring_index(id, '-', 1)::integer

If you are using H2, it's INT, not INTEGER

cast(substring_index(id, '-', 1) as INT)

Also note that the relevant operations where you could use the unsigned notion in Java are binary, hex or octal conversions (for the leading bit, obviously). These always treat the Integer as an unsigned int anyway, so it does not matter. If you really want to apply that notion, you'll have to use a short with a leading bit, and cast it to int, that will give you a 'truely unsigned' int.

Upvotes: 0

Sebastien Lorber
Sebastien Lorber

Reputation: 92120

You can use the @Formula annotation and order on the annotated property, it works fine.

@Formula("CAST(substring_index(id, '-', 1) AS UNSIGNED)")
public varType getToto() {
  return toto;
}

@Formula("CAST(substring(id, length(substring_index(id, '-', 1)) + 2) AS UNSIGNED)")
public varType getTata() {
  return tata;
}

And then you do in HQL: order by toto,tata

Btw you could consider using a composite id.

Upvotes: 1

Related Questions