Grails filter domain class by property of another domain

Question

I have a Grails application and want to create filters for my domain class using named query.
I have domains Act and Status, StatusName is an Enum:

class Act {
    static hasMany = [status : Status]
}

class Status {
    Date setDate
    StatusName name
    static belongsTo = [act : Act]
}

I want to filter Acts which have their most recent Status's name equal to specific name.
For now I have this code in Act:

static namedQueries = {
    filterOnStatus { StatusName s ->
        status {
            order('setDate', 'desc')
            eq 'name', s
            // I need only first Status, with most recent setDate
            // among all Statuses of that Act
        }
    }
}

But this filter all Acts that have Status with specific name, not only with most recent. I tried to place maxResult 1 in query, but it seems not to work.
Any help would be appreciated.

EDIT: Problem was solved that way:

filteronStatus {
    createAlias('status', 's1')
    eq 's1.name', s
    eq 's1.setDate', new DetachedCriteria(Status).build {
        projections {
            max('setDate')
            eqProperty('act', 's1.act')
        }
    }
}

What do you mean by "the most recent" ? You need to add date to the arguments of named query. If `setDate` is more then that date - the status is in your "most recent" range. — rxn1d, Oct 02 '13 at 07:36
`Act` has several statuses. Each `Status` has a date (`setDate`) when it was set. Most recent status mean `Status` with most recent `setDate` among all statuses. — TulaGingerbread, Oct 02 '13 at 07:40

Olencha · Accepted Answer · 2013-10-03T07:15:55.250

1

see 'namedQueries' from Grails Doc

// get a single recent Act

def recentAct = Act.filterOnStatus(statusName).get()

ADD:

HQL

"select s1.act from Status as s1 \
            where s1.name = :statusName \
            and s1.setDate = (select max(s0.setDate) from s1.act.status s0)"

NamedQuery

listByStatus { statusName ->
            createAlias('status', 's1')
            eq 's1.name', statusName
            eq 's1.setDate', new DetachedCriteria(Status).build{ projections { max('setDate')} eqProperty('act','s1.act') }
        }

edited Oct 03 '13 at 07:15

answered Oct 02 '13 at 06:51

Olencha

418
2
11

I don't need one recent Act, I need all Acts which satisfy criteria 'most recent Status of this Act has StatusName equal to specified'. The code you offer give me one Act with most recent Status of all. – TulaGingerbread Oct 02 '13 at 07:10
def recentAct = Act.filterOnStatus(statusName)..listDistinct() – Olencha Oct 02 '13 at 08:15
try def recentAct = Act.filterOnStatus(statusName).listDistinct(). Anyway I suggest to use model: Act{ Status lastStatus }; – Olencha Oct 02 '13 at 08:21
Yeah, I thought about adding new field to `Act`. But I'll do it only if I'll be sure there is no other way to fix that. And `listDistinct()` is not the way to solve it – TulaGingerbread Oct 02 '13 at 09:09

Grails filter domain class by property of another domain

1 Answers1