ORM: Filtering Calculated Attributes from Filter and Order By Clauses
Parsing the Order By Clause Let’s start with order by clauses. They are pretty straightforward. An order by clause can be defined (I believe) as a comma delimited list of 1..n clauses each of which have a single field name followed by an options asc/desc qualifier. So, if I just loop through the comma delimited list, treat each record as a list of space delimited words and select the first word, if it is a calculated attribute, I add that clause (including its optional qualifier if it is there) to the “OutstandingOrderBy” list to be returned to the service layer for processing as part of a query of queries. If not, I add it to the order by SQL, replacing the attribute name with the associated table SQL name and field name to provide the fully qualified order by field SQL.
Parsing the Filter Clause In general terms filter clauses could be described as 1..n “field – operator – value” clauses linked by and/or statements. Most common operators would be =, <, =<, >, >=, <>, LIKE, and IN. Of course, it is also possible to write things like (field1 AND field2) = value (although this could obviously be rewritten less succinctly but more parsably as field1 = value AND field2 = value), so I’m going to have to simplify the way that filters are passed into the DAO so that it can successfully parse out any calculated attribute filters while still leaving valid SQL to pass to the db.
I’m really not very good at that kind of coding, so I’m just going to move on with the rest of the ORM without this feature (it’ll just break if you try to filter by a calculated attribute). I’ll speak to some people who are better at Reg-ex parsing than I am about a combinatio of a RegEx and a set of rules for formatting filter requests and I’ll drop this in when I get a chance.
If anyone has any good ideas they’d be much appreciated!
One of the concerns I havce always had about ORMs is that they can only handle relatively simple DB intearaction. (CRUD basically) once you start looking at WHERE, ORDER, GROUP BY, not to mention correlated subqueries with related aggregate fucntions, then these are always going to have to individually crafted. So I would always make the decision to leave these as a whole to individual gateway methods. Having made this decision, I would then be wondering at what point does a query become too complex to be handled ny an ORM. Even inserts and updates of single objects with one-to-many relationships are not always easy. Take for example a form where a user can select options via checkboxes. If an option is selected, then we need have a child relationship. Now copnsider the update situation where someone deselects an option - then the child record needs deleted. How will a blackbox ORM handle this (pretty common) situation? We need obviously to look at what has changed in the relationships. (Normally i delete all and reinsert all)
I tend to agree that generic ORMs only solve a small subset of SQL problems. I have a fairly cohesive set of SQL problems I need to solve. Some of them are traditional ORM issues, others are outside the remit of what an ORM would usually handle, but as I need to be able to generate rather than write my code I need to provide coverage for common patterns whether or not they would usually be included in an ORM.
For example, I need the ability for users to ordre and filter by object attributes irrespective of whether they are persisted in the db or not to provide a generic solution which means I need to support both DB filter/order and an in-object QoQ library to handle ordering or filtering by calculated properties by iterating through a recordset using an IBO to create a new query as if all the attributes were persistent and then QoQing that.
I also need the CategoryTitle,ProductCount kind of aggregate joins to be auto-generatable.
You raise a really good point with the comma delimited list to child object and it is on my list of issues to address. I need to think through the different use cases to come up with a realsonably performant general algorithm for that class of problems.
In general terms yu need to distinguish between use cases where the child object is "owned" by the parent (which affects whether the child should be deleted or just de-associated on being unchecked). You also need to distinguish between cases where the object is just the value of the check box, where the object has additional properties, where there is a simple joining table and where the joining table has additional join properties. If you can cover those combinations of cases, I think that'd pick up a lot of the use cases I usually have to deal with. Look for sample code later in the week!
It seems to me that what initially (I assume) was intended to be a way to relieve developers of writing repetitive CRUD activities is becoming something which may in fact endanger something we are all aiming for - encapsulation, decoupling, blah blah. The more complex the ORM becomes, the more likely that the entire implementation becomes brittle.
Better leave a lot of the ordering and filtering elsewhere in the application
In fact i do a lot of this stuff in the view part of may apps (shock horror). As far as I am concerned the business object/service returns a record set. If it is filtered/sorted because of intrinsic business rules, then it will be, but often these choices can be left to the presentation of data. If a user asks for a recordset to be ordered by date is that the role of the business component? Not necessarily
I think you raise a really important point. Such an important point it rated another posting!
http://www.pbell.com/index.cfm/2006/10/16/Where-th...
Best Wishes,
Peter