I have an SQ: query which I want to use in Grails but need to convert it to HQL so I could use it for findAll method. The code goes like
artifacts = Artifact.findAll(
"FROM Artifact WHERE id NOT IN ( " +
"SELECT artifact_id FROM Classification " +
"WHERE active = :active) AND document_id =:doc_id",
[active: '1', doc_id:document.id, max:limit, offset:startIndex])
This is giving me stringIndexOutofBounds error. I think it s probably because of the syntax because the SQL works fine on the database.
You use class and field names in HQL, and table and column names in SQL. So you shouldn't see underscores (at least not many) in HQL queries. Also, it's unlikely to be a factor here, but HQL queries run from findAll and those run from executeQuery can be slightly different. I can't remember what the difference is, but executeQuery is the correct one no matter which domain class is involved.
It's hard to know without seeing the domain classes, but it seems like artifact_id should be artifact.id, and document_id should be document.id. And since you have the Document instance, it's more O-O-proper to compare the objects and not their ids. Finally I'm assuming that active is a boolean property, so it needs a boolean value and not a 1 or 0. So, putting this all together, my best guess is that this is what you want:
def artifacts = Artifact.executeQuery(
"FROM Artifact WHERE id NOT IN ( " +
"SELECT artifact.id FROM Classification " +
"WHERE active = :active) AND document =:doc",
[active: true, doc:document],
[max:limit, offset:startIndex])
Note that you need to separate param values from pagination controls into two maps.
The method you want is findAll(String query, Map namedParams, Map queryParams) but you've got your namedParams and queryParams in the same array. Seperate them and it should work.
artifacts = Artifact.findAll(
"FROM Artifact WHERE id NOT IN ( " +
"SELECT artifact_id FROM Classification " +
"WHERE active = :active) AND document_id =:doc_id",
[active:'1', doc_id:document.id],
[max:limit, offset:startIndex])
