Example of 'createExistsFilter(columnPath)'

Hello,

The examples can be found here and you can also create an advanced filter in some section and review its body. For example here is the filter to list all accounts that have any activity connected to an account:

If we take a look at the network tab of the console to get the SelectQuery (to identify the needed query you can clear the network tab and then hit the "Accounts" section header in the top left corner or click the "Apply" button in the filter and as a result the query will be sent) that went to the database to retrieve data we can see the path to the column in the filters object:

comparisonType and filterType are standing for the "exists" function (can be seen in the Terrasoft.ComparisonType and Terrasoft.FilterType objects).

So if you need to retrieve a correct path to some column you can use this approach to find the correct path.

Best regards,

Oscar

Oscar Dylan,

Thanks for the reply, and the 'columnPath' tips.

It's actually how the  'createExistsFilter(columnPath)' ESQ filter is used that I'm not sure of.  If you could give me an example of this function being used that would be great.

Gareth Osler,

It's not widespread in the system, but there was one use case when it was needed and it's described in this community thread. One of the examples in the system: please see the historyMessageEsqApply method from the PortalMessageHistoryItemPage module:

filterInnerGroup.add("PortalMessageExists",
							esq.createExistsFilter("[PortalMessage:Id:RecordId].Id"))

Best regards,

Oscar

Gareth Osler,

Hi Gareth, the usage of it is just like any ESQ, it returns the list of records that match the condition(s). For the example Oscar provided, to expand on that further, lets say you want a list of all accounts where an activity exists (and then to go further) where the activity has a certain owner. In this case we create an exists filter (for accounts where activities exist) that has a sub-filter (for activities with a certain owner).

That code would look like this:

var esq = Ext.create("Terrasoft.EntitySchemaQuery", { 
	rootSchemaName: "Account" 
});
 
esq.addColumn("Id");
esq.addColumn("Name");
 
// create the sub filter for the condition inside the exists
var subFilters = Terrasoft.createFilterGroup();
subFilters.addItem(Terrasoft.createColumnFilterWithParameter(Terrasoft.ComparisonType.EQUAL, "Owner", "a6b4ea0c-420c-45ab-81e7-6e76c8cc15f7"));
 
// now create the exists filter and pass the sub filter conditions to it
// note, this second param of subFilters is optional if all you want is the exists condition
var existsFilter = Terrasoft.createExistsFilter("[Activity:Account].Id", subFilters)
esq.filters.addItem(existsFilter);
 
// now use the results how you would any ESQ
// in this case a list of accounts where an activity exists with a certain owner
esq.getEntityCollection(function(result) {
	if (result.success) {
		Terrasoft.each(result.collection.getItems(), function(entity) {
			var name = entity.values.Name;
			var id = entity.values.Id;
			console.log(name, id);
		}, this);
	}
}, this);

This is the same as this: 

Ryan

Thank you for that. I think I should now be able to shave a few machine cycles off by implementing the correct query operator.

I have to admit I didn't fully understand the advanced filter 'exists' operator until posting this question and finding this post which gives the SQL equivalent of an ESQ 'createExistsFilter(columnPath)' filter.  The Creatio exists operator essentially implements a SQL EXISTS operator with a correlated subquery.  The column path using a reverse connection sets up a join to another table with the option of adding more filters to the subquery.