The ultimate find by method for ORM: Part II

In part I I took a look at a basic implementation of a dynamic find by method. Tony Nelson was quick to point out that this would not work if you had entity names that contained AND such as Candidate. I went back to the drawing bored and with a nice idea from Tony I cam up with another solution. His example was a little more dynamic ubt please understand this is a stand alone example for a reason. This functionality is going to be created in my abstract service layer. Anything that is going to be common among all service objects should not be repeated.

So how are we going to fix this problem with the AND keyword. The AND join is going to tell us that we need to find rows by 2 different fields. This could be improved upon to handle many ands but I really don't see myself ever needing more than 2 lookups for a find by method. If we know what entity we are working with we can do a lookup for the properties of that entity. In the hard coded example case we know we are working with the Art entity so we can do the following. private array function getProperties(){ var entity = "art"; var properties = ormGetSessionFactory().getClassMetaData(entity).getPropertyNames(); return properties; } Given the fact that we know the valid properties of the entity we can do the following.

1. Does any part of the missing method name after findby contain AND
2. If no - then just return that as the lone field
3. If yes - are the left and right sides of and valid properties? If so we have a join. If not we have a field name.

Remember this whole time we have been working with the dynamic find by method. The find by method is looking for exact methods. If we wanted to we could use this same logic for a search by method. These and many more methods are going to provide a constant API for talking with Entities. I will be posting my abstract service layer shortly so stay tuned. Until then here is the stand alone service component. /** * @accessors true */ component { public Service function init(){ return this; } public any function findBy(String fields,Struct args,Struct options){ var filter = {}; for(i=1; i <= listLen(arguments.fields); i++){ filter[listGetAt(arguments.fields,i)] = arguments.args[i]; } var rows = EntityLoad("art",filter,arguments.options.unique); if(options.asquery) { return entityToQuery(rows,"art"); } return rows; } public any function searchBy(String fields,Struct args,Struct options){ var sql = "from art where 1=1 "; var params = []; for(i=1; i <= listLen(arguments.fields); i++){ sql = sql & "AND " & listGetAt(arguments.fields,i) & " LIKE ?"; arrayAppend(params,arguments.args[i]); } var rows = ormExecuteQuery(sql,params); if(options.asquery) { return entityToQuery(rows,"art"); } return rows; } public any function onMissingMethod(String missingMethodName,Struct missingMethodArguments){ var name = arguments.missingMethodName; var args = arguments.missingMethodArguments; if(lcase(left(name,6)) == "findby"){ var fields = right(name,len(name)-6); var options = {asquery=false,unique=false}; if(findNoCase("ASQUERY",fields)){ options.asquery = true; fields = replaceNoCase(fields,"ASQUERY",""); } // AND JOIN fields = getJoinFields(fields); return findBy(fields,args,options); } // Search By if(lcase(left(name,8)) == "searchby"){ var fields = right(name,len(name)-8); var options = {asquery=false,unique=false}; if(findNoCase("ASQUERY",fields)){ options.asquery = true; fields = replaceNoCase(fields,"ASQUERY",""); } // AND JOIN fields = getJoinFields(fields); return searchBy(lcase(fields),args,options); } } /** * @hint I will search fields for the word AND. If I find and I will grab the left * and right side to see if they are valid properties, if they are then we have a join. * If we do not it could be that our field name has and in it such as Candidate or standard. */ private string function getJoinFields(String fields){ var fieldList = arguments.fields; if(findNoCase("AND",arguments.fields)){ // make sure the AND we found was not part of a field name var join = replaceNoCase(arguments.fields,"AND",","); var f1 = listFirst(join); var f2 = listlast(join); // if the left and right side of AND are valid properties if(isValidProperty(f1) && isValidProperty(f2)){ // we found an actual And statement and not a property fieldList = replaceNoCase(arguments.fields,"AND",",","all"); } } return fieldList; } /** * @hint I will take a prperty name and compare it against the acutally property list of the entity */ private boolean function isValidProperty(String property){ var properties = arrayToList(getProperties()); var exists = false; if(listContainsNoCase(properties,arguments.property)){ exists = true; } return exists; } /** * @hint I will return an array of the property names for this entity */ private array function getProperties(){ var entity = "art"; var properties = ormGetSessionFactory().getClassMetaData(entity).getPropertyNames(); return properties; } }