Grails To Adjust _idx Data On Remove

Question

We have a Domain class like so:

class Attribute {
  List attributeParameters = []
  static hasMany = [attributeParameters: AttributeParameter]
}

AttributeParameter belongs to an Attribute. There are several other relationships defined here and there is an instance where we need to delete several AttributeParameters across many Attributes. So we're doing this:

def parameters = AttributeParameter.findAllBySomeOtherCriteria(foo)
parameters*.delete()

That works, however, there is a column in attribute_parameter called attribute_parameters_idx that gets out of sequence. This column exists because we've defined the collection as a List. And if we were do to attribute.removeFromAttributeParameters(ap), Grails would automatically adjust this column. But since we're not removing the data that way, they get out of sequence.

When this happens we get null values back when looping over the collection from Attribute. This is not a cache issue or refreshing the parent issue. What happens is when we do:

attribute.attributeParameters.each { }

Something internally is adding additional rows in the iteration based on the _idx values. Since one (or more) of those rows are missing, Grails is adding a null row in the Collection. You can't see this when debugging, but you can watch it as the iteration occurs.

My question, finally, is:

• Is there a way to tell Grails to update the _idx values for a specific collection and if not
• Is there a way to get this _idx column and modify its values manually via Gorm

Answer 1

I think this comes down to how Hibernate handles collections, especially indexed ones like List. Without using addTo and removeFrom Hibernate doesn't know it needs to update the indexes.

As a workaround, I can think of something like this:

Add an afterDelete event on your AttributeParameter class. In it, you'll want something like:

def afterDelete() {
    def sql = new Sql(dataSource)
    sql.execute("update attribute_attribute_parameter set attribute_parameter_idx = " +
    "attribute_parameter_idx - 1 where attribute_parameter_idx > " +
    "(select attribute_parameter_idx from attribute_attribute_parameter where " +
    "attribute_parameter_id = $id and attribute_id = $attribute.id) " +
    "and attribute_id = $attribute.id")
}

PS. Despite it being private, I think you could use getIndexColumnName() to get the index column name dynamically.