Question

EntitySchemaQuery in @creatio-devkit/common sdk

18:21 Apr 03, 2024

Cheers to all.

I'm developing my custom UI component in Angular and I need to get some data from Creatio database inside of my component. I discovered there is EntitySchemaQuery class in Creatio's sdk, so I decided to use it, but I'm stuck when I need to retreive data with that ESQ as I didn't find method for that. In classic ESQ there was getEntityCollection method for that. Could you please suggest how do I retrieve data from the database? Either using ESQ or other class, but user's authorization matters.

Here is the code of component.

import { Component, OnInit, Input, Output, 
        ViewEncapsulation, EventEmitter, SimpleChanges  } from '@angular/core';
import { CrtViewElement, CrtInput, CrtOutput, EntitySchemaQuery,
          ComparisonType,  AggregationType, AggregationEvalType, isGuid} from '@creatio-devkit/common';
 
@Component({
  selector: 'usr-input',
  templateUrl: './input.component.html',
  styleUrls: ['./input.component.scss'],
  encapsulation: ViewEncapsulation.ShadowDom
})
/* Add the CrtViewElement decorator to the InputComponent component. */
@CrtViewElement({
  selector: 'usr-input',
  type: 'usr.Input'
})
export class InputComponent implements OnInit {
  constructor() {}
 
  @Input("recordId")
  @CrtInput()
  /* The input recordId. */
  public recordId!: string;
 
  /* Add decorators to the EventEmitter<string>() event. */
  @Output()
  @CrtOutput()
  /* Track input value changes. */
  public valueChange = new EventEmitter<string>();
 
  ngOnInit(): void {
  }
 
 
  ngOnChanges(changes: SimpleChanges) {
    console.log(changes);
 
    if (isGuid(changes["recordId"]?.currentValue)) {
      let esq = new EntitySchemaQuery("ClvObject");
      esq.addAggregationFunctionColumn("Id", AggregationType.Count, "AppCount", AggregationEvalType.All);
      esq.filters.addSchemaColumnFilterWithParameter(ComparisonType.Equal, "ClvProject", this.recordId, "currentFilter");
 
      let record = esq.getMetadata();
      console.log(record);
    }
  }
}

3 comments

Ryan Farley

19:00 Apr 03, 2024

The EntitySchemaQuery classes in the devkit don't do anything, at least not how it is exposed. The ESQ classes require an executor class that actually *runs* the query and provides the connection. We don't have access to this, the executor that runs the ESQ is not part of the devkit sdk. This is intentional/by design since they want us to use the new Model classes instead. This better anyways, the Model class is far easier and more intuitive IMO.

The intended way to get data now is the Model class, which is in the devkit sdk. Here's some articles on the topic:

Model Query Using Filters: https://customerfx.com/article/querying-data-using-filter-conditions-vi…

Model Query for a Single Record Given it's Id: https://customerfx.com/article/retrieving-a-record-via-the-model-class-…

Also, the model class does inserts/updates/deletes:

Inserting a Record: https://customerfx.com/article/inserting-a-record-from-client-side-code…

Updating a Record: https://customerfx.com/article/updating-a-record-from-client-side-code-…

Deleting a Record: https://customerfx.com/article/deleting-a-record-from-client-side-code-…

Copying a Record: https://customerfx.com/article/copying-a-record-from-client-side-code-u…

You can also use the Model class to get an object's schema:

Get Object Schema: https://customerfx.com/article/getting-an-object-schema-using-the-model…

Ryan

Andrii Orlenko

12:16 Apr 04, 2024

Hi, Ryan. Thanks for quick reply.

Maybe you could suggest how do I need to prepare aggregate columns for the query using Model class? I tried it as described below

  async ngOnChanges(changes: SimpleChanges) {
    console.log(changes);
 
    if (isGuid(changes["recordId"]?.currentValue)) {
      const dataModel = await Model.create("ClvObject");
      const filters = new FilterGroup();
      filters.addSchemaColumnFilterWithParameter(ComparisonType.Equal, "ClvProject", this.recordId, "currentFilter");
      const records = await dataModel.load({
//        attributes: ["Id", "ClvName", "ClvType", "ClvProject.ClvCommissioning"],
        attributes: [{ 
          aggregationConfig: {
            aggregationFunction: AggregationFunction.Count,
          },
          type: "aggregation",
          path: "Id",
          name: "AppCount",
          caption: "AppCountCaption",
          dataValueType: DataValueType.Integer
        }],
        parameters: [{
            type: ModelParameterType.Filter,
            value: filters
        }]
      });
      console.log(records);
 
    }
  }

But instead of getting one record with one column "AppCount" containig 2, I get two records each containg "Id" and "AppCount"

Andrii Orlenko

18:22 Apr 04, 2024

Ok< I found out the way. Looks like aggregationConfig isn't yet working well and functionConfig should be used instead. Here is working exampe.

  async ngOnChanges(changes: SimpleChanges) {
    console.log(changes);
 
    if (isGuid(changes["recordId"]?.currentValue)) {
 
      const dataModel = await Model.create("ClvObject");
      const filters = new FilterGroup();
      filters.addSchemaColumnFilterWithParameter(ComparisonType.Equal, "ClvProject", this.recordId, "currentFilter");
      const records = await dataModel.load({
//        attributes: ["Id", "ClvName", "ClvType", "ClvProject.ClvCommissioning"],
        attributes: [
          { 
            type: "function",
            path: "Id",
            name: "AppCount",
            caption: "AppCountCaption",
            dataValueType: DataValueType.Integer,
            functionConfig: {
              aggregation: AggregationFunction.Count,
              type: "aggregation",
              aggregationEval: "all",
            }
          }],
          parameters: [{
              type: ModelParameterType.Filter,
              value: filters
          }
        ]
      });
      console.log(records);
 
    }
  }

And result

Show all comments