query builder

Posted by Marian Edu on 05-Oct-2015 12:30

trying to come up with some kind of query builder that can help calling select/update/delete queries looking at a fluent syntax what would you guys use to build the filter/where clause?

what I’m contemplating right now is something along the line of…

query.each(‘customer').where(‘custnum', 10) -> simple query, eq operator as default (each customer where custnum = 10) 

query.each(‘customer’, ’name,city,address').where(‘balance', ‘>', 100) -> simple query (each customer where balance > 100)

query.each(‘customer').where().and(‘balance', ‘>', 100).eq(’country’, ‘USA’).or(’state’, ‘AL’).eq(’state’, ‘AK’).endOr().endAnd() -> each customer where balance > 100 and country = ‘USA’ and (state = ‘AL’ or state = ‘AK’)  

does this make any sense at all? does simply passing a filter object makes for a better alternative? the only concern there is this can be seen as ‘internal’ and changing the structure later will be impossible without breaking existing code… the structure looks something like this:

“filter”: {
  'and | or': [ condition, condition, …]
  condition := { “field”: value }
               { “field”: {“op”: value}}
               | and | or 


All Replies

Posted by Håvard Danielsen on 05-Oct-2015 12:57

A fluent query builder sounds very interesting, but there should be a query model(wrapper) class that  takes the entire json info as a parameter to a method or the constructor.  

If the query model is structured according to the query syntax then it would not be internal and the interface and its consumers would not need to change if the internal json stucture changes.  One would only need to change the implementation of the JSON parsing.  

I have implemented batching, filtering and sorting for other JavaScript frameworks and they have the exact same information as the KendoUI, and could thus all use the same interface, there would just be different parser implementations.  

Personally, I would implement an interface that has all request info, including paging and sorting that can be passed to Business Entities or any data access. It may extend the query model or it may just use the query model under the covers.  

The fluent builder should ideally work against the same model/class.

Posted by Marian Edu on 05-Oct-2015 13:10

just to clarify a bit, this is indeed javascript (node.js to be more precise) and the complete query information is sent oven in a json object to be interpreted by the progress back-end but this is not about the server side... this will always use a given json format, well until we find out this might need to change :(

not only that but the whole json can be quite complicated, multiple tables, join information, order by and offset/limit thought having something like a builder would help with: hiding the json format and have a more declarative syntax




Posted by Matt Baker on 05-Oct-2015 14:04

MongoDB has something very similar to this from which you can learn how others do it.  It doesn’t support joins the way a relational database does, but it has a full set of operators for single collection types.  Their query language is JSON, so it directly translates to javascript.  For other languages like java they use a builder style wrapper around it.

Posted by Marian Edu on 06-Oct-2015 02:52

Thanks Matt, the end result looks muck like mongodb's one but was looking for a fluent interface to help building the query... finally settled to this for time being:

var f = q.filter;

var qry = q.each('customer').where(f.and(
    f.eq('name', 'toto'), 
    f.lt('balance', 100), 
        f.eq('state', 'ak')

Posted by Håvard Danielsen on 06-Oct-2015 11:34

> just to clarify a bit, this is indeed javascript (node.js to be more precise)

I see the periods now.    I think a fluent query builder would be nice in ABL too...  

This thread is closed