CODEWITHSUNDEEP
sqlgrailshql
Leni Kirilov
Leni Kirilov

Reputation: 1315

Grails executeUpdate with Limit doesn't work for deletion

I'm trying to do something like this in Grails HQL but it doesn't work

I want to remove up-to 5 elements out of list of IDs which could contain 10 elements. I order them by date and delete only the oldest 5

Entity.executeUpdate(
        "DELETE FROM Entity WHERE id in (:list) ORDER BY date LIMIT 5",
        [list: oldestIdsForRemoval])

Pure SQL works just fine

DELETE FROM entity WHERE id in ('8f027db299354a869af51e1f0a60ede1', '96df61161c32491f8aa4a14c5017852c', '88aa7b2bb6e1444f8d892fff10b8adcd') ORDER BY date ASC LIMIT 1

Is this even possible? I've tried also adding max:5 parameter to the list of parameters, but it fails.

I'm using Grails 1.3.6 . If it is fixed in newer version, would be good to know , although I cannot use it. Documentation doesn't help much in this regard: http://grails.github.io/grails-doc/1.3.7/ref/Domain%20Classes/executeUpdate.html

Upvotes: 3

Views: 251

Answers (1)

Emmanuel Rosa
Emmanuel Rosa

Reputation: 9885

When working with HQL, consider using Hibernate's User Guide. It contains a section on HQL which covers the language pretty well. Here's the root directory: https://docs.jboss.org/hibernate/orm/

Another source, though a more cryptic one, is the ANTLR source for the parser used to parse HQL code: https://github.com/hibernate/hibernate-orm/blob/master/hibernate-core/src/main/antlr/hql.g

It is from the ANTLR code that you may arrive at the conclusion, as I did, that HQL does not support LIMIT. This being the case, you have a couple of options:

  1. Use two queries. A SELECT, along with a max, to select the IDs, and then a DELETE for those specific IDs.
  2. Use a native query instead (ex. SQL) to give you full access to the underlying database.

Upvotes: 1

Related Questions