Implementation of Dynamic search conditions for asp.net web pages

Scenario

Recently, we have a requirement to insert various types of data into mongodb, which is a database that records business logs.
Because the business object types are different, the format of the inserted data is also completely different.
In addition, you also need to provide a query interface to search for data.
There is no problem with inserting data, but the query is...

Query Design Scheme

First, let users directly enter the mongodb query syntax, similar to the json format. However, although the user is also a developer, they are not familiar with this syntax, so they gave up.
The second idea is to allow the user to input SQL statements and then convert them... The result ends with a failure.
Finally, we can see the Interactive Design of the iTunes smart playback list:

Here, you can insert a condition or a set of conditions (equivalent to inserting a bracket, which contains many conditions ).

The expression in the figure can be considered: score> 3 & Type = "Music" & Author = "" & (Author = "" & Author = "" & Author = "")

That is to say, such interaction can fully implement various nested logic.

Data Structure
To design such a structure, you must first think about the data structure.

After analysis, I think there are actually two types: one can be considered as QueryGroup and the other can be considered as QueryItem.

The Code is as follows:Copy codeThe Code is as follows: public class QueryGroup
{
Public GroupType {get; set ;}
Public List <QueryItem> Items {get; set ;}
Public List <QueryGroup> Groups {get; set ;}
}

Public class QueryItem
{
Public string Name {get; set ;}
Public QuerySymbol OperatorType {get; set ;}
Public string Value {get; set ;}
Public DataType ValueType {get; set ;}
}

QueryGroup contains a set of query conditions And a group of subquerygroups. In addition, it has an important property GroupType, which indicates whether the logical relationship of the group of data is And Or. That is, the "any" and "any" options in the above interface.

The Internal Attributes of QueryItem are field names and logical operation types (equal to, not equal to, greater ...) , And attribute type (integer, text ...).

There are several difficulties after designing the data structure:

1. How to Design frontend interaction?
2. How do I transmit data to the backend?
3. How can I convert the data obtained by the backend to a query expression?
Let's take one of them!

Frontend design Interaction

Bootstrap is used here, and the interface is very nice!

Let's take a look at the front-end design scheme. The above are dynamic conditions, and below are some fixed conditions.

The structure here is consistent with the data structure above. html is divided into two types: QueryGroup and QueryItem.

Put them in two hidden divs as templates.

The Code is as follows:Copy codeThe Code is as follows: <div style = "display: none;">
<Div class = "query-group-template">
<Div class = "query-group well">
<Div class = "query-title">
<Span class = "help-inline"> match the following </span>
<Select class = "input-small group-type">
<Option value = "1"> all </option>
<Option value = "2"> Any </option>
</Select>
<Span class = "help-inline"> rule: </span>
<Button type = "button" class = "btn-mini btn-success add-query-item" title = "add a condition">
<I class = "icon-plus icon-white"> </I>
</Button>
<Button type = "button" class = "btn-mini btn-info add-query-group" title = "add a group of conditions">
<I class = "icon-th-list icon-white"> </I>
</Button>
<Button type = "button" class = "btn-mini btn-danger delete-query-group" title = "delete this set of conditions">
<I class = "icon-minus icon-white"> </I>
</Button>
</Div>
</Div>
</Div>
<Div class = "query-item-template">
<Div class = "query-item">
<Input type = "text" value = "" placeholder = "field name" title = "field name" class = "property-name"/>
<Select class = "input-mini operate-type" title = "condition">
<Option value = "1" >=</option>
<Option value = "2">! = </Option>
<Option value = "3" >></option>
<Option value = "4" >=</option>
<Option value = "5"> </option>
<Option value = "6" ><=</option>
<Option value = "7"> LK </option>
</Select>
<Input type = "text" class = "query-value" value = "" placeholder = "value" title = "value"/>
<Select class = "input-medium value-type">
<Option value = "3"> String </option>
<Option value = "1"> Int </option>
<Option value = "2"> Double </option>
<Option value = "4"> DateTime </option>
</Select>
<Button type = "button" class = "btn-mini btn-danger delete-query-item" title = "delete condition">
<I class = "icon-minus icon-white"> </I>
</Button>
</Div>
</Div>
</Div>

It is not difficult here. The most important thing is the event of each button.
Take a closer look, there are a total of four buttons:
The preceding three conditions are: Add a row of conditions, add a group of conditions, and delete the group of conditions.
The right side of a single condition is: delete this condition.
The logic here is actually very simple:Copy codeThe Code is as follows: $ ('# queryiner iner'). append ($ ('. query-group-template>. query-group'). clone ())
$ ('# QueryContainer>. query-group'). first (). find ('. delete-query-group'). remove ();
$ ('Button. add-query-item'). live ('click', function (){
$ (This). parent (). parent (). append ($ ('. query-item-template>. query-item'). clone ());
Return false;
});
$ ('Button. add-query-group'). live ('click', function (){
$ (This). parent (). parent (). append ($ ('. query-group-template>. query-group'). clone ());
Return false;
});
$ ('Button. delete-query-group'). live ('click', function (){
If (! $ (This). parent (). hasClass ('query-group') {return false ;}
$ (This). parent (). parent (). remove ();
Return false;
});
$ ('Button. delete-query-item'). live ('click', function (){
$ (This). parent (). remove ();
Return false;
});

In addition, check the first two lines of the Code. Do not forget to add a set of conditions when loading for the first time, and remove the "Delete group conditions" button in the default group.
Frontend Data Processing
Interface interaction is really simple, but how can I transmit this data to the backend?
Can I extract fields from the form and pass them to the form? Then the backend is crying... It's a mess of data.
That... Since the structure of the query condition is very clear, why cannot it be converted into an object in javascript first?
Then, serialize this object...
Then, send the json file to the backend...
Finally, the backend defines the type of the same structure, and then deserializes...
That is to say, in this interaction process, you only need to convert the form data instance into an object in javascript!
Let me first define two objects (note that the field name must be the same as that of the backend ):Copy codeThe Code is as follows: function QueryGroup (){
This. GroupType = 0;
This. Items = [];
This. Groups = [];
}
Function QueryItem (){
This. Name = '';
This. OperatorType = 0;
This. Value = '';
This. ValueType = 0;
}

The method for converting instances into objects is also very simple and requires recursion. The basic logic is:
The object in the outermost QueryGroup is recycled once. If QueryItem is used, it points to the value. If QueryGroup is used, this method is called recursively.
The Code is as follows:Copy codeThe Code is as follows: function GetQueryGroup (group ){
Group = $ (group );
Var queryGroup = new QueryGroup ();
QueryGroup. GroupType = parseInt (group. find ('. group-type'). val ());
Var queryItems = group. children ('. query-item ');
For (var k = 0; k <queryItems. length; k ++ ){
Var queryItem = new QueryItem ();
QueryItem. Name = $ (queryItems [k]). find ('. property-name'). val ();
QueryItem. OperatorType = parseInt ($ (queryItems [k]). find ('. operate-type'). val ());
QueryItem. Value = $ (queryItems [k]). find ('. query-value'). val ();
QueryItem. ValueType = parseInt ($ (queryItems [k]). find ('. value-type'). val ());
QueryGroup. Items. push (queryItem );
}
Var childGroups = group. children ('. query-group ');
For (var k = 0; k <childGroups. length; k ++ ){
QueryGroup. Groups. push (GetQueryGroup (childGroups [k]);
}
Return queryGroup;
}

Finally, the form is a form submission, and an object is generated. serialize the object to json and encode it:
EncodeURIComponent (JSON. stringify (item ))
Backend Data Processing
Backend Data Processing consists of deserialization and conversion to query conditions.
The data structure has been defined above. As long as the field name is the same as that in json, it can be directly deserialized.Copy codeThe Code is as follows: var json = Uri. UnescapeDataString (Request ["query"]);
Var item = JsonConvert. DeserializeObject <QueryGroup> (json );

After two lines of code, it becomes an object in. net!
Finally, generating query conditions is actually very simple. It is also a method that can be called recursively. The basic logic is similar to the previous process of instantiating form data.
I have extended a method in QueryGroup. Among them, ICriteria and IMongoQuery have a similar structure. mongodb users can use it as IMongoQuery. It only includes a layer and eventually generates IMongoQuery.Copy codeThe Code is as follows: public class QueryGroup
{
Public GroupType {get; set ;}
Public List <QueryItem> Items {get; set ;}
Public List <QueryGroup> Groups {get; set ;}
Public ICriteria ToICriteria ()
{
ICriteria result = null;
Foreach (var criteria in GetICriteriaList ())
{
If (result = null)
{
Result = criteria;
Continue;
}
If (GroupType = Model. GroupType. AndAlse)
{
Result = result. Add (criteria );
Continue;
}
If (GroupType = Model. GroupType. OrElse)
{
Result = result. Or (criteria );
Continue;
}
}
Return result;
}
Private List <ICriteria> GetICriteriaList ()
{
Var list = new List <ICriteria> ();
Foreach (var item in Items)
{
List. Add (new Criteria (item. Name, item. OperatorType, new QueryValue (item. ValueType, item. Value, FieldHierarchyLevel. Child )));
}
Foreach (var group in Groups)
{
List. Add (group. ToICriteria ());
}
Return list;
}
}

After obtaining the query condition object, you can directly call the relevant query method.

Postscript
Mongodb is used in this scenario, so the final converted object is the mongodb query object. In fact, it is very convenient to convert SQL statements.

In addition, it is a little more complicated. It is also difficult to convert the tree into an Expression Tree in. net!

The gif Demo is attached.

Author: Dozer