2

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
  • Who will be creating the queries? How will they be evaluated - does the query need to eventually be expressed in pure SQL or is it up to you? What technologies are you using to implement this (some environments make some techniques easier or harder)? – psr Sep 15 '14 at 22:01
  • @psr, some of your questions answered... – tacos_tacos_tacos Sep 15 '14 at 23:14
  • 1
    Hmm, what are your UI requirements and target audience? A simple DSL for ANDs and ORs can be easier than a visual query builder (or a fancier interface can do both and have one update the other). Also, it's hard to tell if you are better off storing the query by constituent parts or just saving and parsing a string. Do you need to use SQL to query your saved queries? – psr Sep 16 '14 at 0:18
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.