Uso del modelo del Generador de consultas

Angular Query Builder proporciona un modelo de formato JSON serializable/deserializable, lo que facilita la creación de consultas SQL.

Descripción general

This Angular Query Builder example demonstrates how the IgxQueryBuilderComponent expression tree could be used to request data from an endpoint Northwind WebAPI and set it as an IgxGridComponent data source.

Query Builder Model

In order to set an expression tree to the IgxQueryBuilderComponent, you need to define aFilteringExpressionsTree. Each FilteringExpressionsTree should have filtering logic that represents how a data record should resolve against the tree and depending on the use case, you could pass a field name, entity name, and an array of return fields. If all fields in a certain entity should be returned, the returnFields property could be set to ['*']:

const tree = new FilteringExpressionsTree(FilteringLogic.And, undefined, 'Entity A', ['*']);

Once the root FilteringExpressionsTree is created, adding conditions, groups or subqueries, could be done by setting its filteringOperands property to an array of IFilteringExpression (single expression or a group) or IFilteringExpressionsTree (subquery). Each IFilteringExpression and IFilteringExpressionsTree should have a fieldName that is the name of the column where the filtering expression is placed, and either a condition of type IFilteringOperation or a conditionName. If required, you could also set a searchVal, searchTree of type IExpressionTree, and ignoreCase properties.

Definición de una expresión simple:

tree.filteringOperands.push({
            fieldName: 'Name',
            conditionName: IgxStringFilteringOperand.instance().condition('endsWith').name,
            searchVal: 'a'
        });

Definición de un grupo de expresiones:

const group = new FilteringExpressionsTree(FilteringLogic.Or, undefined, 'Entity A', ['*']);
group.filteringOperands.push({
    fieldName: 'Name',
    conditionName: IgxStringFilteringOperand.instance().condition('endsWith').name,
    searchVal: 'a'
});
group.filteringOperands.push({
    fieldName: 'DateTime created',
    conditionName: IgxDateFilteringOperand.instance().condition('today').name
});
tree.filteringOperands.push(group);

Definición de una subconsulta:

const innerTree = new FilteringExpressionsTree(FilteringLogic.And, undefined, 'Entity B', ['Number']);
innerTree.filteringOperands.push({
    fieldName: 'Number',
    conditionName: 'equals',
    searchVal: 123
});
 tree.filteringOperands.push({
    fieldName: 'Id',
    conditionName: 'inQuery',
    searchTree: innerTree
});

El modelo se puede serializar/deserializar en formato JSON, lo que lo hace fácilmente transferible entre el cliente y el servidor:

JSON.stringify(tree, null, 2);

Using Sub-Queries

In the context of the IgxQueryBuilderComponent the IN / NOT-IN operators are used with the newly exposed subquery functionality in the WHERE clause.

Note

Una subconsulta es una consulta anidada dentro de otra consulta que se usa para recuperar datos que se usarán como condición para la consulta externa.

Selecting the IN / NOT-IN operator in a FilteringExpression would create a subquery. After choosing an entity and a column to return, it checks if the value in the specified column in the outer query matches or not any of the values returned by the subquery.

El siguiente árbol de expresión:

const innerTree = new FilteringExpressionsTree(FilteringLogic.And, undefined, 'Products', ['supplierId']);
innerTree.filteringOperands.push({
    fieldName: 'supplierId',
    conditionName: IgxNumberFilteringOperand.instance().condition('greaterThan').name,
    searchVal: 10
});

const tree = new FilteringExpressionsTree(FilteringLogic.And, undefined, 'Suppliers', ['supplierId']);
tree.filteringOperands.push({
    fieldName: 'supplierId',
    conditionName: IgxStringFilteringOperand.instance().condition('inQuery').name,
    searchTree: innerTree
});

Podría serializarse llamando:

JSON.stringify(tree, null, 2);

Esto se transferiría como:

{
  "filteringOperands": [
    {
      "fieldName": "supplierId",
      "condition": {
        "name": "inQuery",
        "isUnary": false,
        "isNestedQuery": true,
        "iconName": "in"
      },
      "conditionName": "inQuery",
      "searchVal": null,
      "searchTree": {
        "filteringOperands": [
          {
            "fieldName": "supplierId",
            "condition": {
              "name": "greaterThan",
              "isUnary": false,
              "iconName": "filter_greater_than"
            },
            "conditionName": "greaterThan",
            "searchVal": 10,
            "searchTree": null
          }
        ],
        "operator": 0,
        "entity": "Suppliers",
        "returnFields": [
          "supplierId"
        ]
      }
    }
  ],
  "operator": 0,
  "entity": "Products",
  "returnFields": [
    "supplierId"
  ]
}

SQL Example

Echemos un vistazo a un ejemplo práctico de cómo se puede utilizar el componente Ignite UI for Angular Query Builder para crear consultas SQL.

In the sample below we have 3 entities with names 'Suppliers', 'Categories' and 'Products'.

Let's say we want to find all suppliers who supply products which belong to the 'Beverages' category. Since the data is distributed across all entities, we can take advantage of the IN operator and accomplish the task by creating subqueries. Each subquery is represented by a FilteringExpressionsTree and can be converted to a SQL query through the transformExpressionTreeToSqlQuery(tree: IExpressionTree) method.

First, we create а categoriesTree which will return the categoryId for the record where name is Beverages. This is the innermost subquery:

const categoriesTree = new FilteringExpressionsTree(0, undefined, 'Categories', ['categoryId']);
categoriesTree.filteringOperands.push({
    fieldName: 'name',
    conditionName: IgxStringFilteringOperand.instance().condition('equals').name,
    searchVal: 'Beverages'
});

The corresponding SQL query for this FilteringExpressionsTree will look like this:

SELECT categoryId FROM Categories WHERE name = 'Beverages'

Then we create а productsTree that will return the supplierId field from the categoriesTree for the records where the categoryId matches the categoryId returned by the innermost subquery. We do this by setting the inQuery condition and the relevant searchTree. This is the middle subquery:

const productsTree = new FilteringExpressionsTree(0, undefined, 'Products', ['supplierId']);
productsTree.filteringOperands.push({
    fieldName: 'categoryId',
    conditionName: IgxStringFilteringOperand.instance().condition('inQuery').name,
    searchTree: categoriesTree
});

Este es el estado actualizado de la consulta SQL:

SELECT supplierId FROM Products WHERE categoryId IN (
    SELECT categoryId FROM Categories WHERE name = 'Beverages'
  )

Finally, we create а suppliersTree that will return all fields from Suppliers entity where the supplierId matches any of the supplierIds returned by the middle subquery. This is the outermost query:

const suppliersTree = new FilteringExpressionsTree(0, undefined, 'Suppliers', ['*']);
suppliersTree.filteringOperands.push({
    fieldName: 'supplierId',
    conditionName: IgxStringFilteringOperand.instance().condition('inQuery').name,
    searchTree: productsTree
});

Nuestra consulta SQL ya está completa:

SELECT * FROM Suppliers WHERE supplierId IN (
  SELECT supplierId FROM Products WHERE categoryId IN (
      SELECT categoryId FROM Categories WHERE name = 'Beverages'
    )
)

Now we can set the expressionsTree property of the IgxQueryBuilderComponent to suppliersTree. Furthermore, every change to the query triggers a new request to the endpoint and the resulting data shown in the grid is refreshed.

API References

Additional Resources

Nuestra comunidad es activa y siempre da la bienvenida a nuevas ideas.