Data model for persisting queries to database

Question

I have been asked to build what is essentially a query builder for a reporting application.

The variety of objects to query, potential modifiers, number of conditions, and so forth to be reported on have made me conclude that a query builder would be the best way to go about this task.

I am trying to decide the data model to back the storage of query parameters.

I have seen the models in this SO question as well as in this tutorial. But since it feels especially important to pick a good model up front, I would really appreciate any input.

EDIT: Here are some facts about the technology and requirements

• I am working with C# and .NET Framework 4.5 (using ASP.NET MVC for presentation)
• I am using Oracle Database 10g
• I have Dapper and LinqToDB for Oracle available for data access

I would like to design a schema that can accommodate the following:

• Allows for queries to be stored in terms of their constituent parts, where certain parts (like constraints/predicates) may be hard-coded as actual SQL or SQL snippets in the case of particularly complex logic that should be encapsulated
• Allows for constraints to be formed in a "group", "constraint" format, where a query has a single root group, each group is equipped with AND/OR, constraints belong to groups, and groups can have subgroups
• Minimizes need to explicitly define joins as part of query (so that they can be inferred by the subjects of constraints or minimally identified where necessary)
• Can store the appropriate editor and source of editor values if appropriate for a given field

Answer 1

The greatest complexity for query builders comes out of trying to handle the scope of ANDs an ORs. If you can handle these elegantly, your model can be simpler. I recommend having two sorts of predicates:

• Your vanilla predicate: value1 operator value2.

• Your compound predicated (or what we might call "implied parentheses"). The whole compound is tagged as using eitherAND or OR logic. This reduces it to a simple list of predicates (unlimited in count) plus a single binary logic tag.

These two predicates offer a polymorphic interface (e.g. IEvaluate). The first is evaluated normally and returns a boolean. The latter is reduced (e.g. you fold over the list) also to a boolean. This would be recursive since we might have nested compounds.

Using this model, at the highest level you will have either a single predicate or a single compound predicate. Of course, the compound predicate may nest to any depth. This is what your Knockout.js example already illustrates.

By doing away with the option of constructing a compound predicate that places any combination of ANDs and ORs between the various predicates, you simplify things quite a bit and lose nothing. You are able to express whatever criteria is necessary. This will entirely eliminate your concept of Lefts and Rights which I think is a good thing.