[coldbox 3.8.0 / cf10] Working with Associated Models with Coldbox ORM and Criteria Builder.

Communities ColdBox HMVC
1

I keep running into this problem and resorting to OrmExecuteQuery() or some other hack, but figure I must be doing this wrong so need to get it right.

If (for example), I have a model called “CalendarEvent”, which has an association “Registrations”.

`

property name=“Registrations” singularname=“Registration” fieldtype=“one-to-many” cfc=“EventRegistration” fkcolumn=“eventID” orderby=“tstamp DESC” ;

`

Here is the EventRegsitration CFC

`

component persistent=“true” table=“EventRegistration” hint=“Calendar Event Registrations” extends=“coldbox.system.orm.hibernate.ActiveEntity”{

property name=“id” fieldtype=“id” generator=“native”;

property name=“tstamp” ormtype=“timestamp” ;

property name=“pmtStatus” ormtype=“string” length=“10” ;

property name=“pmtMethod” ormtype=“string” lenth=“10”;

property name=“pmtAmount” ormtype=“float” scale=“2”

property name=“pmtRate” ormtype=“string” length=“20”;

property name=“regStatus” ormtype=“int”;

property name=“invoiceID” ormType=“integer”;

property name=“profile” fieldtype=“many-to-one” cfc=“Profile” fkcolumn=“profileID”;

property name=“event” fieldtype=“many-to-one” cfc=“CalendarEvent” fkcolumn=“eventID”;

}

`

So then I can get a calendar event with a number of registrations associated with it.

How am I best to work with an events registrations? For example, I want to calculate the following for an event:

  1. How many registrations with regStatus of 1
  2. The sum of the pmtAmount column where regStatus = 1

Do I need to get the registrations with this.getRegistrations() and then start working on them, or is there a way I can do this in a traditinal SQL Sense using Coldbox ORM? (which is what I would like to do)

The main problem is, that I can’t run any HQL on the registrations … “WHERE eventID = ??”, because event is an association, so no property eventID.

I’ve been playing with the CriteriaBuilder and figure this is where I need to be as it will let me do what I need to do, and have this so far (in my Event Model):

`

public function registrationCountByStatus(required status){

var reg = orm.newCriteria(“EventRegistration”);

return reg.eq(‘regStatus’,arguments.status).count()

}

`

Note that I injected ORM into my model, even thought it is already extending ActiveEntity, because I found that if I worked with CriteriaBuilder in my CalendarEvent Model, I got an error as it was trying to work on the CalendarEvent model.

I think I am on the right path, but seem to be hitting walls (strange errors), so figured I must be doing something wrong.

Can anyone suggest (if you can make sense of this longwinded post) if I am on the right track, or should I be looking somewhere totally different.

Thanks So much… even if you have just read this far… appreciated :slight_smile:

Jason

2

I might not understand what you are fully asking, but one thing I can say is that if you can’t write it in HQL I am almost 100% certain you will not be able to do this with criteria.

3

If I’m following you, it seems like you’d like to do something like so in SQL:

select
( select count( * ) from eventRegistrations where eventID = ? and regStatus = 1 ) as regCount,
( select sum( pmtAmount ) from eventRegistrations where eventID = ? and regStatus = 1 ) as regSum
from event
where eventID = ?

If so, you might check out the DetachedCriteriaBuilder that’s built into CriteriaBuilder. It’ll let you create subquery projections that will produce SQL as above.

4

Sorry, meant to include the link: http://wiki.coldbox.org/wiki/ORM:DetachedCriteriaBuilder.cfm#DetachedSQLProjection()

5

H Joel, yes… pretty much as you said, but don’t necassarily nee to nest it in that way… I guess you have confirmed for me that Criteria Builder is the way to go.

One other thing I am unsure if is how I should access Criteria Builder.

I am wanting to work in my EventsCalendar Model, which I have extending ActiveEntity. I can access criteriaBuilder directly from within that model, but if I want to setup Criteria for Models other than the one I am in, do I need to inject the ORM plugin manually.

So at the moment, the top of my CalendarEvents model has these lines:

component persistent="true" table="Event" hint="Event" extends="coldbox.system.orm.hibernate.ActiveEntity"{ property name="orm" inject="coldbox:plugin:ORMService" ;

I then have the following method in my CalendarEvent Model:

`
public function registrationCountByStatus(required status){

var reg = orm.newCriteria(“EventRegistration”);

return reg.count();

}
`

This works fine, but then if I run something like this:

return reg.eq('regStatus',arguments.status).count();

I get the following error:

Application Execution Exception`

Error Type: java.lang.ClassCastException : [N/A]
Error Messages: java.lang.String cannot be cast to java.lang.Integer

`

… which I am wondering may have something to do with the way I have injected ORM…

Any tips?

Thanks again!

6

You may need to JavaCast that call.

7

Thanks Andrew, Javacasting the value did it. after all that :frowning:

Also, yes, DetachedCriteriaBuilder is what I was after.

Thanks!

8

Yeah I saw Joel’s post, not too familiar with Criteria Builder myself, in situations like that anyway. But do be aware that there is a huge overhead in using Criteria Builder as well.

9

Hi Jason–

Glad it’s working for you. Re: the JavaCast-ing, if you’re doing it in the context of CriteriaBuilder, you can use the convenience method convertValueToJavaType(). This will do the JavaCast-ing for your, but you don’t have to figure out the correct type. Here are the docs for that: http://wiki.coldbox.org/wiki/ORM:CriteriaBuilder.cfm#Converting_Values_to_Java_Types