CODEWITHSUNDEEP
grailsdynamicgrails-ormone-to-manycriteria
innovatism
innovatism

Reputation: 389

Grails Criteria dynamic AND conditions for one-to-many relationship

I have a domain class

class Url {
        UUID id
        String url
        static hasMany = [            
            indications:UrlIndication
        ]
        ...
}

And 

class UrlIndication  {
    UUID id
    String name

    static belongsTo =  Url
   ...

}

I want to choose urls so that it has all the necessary UrlIndication elements in a given list indicationsId. For that I use an association and criteria like this one:

indications {
                and {
                        indicationsId.each{
                            indication->
                                eq ('id',UUID.fromString(indication as String))
                        }
                    }
            }

However, all I got is an empty result. Can you suggest any modifications/ other methods so that I can do this? Thanks in advance

Upvotes: 1

Views: 1313

Answers (2)

Emmanuel Rosa
Emmanuel Rosa

Reputation: 9885

Your query returned an empty list because it's the equivalent of the expression (pseudo-code): if 1 = 1 and 1 = 2 and 1 = 3

Such an expression would always be false. in or inList would not work for the reason @innovatism described.

In theory, Criteria's eqAll() or HQL's = ALL would work. But, I don't know for sure because I could not get either one to work.

What will work is to use inList to return a subset of Urls: those which contain at least one of the UrlIndication IDs. Then use Groovy's containsAll() to finish the job.

def ids = indicationsId.collect { UUID.fromString(it as String) }

Url.createCriteria()
    .buildCriteria {
        indications {
            inList 'id', ids
        }
    } 
    .setResultTransformer(org.hibernate.Criteria.DISTINCT_ROOT_ENTITY)
    .list()
    .findAll {
        it.indications.id.containsAll(ids)
    }

Since the query has the potential to return duplicate Url instances, the ResultTransformer is set to return a unique list.

Finally, findAll() is used along with containsAll() to filter the list further.

Using eqAll (maybe)

Something like the following might work. Something funky is going on with Grails' HibernateCriteriaBuilder that causes the eqAll method to look up properties in the root entity; completely ignoring the sub criteria. So the following uses Hibernate directly. It didn't work for me, but it's as close as I could get. And it gave me a head-ache!

Url.createCriteria().buildCriteria {}
    .createCriteria('indications', 'i')
    .add(org.hibernate.criterion.Property.forName('i.id').eqAll(org.hibernate.criterion.DetachedCriteria.forClass(UrlIndication)
        .add(org.hibernate.criterion.Restrictions.in('id', ids))
        .setProjection(org.hibernate.criterion.Property.forName('id'))
    ))
    .setResultTransformer(org.hibernate.Criteria.DISTINCT_ROOT_ENTITY)
    .list()

The problem I had is I could not get Restrictions.in to work. Restrictions.eq works fine.

Upvotes: 1

injecteer
injecteer

Reputation: 20699

the in clause should do:

indications {
     'in' 'id', indicationsId.collect{ UUID.fromString indication.toString() }
}

Upvotes: 0

Related Questions