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

Question

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

Emmanuel Rosa · Accepted Answer · 2015-09-17T20:24:49.290

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.

FInally, I had the same approach, but that bothered me when I want to use paginate. Do you have any suggestions about that?Thanks a lot! — innovatism, Sep 17 '15 at 17:33
I just realized _you're_ innovatism. LOL! Anyway, You won't be able to use GORM pagination with this method because the offset/max values become useless when you filter the list down after getting the results from GORM. You'd have to paginate the final list. I'm not sure if such an implementation already exists. I updates my answer with something that might work, if you've got some keyboard grease left. — Emmanuel Rosa, Sep 17 '15 at 20:25
@innovatism, a new question similar to this one came up recently. There's an HQL query that might work for you. Take a look: http://stackoverflow.com/questions/32856121/grails-how-to-best-construct-a-hibernate-criteria-builder-to-search-hasmany — Emmanuel Rosa, Sep 30 '15 at 21:02

score 0 · Answer 2 · answered Sep 17 '15 at 11:37

0

the in clause should do:

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

answered Sep 17 '15 at 11:37

injecteer

20,038
4
45
89

This will give you urls having either of the elements or both, and not urls that have ALL of these element. – innovatism Sep 17 '15 at 12:12

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

2 Answers2

Using eqAll (maybe)