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
3
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 either
ANDorORlogic. 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.