Hello Gareth,

Terrasoft.ComparisonType.EXISTS is not used in the client-side system logic anymore since it was replaced with the createExistsFilter function. So you need to use it instead of EXISTS comparison type.

Best regards,

Oscar

Oscar Dylan,

I have tried the following but it is returning more than one record:

const caseId = this.get("MasterRecordId");
const esq = this.Ext.create(Terrasoft.EntitySchemaQuery, {
	rootSchemaName: "UsrItemInOrder"
});
esq.addAggregationSchemaColumn("Id", Terrasoft.AggregationType.COUNT, "RefundCount", Terrasoft.AggregationEvalType.ALL);
const esqFirstFilter = esq.createColumnFilterWithParameter(Terrasoft.ComparisonType.EQUAL, "UsrCase", caseId);
esq.filters.addItem(esqFirstFilter);
var subFilter = Terrasoft.createFilterGroup();
subFilter.addItem(Terrasoft.createColumnFilterWithParameter(Terrasoft.ComparisonType.EQUAL, "UsrRefundItem", true));
const esqSecondFilter = Terrasoft.createExistsFilter("Id", subFilter);
...

I am not filtering with a subquery here, just on the one table.

Gareth Osler,

Hi Gareth. Here is a sample of how to get a count (this gets a count of all accounts with a type of "Customer", however, to just get a count of the entire table you'd just omit the filter):

var esq = this.Ext.create("Terrasoft.EntitySchemaQuery", {
    rootSchemaName: "Account"
});
esq.addAggregationSchemaColumn("Id", Terrasoft.AggregationType.COUNT, "AccountCount");
esq.filters.add(Terrasoft.createColumnFilterWithParameter(Terrasoft.ComparisonType.EQUAL, "Type.Name", "Customer"));
esq.getEntityCollection(function(result) {
    console.log("Total customers", result.collection.first().get("AccountCount"));
});

With an exists sub-filter like in your code, it would look like this:

// get a count of all accounts with a "UsrClientSystem" record that has a system type of "Creatio"
var esq = this.Ext.create("Terrasoft.EntitySchemaQuery", {
    rootSchemaName: "Account"
});
esq.addAggregationSchemaColumn("Id", Terrasoft.AggregationType.COUNT, "AccountCount");
 
var subFilter = Terrasoft.createFilterGroup();
subFilter.addItem(Terrasoft.createColumnFilterWithParameter(Terrasoft.ComparisonType.EQUAL, "UsrSystemType.Name", "Creatio"));
esq.filters.addItem(Terrasoft.createExistsFilter("[UsrClientSystem:UsrAccount].Id", subFilter));
 
esq.getEntityCollection(function(result) {
    console.log("Total accounts with Creatio system", result.collection.first().get("AccountCount"));
}, this);

Ryan

Ryan Farley, Thank you for the reply.  I am querying with an exists filter on the same table as the esq query, the esq query is on table UsrItemInOerder, for an exist filter of there exists a record with a UsrRefundItem column value of true.  What would the column path be for a createExistsFilter() call in that situation?

Gareth Osler,

I'm not 100% sure I follow. So you want to know if there's a UsrItemInOrder record for the parent Order that has a value of true for the UsrRefundItem column? If that's the case, you can just simplify the query (no need for the exists sub filter):

var esq = this.Ext.create("Terrasoft.EntitySchemaQuery", {
    rootSchemaName: "UsrItemInOrder"
});
// get a count of items with a refund
esq.addAggregationSchemaColumn("Id", Terrasoft.AggregationType.COUNT, "RefundCount");
// for all UsrItemInOrder rows for the parent Order
esq.filters.add(Terrasoft.createColumnFilterWithParameter(Terrasoft.ComparisonType.EQUAL, "Order", this.get("MasterRecordId"));
// add filter for refund items only
esq.filters.add(Terrasoft.createColumnFilterWithParameter(Terrasoft.ComparisonType.EQUAL, "UsrRefundItem", true);
// get result, this will return 1 row with a count of refund items in RefundCount column
esq.getEntityCollection(function(result) {
    var hasRefunds = (result.collection.first().get("RefundCount") > 0);
});

Ryan

Ryan Farley,

That is essentially what I have done.  But could that query be optimized with an exists query, it is being run on a database with circa 1.5 million records.

Gareth Osler,

What I was trying to do is illustrated in SQL terms in this stackexchange question,

SELECT TOP (1) id 
  FROM dbo.table
  WHERE price IS NULL
    AND category = 'hardware';

which I am arriving at the conclusion is not possible with a Creatio ESQ query.  The solution counting the records matching the case Id and 'UsrRefundItem'

flag true conditions is probably the fastest otherwise.

Gareth Osler,

The only way to do a "top" or "limit" that I know of via ESQ is like this:

var esq = Ext.create("Terrasoft.EntitySchemaQuery", {
	rootSchemaName: "UsrEntity"
});
// get only first row in results
esq.rowCount = 1;
esq.addColumn("Id");
// add any filters
esq.getEntityCollection(function(response) {
	// only one row returned
}, this);

To be honest, I've never ran profiler to see if rowCount=X actually does translate to TOP X or LIMIT X, but I assume it does. However, I think the previous method of getting the count would be a more efficient way. The ESQ I posted earlier is the equivalent of:

select count(Id) as RefundCount from UsrItemInOrder where OrderId = 'SOMEID' and UsrRefundItem = 1

Ryan