I tried asking this question first on StakOverflow in a more concrete manner, but after being pointed here I realized I should rephrase it in more general terms; however, you can still review the original question, if you want more concrete detail about my specific use case.
Assume a relatively complex reporting interface. For the sake of simplicity, assume we're presenting the resulting data in tabular form only, but we're displaying an aggregate which doesn't map on existing domain objects. The user can select start/end dates from a calendar, can limit their report by specific criteria, and can order the results by specific columns; all of these are optional. Their request is processed by several layers of code, but after processing we end up with a single SQL query that contains filtering conditions, sorting directives, and JOIN statements in order to aggregate data from several tables.
On the database end I'm using an ORM (NHibernate) which handles persistence and coerces me to implement strict entities/domain objects; that's clear and works fine already.
At the other end (towards the user), I have the view which presents stuff to the user, and a controller that interprets in/out data. That's also clear, and it's properly handled.
My question is this: what should happen between those layers? What's the recommended approach towards proper encapsulation? My intuitive approach was to allow my business logic (BL) to call various query helpers that gradually build various parts of the query object, depending on the user's input. That started raising red flags when I ended up having to handle JOINs conditionally in the BL, which is obviously off kilter.
I agree with Frédéric's answer to my previous question on StackOverflow: move all code that builds the query deeper, closer to the persistence layer, and keep the BL free of any knowledge about the model. That's certainly a cleaner approach, and I could easily define a Data Transfer Object (DTO) to carry the database results "upwards", from the persistence level towards the controller, and ultimately to the view layer. However, the user can fill in a lot of optional input; that means I'll also need an auxiliary class carrying data the other way around, from the controller layer, through the service level where the BL lives, and all the way down to the query helpers themselves on the persistence layer. I don't know what these are called, so let's call them "QTOs", for "Query Transfer Objects" ("query" in the sense of "user queries", not in the sense of SQL). The persistence level would therefore interpret these QTOs into SQL, it would execute the SQL, and it would convert the result set into DTOs that bubble back up.
My problem with this approach is that I'd just be adding a couple of intermediary classes for data transport, but the handling itself would be just the same (only on a different layer). I'd still need to add all of those JOINs conditionally, add criteria conditionally, add sorting directives conditionally – only now I'd have to put in the extra effort of filling in QTOs conditionally in the controller as well! And future refactoring wouldn't be any easier: any changes in the QTO structure would affect both the controller and the model layers – and it's almost guaranteed that any relevant changes to the database structure would result in changes to both the QTOs and the DTOs as well.
In your experience, what is a good way of designing this? Am I over-engineering it? How are my complexity concerns addressed in the design you're using/suggesting? Or are there benefits I'm not seeing that counterbalance the added complexity?