Query Builder for Developers

From Sense/Net Wiki
Jump to: navigation, search
  •  
  •  
  •  
  •  
  • 100%
  • 6.3
  • Enterprise
  • Community
  • Planned

Overview

Sense/Net Query Builder helps users to build complex queries against Sense/Net Content Repository. It has two modes: the Query Wizard and the Query Editor. The Query Wizard helps novice users to create queries with some simple clicks, while the Query Editor enables expert users to edit queries in free text with Sense/Net Content Query Language (CQL), an extension of the Lucene Query syntax. Query Builder can be used as a Sense/Net Fieldcontrol or you can simply bind it to a textbox.

The wizard and the editor

Query Builder enables users to create complex custom queries easily. Although Sense/Net provides the ability to create your own queries through its LINQ API, it also provides a rich query language, the Sense/Net Content Query Language (CQL) through its query parser. CQL is an extension of the Lucene query syntax. Sense/Net Query Builder helps end users to create CQL queries. The component is a jQuery plugin based on Sense/Net’s OData functionality and some KendoUI controls. You can set the plugin to use and show only one of the modes or both. The Query Editor is only for expert users who know Sense/Net CQL syntax.

Query Wizard

Query Wizard lets the users create queries by adding expression and operation rows one by one with a few clicks. Check out the features at the end user documentation for the Query Wizard. If you use the Query Wizard every time when you do some interaction with it, the plugin creates a CQL query text in the background (when you add, delete or move a row). For example this query text can be added to the ’query’ parameter of a Sense/Net ECM OData service URL, if you want to request the OData service for a result list. A comment section is also added to the query in the background with the Query Wizard rows to rebuild the Query Wizard UI later. For example when we change tabs and then go back to the Query Wizard tab, the former expression and operator rows can be reloaded properly. You can also use this comment functionality when you implement query saving, and want to reload queries to the Wizard to edit it.

Querybuilder.png
DisplayName:a* OR Style:Cabrio /*[{"t":5,"ct":"Article","f":"DisplayName","op":"","v":"a*","d":"Display Name"},
{"t":1,"ct":null,"f":null,"op":null,"v":null},{"t":5,"ct":"Car","f":"Style","op":"","v":"Cabrio","d":"Style"}]*/

We use some KendoUI widgets in the Query Wizard. The frame of the plugin is based on the KendoUI Tabstrip and to display fancy and usable dropdowns, comboboxes, datetime and numeric textboxes we use [KendoUI ComboBox], DropdownList, DatetimePicker and NumericTextBox controls. There is another Sense/Net related jQuery plugin in use: in textbox handling, we use InputMachinator.js to create CSS customizable checkboxes.

Query Editor

If you are familiar with Sense/Net Content Query Language (CQL) you can use the Query Editor to create complex queries, in which case the full functionality of CQL is available. For example, users can create queries with sorting or limit the number of results in the query editor.

Queryeditor.png

/Root/Sites/Default_site?query=DisplayName:a*

Some of the common Sense/Net Content Query related functions (Query Presenter Portlet, SmartFolder) use Query Builder from version 6.3.

Getting started

You can bind the Query Builder to a common text area just like other jQuery plugins. First create a textarea:

<textarea id=”myTextarea”></textarea>

Initialize the Query builder via a jQuery selector:

$(’#myTextarea’).QueryBuilder({
       showQueryEditor: true,
       content: odata.getItemUrl('/Root/Sites/Default_Site'),
       showQueryBuilder: true,
       postProcess: function (q) {
          return q + '&$expand=ModifiedBy&$select=DisplayName,Path,Icon,ModifiedBy/FullName,ModificationDate,ModifiedBy/Path&metadata=no';
       },
       events: {
          execute: refreshResultList,
          save: querySave,
          saveas: querySaveAs,
          clear: clearResultList
       },
       templates: templates
});

Configuring the Query Builder jQuery plugin

Mode visibility, design and functionality of the Query Builder are highly customizable. All these properties are optionally customizable, so you can either leave them empty or just leave them out of the configuration.


showQueryEditor Boolean (default: true)

Sets the visibility of the Query Editor tab.


showQueryBuilder Boolean (default: true)

Sets the visibility of the Query Wizard tab.


content string,Path (default: null)

Sets the context of the Query Builder. Only the types enabled on this content will show in the type selection dropdown. If you leave this property empty or null, current content will be the context.


templates Object

You can add a JSON object with a list of html templates that you can use in the custom command button functions. We recommend using KendoUI templates, because they will fit Sense/Net’s new dialog/overlay handling. See more about this here: Dialogs. If you don’t want to use any command buttons in the Query Builder you can leave this property out.


SR Object

You can add a JSON object with a list of custom string resources. It’s an optional property, we prefer you change the string resources in the SN_QueryBuilder.xml. Note, that an upgrade may overwrite your custom string resources in SN_QueryBuilder.xml.


postProcess function

If you want to add the same additional criteria to the query on every execution or save, you can add a custom function here. This will be called before execution whenever you build a query. For example you can set which type a content should be returned as a result:

   postProcess: function (q) {
      return q + " AND Type:File";
   }


metadata array

If you want to use a custom list of types, fields or aspects in the Query Builder field or type selection dropdowns, you can add an array of objects here. These will be shown in the dropdowns both in the Query Editor and the Query Wizard. If you leave this out, all types will be listed in the type selection dropdown that are enabled on the current content or on the content that you’ve set.


actionbuttonPosition String (default: ’bottom’)

If you use the command buttons, you can set their position. You can choose top, right or bottom.

    actionbuttonPosition: 'top'


events

You can add custom functionality to the command buttons. The adjusted functions will be called when the buttons are clicked. If there are no events defined, no command buttons will be shown.


execute function

This function is called when the run/execute button is clicked. You can implement your custom functionality with these parameters:

query: this is the actual query that you see in the textbox or on the Query Wizard tab. It can be post processed by setting some post processing functions. (See postProcess function)

   DisplayName:a* &$expand=ModifiedBy&$select=DisplayName,Path,Icon,ModifiedBy/FullName,ModificationDate,ModifiedBy/Path&metadata=no

path:: this path can be called through OData. If you set some post processing functions, this will contain the path from the content parameter that you’ve set concatenate with the query.

   /Root/Sites('Default_Site')?query=DisplayName:a* &$expand=ModifiedBy&$select=DisplayName,Path,Icon,ModifiedBy/FullName,ModificationDate,ModifiedBy/Path&metadata=no

results:: this is a list of results. Use this parameter if you just want to do something with the result set. You get an array with the results.


save function

This function is called when the save button is clicked. You can implement your custom functionality with these enabled parameters:

queryTitle: a variable to store a query title.

queryType: a variable to store a query type, its value can be ’Public’ or ’Private’.

query: same as query by the execute event

content: it stores the value of the content parameter, that you’ve set in Query Builder.

path: same as query by the execute event

You can learn more about query saving in this article Built-in OData actions


save as function

This function is called when the save as button is clicked. The parameters are the same as by the save function.


clear function

This function will be called when the clear button is clicked. Basic clear functionality is implemented in the plugin (it clears the query editor text box and removes all the Query Wizard rows). You can also add additional functionality (e.g. clear your custom result list also).

Query Builder as FieldControl

QueryBuilder is a Fieldcontrol in Sense/Net ECM from version 6.3. You can put it into your Ascx content views like this:

<sn:QueryBuilder runat=”server” ID=”QueryBuilder” />

or set your field in its Content Type Definition to behave as a Query Builder as follows:

   <Configuration>
        <ControlHint>sn:QueryBuilder</ControlHint>
   </Configuration>

Related links