hibernate query - grails

Question

I'm new in grails and I'm not really familiar in hibernate. I've come up to a problem regarding hibernate query using executeQuery in grails. I just made this code similar to the original code that's having a problem:

def nonMemberList = Group.executeQuery("select gp.name from Group gp where gp.status = 'CONFIRMED_MEMBER'")
def FemaleMemberList = Group.executeQuery(""" select sum(case when g.isMale = false and g.name not in(:isNotConfirmedMember) from Group g where :startDate <= g.dateCreated and g.dateCreated < :endDate""",
[isNotConfirmedMember: nonMemberList, startDate: startDate, endDate: endDate])

Error occurs whenever the nonMemberList is null and I've found out that the list should not be null when I use the is not syntax.

I'm not really familiar in hibernate queries, that's why I'm having a problem what other hibernate query should I use that also behaves like the original code. thanks for sharing your knowledge.

nonMemberList is unlikely to be null, but it could be empty. The second query becomes invalid with an empty list, I believe you can't perform `in` operations on empty collections — tojofo, Jun 28 '12 at 00:23

score 2 · Answer 1 · answered Jun 27 '12 at 06:50

Have you tried to use Criteria? Located here: http://grails.org/doc/latest/guide/GORM.html#criteria

Please try the code below for the nonMemberList first, see if it produced null:

//non member list code
def cNonMember = Group.createCriteria()
def nonMemberList = cNonMember.list() {
    eq("name", "CONFIRMED_MEMBER")
}

Hope it helps.

score 1 · Answer 2 · answered Jun 28 '12 at 00:44

Ok it seems to me that what you're after is the female members, if you have no need for the nonMember list separately then you can just get the list of females using something like;

def c = Group.createCriteria()
def females = c.list {
    le('startDate', startDate)
    lt('dateCreated', endDate)
    and {
       ne('status', 'CONFIRMED_MEMBER')
       eq('isMale', false)
    }
}

You seem to have an incomplete projection in your example too, if you wanted just the count of female members or the sum of some property you should be able to add that to the above.

score 0 · Answer 3 · answered Jun 27 '12 at 06:59

I'm not sure about the grails syntax, but I think you can try

select sum(case when g.isMale = false " + (nonMemberList == null ? "" : "and g.name not in(:isNotConfirmedMember)") + " from Group g where :startDate <= g.dateCreated and g.dateCreated < :endDate

score 0 · Answer 4 · answered Jun 27 '12 at 13:41

0

def nonMemberList = Group.withCriteria {
    eq('status', 'CONFIRMED_MEMBER')
}.collect { it.name }

answered Jun 27 '12 at 13:41

Dopele

547
3
16

hibernate query - grails

4 Answers4