More Criteria Builder ORM Questions...

I’m trying to do something like this:

var c = new coldbox.system.orm.hibernate.CriteriaBuilder(‘TripLogPointAward’);
c.createAlias(‘log’,‘log’);
c.add(c.restrictions.eq(“user”,user));
c.add(c.restrictions.isIn(‘log.mode’,modes));
c.add(c.restrictions.isGE(‘log.date’,startDate));
c.add(c.restrictions.isLE(‘log.date’,endDate));
points = c.withProjections(sum=“pointValue”).get();

I’m receiving this error message: java.lang.String cannot be cast to java.util.Date

java.lang.ClassCastException: java.lang.String cannot be cast to java.util.Date at org.hibernate.type.DateType.toString(DateType.java:101) at org.hibernate.type.NullableType.nullSafeToString(NullableType.java:119) at org.hibernate.type.NullableType.nullSafeSet(NullableType.java:160) at org.hibernate.type.NullableType.nullSafeSet(NullableType.java:138) at org.hibernate.loader.Loader.bindPositionalParameters(Loader.java:1769) at org.hibernate.loader.Loader.bindParameterValues(Loader.java:1740) at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1612) at org.hibernate.loader.Loader.doQuery(Loader.java:717) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:270) at org.hibernate.loader.Loader.doList(Loader.java:2294) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2172) at org.hibernate.loader.Loader.list(Loader.java:2167) at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:119) at
……

This appears to be a problem with ‘log.date’ and not the end or start dates. I tried changing the ormtype from ‘timestamp’ to ‘date’ on the Log and the error changed from TimeStampType to DateType (in the first line of the stack trace)….

This is a huge problem for me and I am at a loss on how to solve it…

Any suggestions!?

Cheers,
ben

PS: Luis, You should update the coldbox criteria builder docs as they are out of date and reference isGTE/isLTE instead of isGE/isLE… caused some frustration until I looked at the source for the restrictions class on github… thanks!

Ben,

Your not clear what startDate and endDate are, and by the error message it appears that they are strings and not date objects that the database understands. Normal SQL queries have this problem and is why createODBCDateTIme functions exist.

I am assuming that this is your problem, your are trying to send a string into the database that is requiring a date / time object.

Wow… well I was about to reply that this wasn’t the case, but then I tried a few things…. first off, here’s how my component looks: http://www.screencast.com/t/iAAR3tyt13

What’s odd is that, despite the fact that the property is set as “date”, when making the assignment either via the default attribute or manually by setting startDate = “01/01/2010” or something similar, it’s being treated as a string by CF within the context of the criteria.

I was able to fix it by specifying the value via createDate(2010,1,1)…

odd… any clue why this might be? is there any way I can make that assignment without having to manually create a date through a function like createDate???

Thanks for the prompt reply Andrew!

To be honest Ben I don’t think so… Due to ColdFusion treating everything as strings, we need to convert to a certain format for other things to work, and dtabases are just one example of this.

You need to remember that even though you set date as a property on the ORM entity, this is indeed creating a date field in the database, so if it is not converted to a date string it will throw that error message.

So I guess until ColdFusion moves away from being typeless, which will never happen, I guess the answer is no. At least not that I am aware off.

Oh well, thanks a ton for the help. Good to remember these gotchas about CF being typeless/dynamic

Yep, even the most seasonal can get tripped up.

One more question…

I want to make a similar query except, instead of restricting it to the user, I want to restrict it to a specific group.

So, my user class has a many-to-many relationship called ‘groups’.

I’m a bit at a loss as to how to approach this… any ideas?

if I were to be able to create functionality in hibernate, it would be something like isIn(‘user.groups’,group)… but obviously that won’t work for a long list of reasons

Thanks a TON for the help everyone.

-Ben

Wow, that was easier than I expected…

Just as a reference to someone who wants to do some calculations on data through many levels of association, I’m sending my solution back to the list…

So, just as before, I’m using criteria builder.

It turns out it’s much easier to get from Group->User->Logged Trips or Group->User->PointAwards than the other way around. So, I do:


		var c = new coldbox.system.orm.hibernate.CriteriaBuilder('Group');
		c.createAlias('members','u');
		c.createAlias('u.pointAwards','p');
		c.createAlias('p.log','l');
		c.add(c.restrictions.eq('id',group.getID()));
		c.add(c.restrictions.isIn('l.mode',modes));
	 	c.add(c.restrictions.isGE('l.date',startDate));
	 	c.add(c.restrictions.isLE('l.date',endDate));
		points = c.withProjections(sum="p.pointValue").get();

The way these stats were being calculated before was INCREDIBLY inefficient with loops inside loops inside loops and was killing our server with 99% cpu for upwards of 5 seconds to calculate the stats for each individual and their associated groups. Now, I'm looking at a value closer to 500ms at 20% cpu. 

I'm still playing around with an enhanced caching strategy, but overall I think I'm going to be able to increase performance of this chunk of functionality around 900 fold once caching is in place.

My old code had this comment above my stats calculations: 

	// todo: fix this entire workflow for scaleability... right now this scales exponentially.. SO BAD!

I’m so happy that this performance upgrade was as direct as it was. Thanks to Louis for wrapping the Hibernate functionality so neatly. Without your efforts and Coldbox, I’m sure I would be pulling my hair out to write a ton of unmaintainable SQL.

Cheers!
Ben

Actually Criteria just builds HQL, so if you wanted to write in HQL you would get a better performance overall, probably not by much depending on how many times that is run.

But from a readability point of view seeing it mapped out like this, makes it easier to piece together and understand.

Hi Ben

parseDateTime does a nice job to quickly convert the date string.

Markus