CODEWITHSUNDEEP
hibernategrailsgrails-ormcriteriadetachedcriteria
nst1nctz
nst1nctz

Reputation: 333

Grails Hibernate Criteria: How do I specify multiple criteria for one association?

Edit 1: To further clarify my problem, I updated my example code so that only one property is relevant.

In the grails documentation, the following example is given in the Criteria section under querying associations:

We can query [...] association[s] by using the property name transactions as a builder node:

def c = Account.createCriteria()
def now = new Date()
def results = c.list {
    transactions {
        between('date', now - 10, now)
    }
}

The above code will find all the Account instances that have performed transactions within the last 10 days [...].

This is all great, but how do I do more complex queries for associations? I mean, not everytime I want to say

query all domain objects that have at least one associated object that satisfies my condition,

but rather

query all domain objects that have at least one associated object that satisfies my condition AND that have at least one associated object that satisfies another condition.

Here is the criteria I tried to use to address the latter case. It does not work.

new DetachedCriteria(ParentDomainClass).build{
    associatedObjectsOfChildClass {
        eq 'someProp', 'value'
    }
    associatedObjectsOfChildClass {
        eq 'someProp', 'otherValue'
    }
}.list()

When I execute this code, only one of the association criteria is considered. I think it is not allowed to have multiple subqueries for the same association. But if that's the case, how can I restrict this further?

The following code yields the expected result, but it is not entirely achieved at database level.

new DetachedCriteria(ParentDomainClass).build{
    associatedObjectsOfChildClass {
        eq 'someProp', 'value'
    }
}.list().intersect(new DetachedCriteria(ParentDomainClass).build{
    associatedObjectsOfChildClass {
        eq 'someProp', 'other_value'
}}.list())

Here is a solution in native MySQL:

select * from parent_object
where id in(
    select parent_id from child_object
    where some_prop = "value"
)
and id in (
    select parent_id from child_object
    where some_prop = "other_value"
)

How do I achieve that using only criteria?

Upvotes: 0

Views: 1635

Answers (1)

Uday
Uday

Reputation: 629

Check the sql query logs for this. You can log the query by adding logSql=true in dataSource.groovy or set this property in yml file for Grails 3.

Have you tried 

Account.createCriteria().list{
    associatedObjectsOfChildClass {
        eq 'someProp', 'value'
        eq 'otherProp', 'otherValue'
    }
}

Upvotes: 1

Related Questions