Gorm with Join Tables

[Eric Berry]

Hey all,

I am trying to figure out how to perform a query on join tables and get the sum.  Here's my working SQL that I can run in postgres directly:

select sum(bl.amount_earned) from buyer_log as bl, submission as s where s.id = bl.submission_id and s.lead_id = XXX

I am trying to set up a method in the Lead domain class that will get this information for the specific lead. I am not able to figure out how to do projections for associated tables using criteria builder, and when I try the following, it gives me the following error:

Submission.executeQuery( "select sum(bl.amount_earned) from BuyerLog as bl, Submission as s where s.id = bl.submission.id and s.lead.id = :id", [id: this.id] )[0]

Caused by: org.springframework.orm.hibernate3.HibernateQueryException: could not resolve property: amount_earned of: com.scs.cwf.BuyerLog [select sum(bl.amount_earned) from com.scs.cwf.BuyerLog as bl, com.scs.cwf.Submission as s where s.id = bl.submission.id and s.lead.id = :id]; nested exception is org.hibernate.QueryException: could not resolve property: amount_earned of: com.scs.cwf.BuyerLog [select sum(bl.amount_earned) from com.scs.cwf.BuyerLog as bl, com.scs.cwf.Submission as s where s.id = bl.submission.id and s.lead.id = :id]

Any help would be greatly appreciated!

[Eric Berry]

My question was answered by schmolly159 in the grails irc.  Here's what my code ended up being:

def ret = BuyerLog.withCriteria {

    submission {

        eq("lead", this)

    }

    projections {

        sum("amountEarned")

    }

}

if (ret) {

return ret[0]

} else {

return 0.0

}

Very fast and works great :D

--
Utah Groovy & Grails Users Group
ug...@googlegroups.com
http://groups.google.com/group/uggug/
- All meeting times and places can be found here.
 
** Please prefix your subject with "[JOB]" if your message is about job opportunities.