Ordering By Attributes – not Properties
But what happens when you want to order by a non-trivial calculated attribute that isn’t part of your database? For instance, let’s say price requires some complex business rules to calculate that you don’t want to push down to the database, how do you allow users to order a list by price or to filter it based on a range of prices?
To generically solve this problem, you need to add the ability to order your IBO by calculated values. There is no really efficient way to do this, but processor cycles are a small cost for a happy customer (*grin*), so I’m going to add the ability to order, filter and page through an IBO based on calculated values. It is something to be used sparingly, but something that’ll save a bunch of custom code whenever it is required.
So, how would I go about solving this problem? For the ordering and filtering, probably the easiest solution would be to load the IBO with the original recordset, loop through the IBO adding all of the calculated values to the recordset, store the extended recordset in the IBO (for future use if you’re caching your IBOs), run a Query of Queries to order and filter the extended recordset appropriately and then clear and load the IBO with the appropriately ordered and filtered recordset. As for paging, I probably need to add a couple of settable properties (page number and records per page) so first() would take you to the first record on the page and isLast() would be true when you hit the last record on the page.
I was not sure from your other posts on IBO, but does the IBO call the database to get the query information or does the query get passed to the IBO upon instantiation? I only ask because if you pass in the query to the IBO AND leave the sorting and pagination up to the IBO, you could potentially be getting a ton more data back from the database than is required. Going back to your price example, imagine *worst* case scenario where a user wants to paginate through an entire product catelog. That could be thousands of products across 100 pages of search results. The only way I can see this being done efficiently would be to either have the IBO do the database calls itself or to have some sort of service do the database call AND the sorting and what not.
Firstly, the IBO doesn't know about the database - that is what the DAO is for.
Secondly, I already have a stored procedure that handles getting back pages records from large data sets (I'd say anything more than a couple of hundred records I'd use the SP fro as it only returns the records you required).
If you really wanted to order by a calculated price for a large product catalog, firstly you'd have to go get a dedicated server as I wouldn't want to host you!!! Secondly, I'll probably cache the catalog query in an application scoped factory and use an IBO with ordering and filtering against the cached query.
The ordering, paging and sorting for the IBO are really for smaller recordsets, but it was something I had to add or there would be no way to order or filter by calculated attributes other than to put ALL of your business rules in the database - which is an option, but not one that I love.
I see what you are saying sort of... as I have said before, I just trying to take it all in for the moment and work it out. I can't wait to see the code, I am sure it will clear up a lot of what you are saying.