쿼리 빌더 모델 사용

Angular Query Builder는 직렬화 가능/역직렬화 가능한 JSON 형식 모델을 제공하므로 SQL 쿼리를 쉽게 작성할 수 있습니다.

개요

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.

간단한 표현식 정의하기:

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

표현식 그룹 정의:

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);

하위 쿼리 정의:

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
});

모델은 JSON 형식으로 직렬화/역직렬화할 수 있으므로 클라이언트와 서버 간에 쉽게 전송할 수 있습니다.

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

하위 쿼리는 외부 쿼리의 조건으로 사용될 데이터를 검색하는 데 사용되는 다른 쿼리 내에 중첩된 쿼리입니다.

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.

다음 식 트리는 다음과 같습니다.

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
});

다음을 호출하여 직렬화 할 수 있습니다.

JSON.stringify(tree, null, 2);

이는 다음과 같이 전송됩니다.

{
  "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

Ignite UI for Angular Query Builder 구성 요소를 사용하여 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
});

다음은 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
});

이제 SQL 쿼리가 완료되었습니다.

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

우리 커뮤니티는 활동적이며 항상 새로운 아이디어를 환영합니다.