Just wanted to note that I ran into an issue using the ColdBox
entityService, specifically when calling findWhere and trying to
filter on a column name "entityName". entityName is excluded from the
query builder and the filter never worked.
I'd suggest changing the API to separate the internal arguments from
the parameters of the query to avoid this. Something like
ormService.findWhere( entityName="items", criteria={ entityName =
"posts" } );
.brett
yes, good point, I made a ticket for this, and already started updating the services, plus criteria is more descriptive anyways, thanks for the tips!!
Luis F. Majano
President
Ortus Solutions, Corp
ColdBox Platform: http://www.coldbox.org
Linked In: http://www.linkedin.com/pub/3/731/483
Blog: http://www.luismajano.com
IECFUG Manager: http://www.iecfug.com
Does anyone have a better word then criteria? This is super picky, but I hate typing that word and all its vowels 
Where is easier to type, but might be confusing? Thoughts?
Curt Gratz
Computer Know How
How about Filters? Maybe just "Where" and have an uber "find"
function that takes in different combinations of arguments to form the
query. list() already uses the criteria argument so hopefully what is
used is consistent in all the functions.
Here are some other thoughts about BaseORMService.cfc
+ What is the difference between findIt() and findAll()
+ findAll() and list() seem to be very similar too
+ How does one use the OR and IN WHERE operators?
+ getAll() executes a entityLoadByPK for each ID in the array. Is
this for performance reasons since certain entities might be already
cached in the ORM session?
+ deleteByID() - would be cool if you could pass in an array of ids.
+ I wish there was an interception point called in save(). I ran
into some issues when using the ORM handlers when inserting and
updating entities. (I couldn't use ORMPreInsert to set an assigned
key, cf would error compaining of the missing ID before the event was
called ). This would also be useful for adding functionality outside
of the transaction (for example - checking to make sure the entity is
valid before attempting to save it).
+ Would be nice if there function(s) to retrieve metadata about the
entity. For example a getProperty( propertyName ) function that
returns the metadata for a named property. Another one to return a
structure of all the property metadata for an entity flattened.
Here is the function I'm using to get the metadata for a named
property:
function getProperty( required any object, required string name )
{
for ( var md = getMetaData ( arguments.object );
structKeyExists( md, "extends"); md = md.extends)
{
if ( structKeyExists( md, "properties") )
{
for ( var idx = 1; idx <= arrayLen ( md.properties ); idx++)
{
if ( md.properties[ idx ].name == arguments.name ) {
return md.properties[ idx ];
}
}
}
}
throw( message="Property not found", detail="The property
#arguments.name# was not found on #getMetaData
( attributes.object ).name#" );
}
.brett
Answers below:
How about Filters? Maybe just “Where” and have an uber “find”
function that takes in different combinations of arguments to form the
query. list() already uses the criteria argument so hopefully what is
used is consistent in all the functions.Here are some other thoughts about BaseORMService.cfc
- What is the difference between findIt() and findAll()
find() always returns 1 entity
findAll() always returns an array of entities
- findAll() and list() seem to be very similar too
list() returns queries by default and is powered by entityLoad(),
findAll() is powered by HQL queries, so it is much more powerful
- How does one use the OR and IN WHERE operators?
I don’t think the current HQL implementation in CF allows for IN operators, as for OR, that can only be used on HQL and not on entityLoad() does not support OR’s just ANDs. For those complex queries, use the executeQuery() in the base service.
- getAll() executes a entityLoadByPK for each ID in the array. Is
this for performance reasons since certain entities might be already
cached in the ORM session?
Yes, and also because you are retrieved by PK, basically doing the work for you instead of loading multiple entities.
- deleteByID() - would be cool if you could pass in an array of ids.
Interesting, trying to see if it would be better to do the array of ids as an HQL query so it is then batched by hibernate. However, I have not tried the IN clauses yet. Maybe Curt can shed some light here. So if we do this:
deleteByID(“User”, [1,2,3]);
Then if the ID is an array, we would execute an hql query like: “delete from User where PK IN (1,2,3)”
Because if we just iterate over the array and do: entityLoad() and then delete(), it could be potentially slow since you are doing 1 query to load the entity, and then delete it. However, if we do it via HQL it is just 1 query but we need the PK, which is trivial to get from hibernate. Thoughts?
- I wish there was an interception point called in save(). I ran
into some issues when using the ORM handlers when inserting and
updating entities. (I couldn’t use ORMPreInsert to set an assigned
key, cf would error compaining of the missing ID before the event was
called ). This would also be useful for adding functionality outside
of the transaction (for example - checking to make sure the entity is
valid before attempting to save it).
Ok, how about creating one called, ORMPreSave() and another one ORMPostSave(). The preSave occurs before the transaction and saving, and the postSave after the commit or rollback.
Hey Luis,
> + What is the difference between findIt() and findAll()
find() always returns 1 entity
findAll() always returns an array of entities
What about findIt() ( Find It ) and findAll?
Ok, how about creating one called, ORMPreSave() and another one ORMPostSave().
I think those would be very useful.
I don't think the current HQL implementation in CF allows for IN operators,
I believe it does (I haven't tried it yet), but it does not seem
straight forward at all, which is why I was hoping there would be a
sane helper function in the ORMService.
Here is a related thread on the ORM-Dev list
http://groups.google.com/group/cf-orm-dev/browse_thread/thread/19dc48732d58fca0/1fb8e899ab7ca5e5
Brett and Luis,
You can use IN in the ColdFusion flavor of HQL, but you can’t use it with named parameters. So something like this works
<cfset users = ormExecuteQuery(“from User where userID IN (1,2,3)”)>
And something like this does not.
<cfset users = ormExecuteQuery(“from User where userID IN :userID”,{userID=“1,2,3”})>
So you could dynamically build the IN statement and concatenate it to your HQL query string if needed. Obviously this opens you up to SQL injection attacks if you don’t first cleanse the input.
See this article by Jason Dean discussing this topic.
http://www.12robots.com/index.cfm/2009/11/19/ORM-Hibernate-Injection–Security-Series-14
Curt Gratz
Computer Know How
For anyone wanting to use "in" another option that works was given by
Barney and posted by Bob Silverberg in this discussion.
http://groups.google.com/group/cf-orm-dev/browse_thread/thread/19dc48732d58fca0?pli=1
Look for this:
<cffunction name="ormList" output="false">
<cfargument name="list" />
<cfargument name="type" default="int" />
<cfset var result = [] />
<cfset var i = "" />
<cfloop list="#list#" index="i">
<cfset arrayAppend(result, javaCast(type, i)) />
</cfloop>
<cfreturn result.toArray() />
</cffunction>
Here it is in action:
<cfset images = ormGetSession().createQuery('from Image where id in
(:id)') />
<cfset images.setParameterList('id',
ormList(session.filters.imageId)) />
<cfset images = images.list() />
I couldn't figure out how to reply to the group. Oh well.
- Gabriel
Actually, I already solved the in dilimea. I talk to the session directly and use the createQuery() and build it out using direct query.
This is already in place on svn, with some cool additions. Also, added some cool DLM stuff and the deleteByID() with an array also. Check it out.
Luis F. Majano
President
Ortus Solutions, Corp
ColdBox Platform: http://www.coldbox.org
Linked In: http://www.linkedin.com/pub/3/731/483
Blog: http://www.luismajano.com
IECFUG Manager: http://www.iecfug.com
Thanks.
More feedback:
+ baseORMService.exists() returns TRUE when the entity DOES NOT
exists, and FALSE when it exists. Shouldn't this be the opposite?
+ In baseORMService.get() if you pass in 0 for the ID the function
will return a new instance of the entity. I use guids for my ids and
passing in 0 feels dirty. Could this also check for an empty ID
string when determining if a new entity should be returned?
.brett
Thanks!!
Updates done/
Luis F. Majano
President
Ortus Solutions, Corp
ColdBox Platform: http://www.coldbox.org
Linked In: http://www.linkedin.com/pub/3/731/483
Blog: http://www.luismajano.com
IECFUG Manager: http://www.iecfug.com