ORM: What about "WHERE" and "ORDER BY"?

The purpose of a "where" clause is to filter the returned recordset based on field values. The purpose of an "order by" clause is to affect the order in which they are returned. When you want to be able to describe your DAO interface “properly” (i.e. without regard to whether the filter or order by clauses relate to persisted fields), a little extra work is required.

If I want to order by FirstName, all I need to do is pick up the table name and alias. In the database the FirstName attribute might be persisted as tbl_AdminUser.FirstName or tbl_User.Horribly_long_and_bizarre_Name_for_firstname_here. To do this I need to replace |space|Attribute_Name|space| with |space|Attribute_TableSQLName.Attribute_FieldName |space|. That is pretty straightforward with just the minor inconvenience that operators such as LIKE become reserved words so you can’t have an attribute name called like. Annoying, but acceptable to me.

It gets a little more interesting if you try to filter by a calculated attribute as the database is fundamentally incapable of filtering or calculating based on attributes that are calculated within the model unless there is a definitive mapping (ORDER BY Age == ORDER BY DateofBirth DESC). I don’t think there will be such a mapping most of the time, so I’m not going to support that feature from day 1. What does that leave us with? Well it means that as well as returning our query, the DAO is also going to have to return any outstanding filters or order bys which will need to be handled by the service layer using a query of queries against the calculated values provided by the business object.

I KNOW how badly this could perform. If I wanted to filter products by price and price was calculated using complex calculations, I’d have to return all products and Query of Query (QoQ) the calculated prices generated by the IBO to get the appropriate subset of products. I know this is going to need pretty smart caching mechanisms down the line to support acceptable performance, but I have real use cases where a user just wants to order or filter a small number of records based on a calculated attribute so there is really no other alternative other than putting all calculations into the db which I’m not comfortable with.

Of course, all of this will be manually overloadable with hand written code where required, but the whole point of this system is to reduce the number of edge cases where I actually have to go write code. As with most of the patterns I use, potentially dangerous, but it has a valid set of use cases. In practice, I’ll probably write a “feasibility analyzer” that analyzers the feasibility of the metadata entered to describe a given method along with estimates of how often it’d change and how big the recordset would be to determine whether generated code would be acceptably performant and what caching mechanisms to automatically deploy. Don’t hold your breath for that code – it’ll probably be Q1 2007 by the time I’m into such subtleties!