The main Active Query Builder namespace.

Modules

Properties

Methods

AQB.Web.onQueryBuilderReady(function (qb) {
    qb.on(qb.Events.SqlChanged, function () {
        AQB.Web.CriteriaBuilder.loadColumns();
    });
});

AQB.Web.onCriteriaBuilderReady(function (cb) {
    cb.loadColumns();
});

Objects

Properties:

User interface components:

• GridComponent
• NavBarComponent
• CanvasComponent
• TreeViewComponent
• StatusBarComponent
• UserQueriesComponent
• EditorComponent

Each of the aforementioned components has the element property. This is a reference to a jQuery object. With this object you can perform any manipulations with the DOM elements constituting the component, particularly:

• change size
• remove/add classes
• relocate element on the page
• hide
• use other libraries to change their behavior, i.e. make it resizable, draggable, etc.

You can find the example of such usage in this article: How to make resizable, change the layout of controls on the web page?.

Events:

           AQB.Web.onQueryBuilderReady(function(qb) {
                qb.on(qb.Events.DataSourceAdding,
                    function(op, abort) {
                        abort.abort = true;
                    });
                qb.on(qb.Events.LinkCreating,
                    function(link, abort) {
                        abort.abort = true;
                    });
                qb.on(qb.Events.LinkDeleting,
                    function(link, abort) {
                        abort.abort = true;
                    });
            });

A dto object of the modified entity is passed as the first parameter for all query object model changing events. 

Methods:

AQB.Web.QueryBuilder.on(AQB.Web.QueryBuilder.Events.SQLError, function(e, app, error) { 
    console.log(app);
    console.log(error);
})

AQB.Web.QueryBuilder.showOverlay('Message');

$(function() {
    var codeMirror = CodeMirror(document.body, {
        mode: 'text/x-sql',
        indentWithTabs: true,
        smartIndent: true,
        lineNumbers: true,
        matchBrackets: true
    });
			
	AQB.Web.QueryBuilder.setEditor({
		element: document.querySelector('.CodeMirror'),
		getSql: function () {
			return codeMirror.getValue();
		},
		setSql: function (sql) {
			codeMirror.setValue(sql);
		},
		setCursorPosition: function (pos, col, line) {
			this.focus();
			codeMirror.setCursor(line - 1, col - 1, { scroll: true });
		},
		focus: function () {
			codeMirror.focus();
		},
		onChange: function (callback) {
			this.changeCallback = callback;
			codeMirror.on('change', this.changeCallback);
		},
		remove: function () {
			codeMirror.off('change', this.changeCallback);
			this.element.remove();
		}
	});
});

AQB.Web.QueryBuilder.validateExpression('Max(id) ; (', function(res, errorText) {
    console.log(res);
    console.log(errorText);
});

Control to display the query diagram pane. 

Properties:

Events:

• CanvasContextMenuCommand
• CanvasOnClick
• CanvasOnEditUserField
• CanvasOnMoveTable
• CanvasOnRemoveCTEFromQuery

Methods:

getDataSources

Returns list of all DataSource objects on the Canvas.

getLayout

Returns the DataSourceLayoutDto object which contains information about the datasources layout to send to the server.

redraw

Redraws the links on the Canvas.

refresh

Repaints the whole content of the Canvas. It should be used if the QueryBuilder was initialized being hidden on the page when the component is displayed. If the component is not displayed at the moment, this method does nothing.

        $(document).on('shown.bs.tab', 'a[data-toggle="xxx"]', function (event) {
            var tabName = $(this).attr('data-target');
            if (tabName === '#QueryTab') {
                AQB.Web.QueryBuilder.CanvasComponent.refresh();
            }
        });

hide

Hides the control.

show

Shows the hidden control on the page and updates its state.

An object representing an item in the FROM clause. Might be a database object reference, a derived table, a named query reference (from CTE).

Properties:

Methods:

close

Removes a Datasource from the Canvas.

trash

Removes a Datasource from the Canvas. If a Datasource is a named sub-query, removes it from CTE.

select

Focuses a Datasource on the Canvas.

showMenu

Displays the Datasource menu. (behind the sandwich icon)

showToolbar

Displays the filter toolbar.

hideToolbar

Hides the filter toolbar.

getBBox

Returns the Datasource control coordinates on the Canvas.

reCreate

Recreates a DataSource control.

moveToTop

Places a DataSource above the others.

dto

Returns a data transfer object to send data to the server.

checkAllField

Marks all fields as selected.

var canvas = AQB.Web.QueryBuilder.CanvasComponent;
var ds = canvas.getDataSources()[0];
ds.checkAllField(true); // true - check, false - uncheck

checkField

Marks a field with the specified name as selected.

var canvas = AQB.Web.QueryBuilder.CanvasComponent;
var ds = canvas.getDataSources()[0];
ds.checkField('id', true); // true - check, false - uncheck

An object representing a join line between objects on the Canvas.

Properties:

Events:

• CanvasLinkOnClick

Methods:

draw

Redraws the Link.

dto

Returns a data transfer object to send data to the server.

select

Makes Link focused on the Canvas.

remove

Removes the Link from the Canvas.

var link = AQB.Web.QueryBuilder.getLinks()[0];
l.remove();

setLeftType

Sets the left join type (inner/outer).

setRightType

Sets the right join type (inner/outer).

var link = AQB.Web.QueryBuilder.getLinks()[0];
link.setRightType(MetaData.JoinType.Outer);

The object representing the Query Columns List grid control.

Properties:

Events:

• GridBeforeCustomEditCell
• GridOnReload

Methods:

getAllRows

Returns all GridRow objects from the Query Columns List except the empty one.

getEmptyRow

Returns the GridRow object for an empty row which always exists at the bottom of the grid.

prependEmptyRow

Adds a new row with empty expression to the top of the list and returns the GridRow object for it.

appendEmptyRow

Adds a new row with empty expression to the bottom of the list and returns the GridRow object for it.

tooltipGetText

Allows overriding a tooltip for grid cells.

findRowByExpression

Returns the GridRow object with the given SQL expression or null if such expression does not exist.

removeRow

Removes the given GridRow from the Query Columns List.

grid = AQB.Web.QueryBuilder.GridComponent;
var row = grid.findRowByExpression('id');
grid.removeRow(row);

toggleGrouping

Inverts the Grouping flag of the Query Columns List.

grid = AQB.Web.QueryBuilder.GridComponent;
grid.toggleGrouping();

setColumnWidth

Sets the width of a grid column.

grid = AQB.Web.QueryBuilder.GridComponent;
grid.setColumnWidth('expression', '20%');
grid.setColumnWidth('alias', 100);
grid.setColumnWidth('sorting', '100px');

The object representing the header of the Query Columns List grid control.

Properties:

Methods:

setColumnWidth(name, width)

Sets the width of the specified column in pixels.

grid = AQB.Web.QueryBuilder.GridComponent;
grid.header.setColumnWidth("expression", 300);

getColumnWidth(name)

Gets the width of the specified column in pixels.

An object representing a row in the Query Columns List grid control.

Properties:

Methods:

delete

Deletes the row. 

dto

Returns a data transfer object to send data to the server.

isEmpty

Indicates whether this row is an empty row.

isChecked

Indicates whether this row is selected for output. (forms an item in the SELECT list)

getExpression

Returns SQL expression of the row.

getAlias

Returns alias ("Column Name") of the row.

getSorting

Returns the Sorting attribute of the row.

getAggregate

Returns the aggregate function of the row. 

getGrouping

Returns the Grouping flag of the row.

getCondition

Returns condition (value of the "Criteria" cell) of the row.

check

Sets/clears the Output (selected) flag of the row.  

var grid = AQB.Web.QueryBuilder.GridComponent;
var row = grid.findRowByExpression('id');
row.check(true);

setExpression

Sets SQL expression for the row.

var grid = AQB.Web.QueryBuilder.GridComponent;
var row = grid.findRowByExpression('id');
row.setExpression('Max(id)');

addExpression

Adds a new row, sets SQL expression for it, and returns the row object to a callback.

var newExpression = "CASE WHEN 1=1 THEN 1 ELSE 0 END";
var newAlias = "AAA";
var grid = AQB.Web.QueryBuilder.GridComponent;
grid.addExpression(newExpression, function(r) {
    r.setAlias(newAlias);
});

setAlias

Sets the row alias ("Column name" attribute).

var grid = AQB.Web.QueryBuilder.GridComponent;
var row = grid.findRowByExpression('id');
row.setAlias('row_id');

setSorting

Specifies the sorting attribute for the row.

var grid = AQB.Web.QueryBuilder.GridComponent;
var row = grid.findRowByExpression('id');
row.setSorting(AQB.Web.Enum.ItemSortType.Asc);

setAggregate

Specifies aggregate function of the row.

var grid = AQB.Web.QueryBuilder.GridComponent;
var row = grid.findRowByExpression('id');
row.setAggregate('Sum');

setGrouping

Sets/clears the grouping flag of the row.

var grid = AQB.Web.QueryBuilder.GridComponent;
var row = grid.findRowByExpression('id');
row.setGrouping(true);

setCondition

Sets condition (value of the "Criteria" cell) for the row.

var grid = AQB.Web.QueryBuilder.GridComponent;
var row = grid.findRowByExpression('id');
row.setCondition('is not null');

The object representing the Database Schema View control.

Properties:

Methods:

setFilter

Specifies and applies the new filter value for the tree.

clearFilter

Clears the filter and restores the unfiltered state.

tooltipGetText

Allows overriding the default text displayed in the tooltip for nodes of the tree.

AQB.Web.QueryBuilder.TreeViewComponent.tooltipGetText = function (item) {
	return item.fullName + ' (' + item.typeStr + ')';
}

The object representing the Reusable Queries control which lets end-users save, manage and reuse their SQL queries.

Properties:

Events:

• OnCreateFolder
• OnCreateUserQuery
• OnEdit
• OnRemove
• OnRename
• OnSaveUserQuery

Methods:

on

Binds a handler to the event.

AQB.Web.QueryBuilder.UserQueriesComponent.on(AQB.Web.QueryBuilder.UserQueriesComponent.Events.OnRemove, function (e, options) {
     log(options);
});

The object representing the SQL Text Editor control.

Properties:

Methods:

getSql

Returns the text from the editor.

setSql

Sets new text to the editor.

setCursorPosition

Moves cursor to the specified position.

focus

Moves focus to the editor.

remove

Deletes the control.

The object representing the Status Bar control which warns the user about inconsistent query states and reports about errors.

Properties:

Methods:

messageInfo

Shows an informational message to the user.

messageWarning

Shows warning message to the user. 

messageError

Shows error message to the user. 

messageText

Shows message of the specified type to the user. 

var statusbar = AQB.Web.QueryBuilder.StatusBarComponent;
statusbar.messageInfo('QueryBuilder has loaded');
statusbar.messageText('Something has happened!!!', 'error');

show

Displays the control on the page.

hide

Hides the control on the page.

The object representing the Query Navigation Bar. Contains the Query Structure Tree button, the Breadcrumbs Bar, the Union Navigation Bar, and the toolbar to add new sub-queries of different types. The two latter elements reside on the second row which is displayed on hover.

Properties:

Events:

• BreadCrumbSelectNode
• NavBarAction
• TreeStructureSelectNode

Methods:

disableUnions

Disables the possibility to work with set operators. (hides the Union Navigation Bar)

disableCte

Disables the possibility to add named sub-queries to the Common Table Expression.

The object representing the SQL Filter Builder control.

Remarks

Criteria Builder is not a part of the Query Builder component itself. It allows editing conditions applied to the query by the Query Transformer server-side object.

Criteria Builder must be initialized separately on both the client and the server sides.

Initialization

When you use both QueryBuilder and CriteriaBuilder objects on the same page, initialized with the same id (AQB.Web.UI.QueryBuilder(qbId), AQB.Web.UI.CriteriaBuilder(qbId)), changes in the Query Builder are automatically passed to the Criteria Builder, but if they have different ids, synchronization must be initialized manually.

AQB.Web.onQueryBuilderReady(function (qb) {
    qb.on(qb.Events.SqlChanged, function () {
        AQB.Web.CriteriaBuilder.loadColumns();
    });
});

It is not necessary to subscribe to this event to update the columns list. You can call the loadCoumns method on switching to the Query Results tab, on a button click, etc.

Displaying the result query data to the user

Columns retrieval

After the Query Builder gets updated and the list of query output columns changes, the AfterSyncCriteriaBuilder event is fired. You can handle it to update the list of columns in the result data grid.

AQB.Web.onCriteriaBuilderReady(function (cb) {
    cb.on(cb.Events.AfterSyncCriteriaBuilder, function () {
        updateGridColumns(cb.Columns); // your method to update grid columns       
    });
});

Data retrieval

To retrieve data when the user changes the filter in CriteriaBuilder, subscribe to the CriteriaBuilderChanged event.

AQB.Web.onCriteriaBuilderReady(function (cb) {
    cb.on(cb.Events.CriteriaBuilderChanged, function () {
        cb.transformSql(function () {
                getData(); // your method to request data from server
            });        
    });
});

Properties:

Events:

• AfterSyncCriteriaBuilder

• CriteriaBuilderChanged

Subscription to the CriteriaBuilder events is possible in the AQB.Web.onCriteriaBuilderReady method.

Methods:

on

Binds a handler to the event.

AQB.Web.CriteriaBuilder.on(AQB.Web.CriteriaBuilder.Events.AfterSyncCriteriaBuilder, function(e, cb) {
    console.log(cb.Columns);
})

isValid

Indicates if there aren't any invalid conditions values exist.

clear

Clears all conditions.

loadColumns

(Re)loads the original query columns.

transformSql

Sends conditions to the server and gets the transformed query text.

var cb = AQB.Web.CriteriaBuilder;
cb.transformSql(function(sql) {
    console.log(sql);
})

addCondition

Adds new condition for the column.

var cb = AQB.Web.CriteriaBuilder;
var column = cb.Columns[0];
cb.addCondition(column, 'is equal to', '1');

addGeneralCondition

Adds general condition.

var cb = AQB.Web.CriteriaBuilder;
cb.addGeneralCondition('1 != 1');

addGroup

Adds a new condition group.

var cb = AQB.Web.CriteriaBuilder;
cb.addGroup('any');

Note:

Methods of AQB.Web.Core module does not immediately apply changes to the query. Changes must be explicitly sent to the server via the sendDataToServer() method call.

Events:

• AfterCustomEditCell
• ContextMenuReceived
• DataReceived
• DataSending
• QueryStructureChanged
• SQLEErrorReceived
• SyncFinished
• UserDataReceived
• UserQueriesReceived

Events handling:

QB.Web.Core.on(QB.Web.Core.Events.DataReceived, function (e, data) {
    //ExchangeObject
    console.log(data);
});

QB.Web.Core.removeListener(AQB.Web.Core.Events.DataReceived, yourHandler);

console.log(AQB.Web.Core.Events);

AQB.Web.Core.updateSQL('Select 1')
AQB.Web.Core.sendDataToServer()

Data exchange:

AQB.Web.Core.setUserData({ message: 'awesome message' });
AQB.Web.Core.sendDataToServer();

qb.UserDataReceived += (builder, args) =>
{
   //builder - current instance of the QueryBuilder on the server
   //args - data received from client
};

SQL query manipulations:

var dto = new AQB.Web.Dto.DataSourceDto();
dto.Name = "Customers"
AQB.Web.Core.addDataSource(dto);

var ds = AQB.Web.QueryBuilder.getDataSources()[0];
var dto = ds.dto();
dto.Alias = "NewAlias";
AQB.Web.Core.updateDataSource(dto);
AQB.Web.Core.sendDataToServer();

var ds = AQB.Web.QueryBuilder.getDataSources()[0]
var dto = ds.dto()
AQB.Web.Core.removeDataSource(dto)
AQB.Web.Core.sendDataToServer()

var dto = new AQB.Web.Dto.DataSourceLinkDto();
var left = new AQB.Web.Dto.DataSourceLinkPartDto();
var right = new AQB.Web.Dto.DataSourceLinkPartDto();

var dataSources = AQB.Web.QueryBuilder.getDataSources()

left.DatasourceGuid = dataSources[0].dto().Guid;
left.FieldName = "AddressID";

right.DatasourceGuid = dataSources[1].dto().Guid;
right.FieldName = "AddressTypeID";

dto.Left = left;
dto.Right = right;

AQB.Web.Core.addLink(dto);
AQB.Web.Core.sendDataToServer()

var link = AQB.Web.QueryBuilder.getLinks()[0]
var dto = link.dto()
dto.Left.Type = AQB.Web.Enum.LinkSideType.Outer
AQB.Web.Core.updateLink(dto)
AQB.Web.Core.sendDataToServer()

var link = AQB.Web.QueryBuilder.getLinks()[0]
var dto = link.dto()
AQB.Web.Core.removeLink(dto)
AQB.Web.Core.sendDataToServer()

var dto = new AQB.Web.Dto.GridRowDto()
dto.Expression = "HumanResources.Employee.EmployeeID"
AQB.Web.Core.addGridRow(dto)
AQB.Web.Core.sendDataToServer()

var dto1 = new AQB.Web.Dto.GridRowDto();
dto1.Expression = "1";
var dto2 = new AQB.Web.Dto.GridRowDto();
dto2.Expression = "2";
AQB.Web.Core.addGridRows([dto1, dto2]);
AQB.Web.Core.sendDataToServer();

var row = AQB.Web.QueryBuilder.getQueryColumnList()[0];
var dto = row.dto();
dto.Grouping = true;
AQB.Web.Core.updateGridRow(dto);
AQB.Web.Core.sendDataToServer();

var row1 = AQB.Web.QueryBuilder.getQueryColumnList()[0];
var dto1 = row.dto();
dto1.SortOrder = 1;
var row2 = AQB.Web.Application.getQueryColumnList()[1];
var dto2 = row.dto();
dto2.SortOrder = 2;
AQB.Web.Core.updateGridRows([dto1, dto2]);
AQB.Web.Core.sendDataToServer();

var row = AQB.Web.QueryBuilder.getQueryColumnList()[0];
var dto = row.dto();
AQB.Web.Core.removeGridRow(dto);
AQB.Web.Core.sendDataToServer();

var row1 = AQB.Web.QueryBuilder.getQueryColumnList()[0];
var dto1 = row1.dto();
var row2 = AQB.Web.Application.getQueryColumnList()[1];
var dto2 = row2.dto();
AQB.Web.Core.removeGridRows([dto1, dto2]);
AQB.Web.Core.sendDataToServer();