Encapsulation for complex queries

Question

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?

Answer 1

You are wise to be wary of over-engineering things. Still, it does sound like your "complex queries" problem context might be a good match for investing in the interpreter pattern, in order to help you abstract (in your BL) the use of your persistence layer, for querying purposes?

Then the question becomes "an interpreter for which query language?" So, I would go back to "stare at" the shape of the various JOINs you would end up writing in your BL with the first approach that you mentioned, and I would try to devise a simple DSL that could be the source query language over your domain for the most frequent/common query patterns.

Alternatively (since you mentioned NHibernate, with a "N") there is also the option of implementing your own QueryProvider if you find Linq's comprehension syntax powerful enough to build those queries.

Answer 2

Here's one possible design:

I would compose it down to 2 areas:

• Mapping
• Query Building

Mapping would handle BL/DTO to DB object mapping, relation building etc., for both in and out. Note that for IN you will need mapping and building, and for OUT (return) you will just need mapping as no query will be built on the round trip/

Query building would handle the previously mapped objects to build a SQL query. A sql query is composed of parts so you could break this down as well:

• Build SELECT
• Build FROM
• Build WHERE
• Build GROUP BY
• Build ORDER BY
• etc...

One could flatten this out into one layer, but I think you want an area of abstraction between the business objects (DTOs) and database (mapping layer).

Then one could test the building of the query separately from the mapping layer. Also, if there is a mapping change, this should be handled in the mapping layer and then the building layer should seamlessly handle the change since it is just building the query from the previously mapped objects.

For example, say I added another join table in the database. The mapping layer should handle this which would automatically handle another join condition. The actual building of the join should be no different, but instead of 1 join condition, now I have 2.

For each (Condition in JoinConditions)....

This also makes each layer testable as well as well as each layer having single responsibility.

I would warn that I have seen of few of these and they become inherently complex. Start out with some common operations and start with some simple logic first before building a more complex/abstract one.

Answer 3

You need basically a query builder mechanism or a mapper from your UI Layer to the DB.

Since you're using a great ORM already, you will only need to build mapping part, NHibernate will take care of query building.

For the mapping part, you need to get all of the UI specific parameters, and put your query builder logic, finally return results. On any change to the UI you need to run this from scratch.

For the query building part, I recommend you to read this article about NHibernate QueryOver Query Building Techniques

Edit due to comment:

UI specific code should be on UI layer, data specific code should be on a Repository or DAL layer. But how could these two layers communicate? That is the tricky part.

I often use an additional layer not only for my (DB) entities and interfaces but also models just for communication purposes, that will be common for all my projects (UI, Business, Service etc.). But consider keeping your project only (UI, Business, Service etc.) models in that particular projects.

So, create a common model in entities layer fpr UI to DAL/Repository mapping, fill it in your UI layer (I had called it mapping before), than send to the DAL/Repository layer. With this information you can build the query, execute and send back the results to the UI layer.

I hope this approach helps you.

Answer 4

Short version: there doesn't appear to exist a comprehensive, standardized design pattern and support for this, at least not in NHibernate.

Long version

I've spent the past couple of weeks investigating this on and off. After a lot of searching, I finally found a technique applicable for the technology I'm using (and for my personal preference, namely NHibernate using QueryOver) on Andrew Whitaker's blog: QueryOver Series - Part 6: Query Building Techniques.

I realize that what Andrew is describing there is more of a technique, rather than a design pattern. However, the fact that someone felt the need to address that specific topic in depth in such a short tutorial (it only has nine parts, one of which addresses this) proves this is a substantial issue, it's relatively frequently encountered, and it does indeed deserve being addressed properly both as a pattern and as a technique (perhaps in underlying libraries).

Andrew's solution is quite elegant, up until section "Aliases may need to be passed around". That's specifically the issue I had also encountered, and that prompted me to post these questions here (the one on StackOverflow and this one).

While Andrew's solution in section "Aliases may need to be passed around" certainly works, I think it's quite inelegant, and it breaks isolation big time (and that's specifically what I wanted to address).

Compare his intermediary solution (before that last section)

IList<ProductDTO> products = session.QueryOver<Product>()
    .ApplyColorFilter(colors)
    .ApplyRatingFilter(minimumRating)
    .SelectList(list => list
        .Select(pr => pr.Name).WithAlias(() => result.Name)
        .Select(pr => pr.Color).WithAlias(() => result.Color)
        .Select(pr => pr.ListPrice).WithAlias(() => result.ListPrice))
    .TransformUsing(Transformers.AliasToBean<ProductDTO>())
    .List<ProductDTO>();

return products;

to his final solution (described in that section)

ProductReview reviewAlias = null;

var query = session.QueryOver<Product>()
    .JoinAlias(pr => pr.Reviews, () => reviewAlias);

FilterQueryByRating(query, () => reviewAlias);

query
    .SelectList(list => list
        .SelectGroup(pr => pr.Id)
        .SelectMax(() => reviewAlias.Rating))
    .List<object[]>();

While the initial approach is indeed limited and needs to be addressed (explanations in his post), it's obviously much more elegant, readable, maintainable, and way better isolated than his final solution.

Since there doesn't appear to be any better solution to this issue in NHibernate, which is relatively widely used and is being actively developed, I can only conclude there really is no clean solution at this point. This is surprising to me, given that we're talking about dynamically generated queries, something people have been struggling with for at least a decade.

If any better answers pop up then I will gladly update the accepted answer at that point (it would serve me as well, after all – and it would certainly be better than this answer).