Does a Function for filtering an Aggregate has a huge performance impact?
Application Type
Reactive
Service Studio Version
11.52.0 (Build 59053)

I want to create some ServerSide-Functions that return true or false which i would use in an Aggregate to filter elements. 

Is it an okayish idea and common? I tried to build the filter statements by string but it doesn't work, so i want to create the logic to filter elements in a function.


mvp_badge
MVP
Solution

Hi Kevin,

You cannot do what you think you can (or what I think you think 😄). You can only use Functions in a Filter of an Aggregate if the output of the Function is not dependent on data from the Aggregate. That is, the Functions are evaluated first, before the Aggregate runs, and then its values are passed as input parameters for the SQL that is executed.

The reason for this is that the Aggregate runs as SQL code on the database server, and the database server cannot run OutSystems server code, which a) runs on a different server to begin with and b) is converted to .NET code running on IIS. There is no way to have the database server call .NET code on another server.

its dependent on the filters you are using but you can also used the list filter for this scenario
for better understanding can you put some example or something

Thanks and Regards,

Akshay  Deshpande

I created a Structure List

  • Entityname (customer)
  • Columnname (FirstName)
  • Type (Text)
  • Value ("Kevin")

Because i want to pass Aggregate Filters from a (filtering-)block to a screen.

In the Screen i need to filter the aggregate depending on the value inside the sturcutre list.

I wanted to create a string with 'customer.firstname like "%Kevin%" and ...' but yeah an aggregate filter doesn't accept strings only boolean values. So i thought about creating a function, i could pass the entity and the filter-structure-list -> inside that function, the true/false is calculated.

give filter 

Edit : example 


thx, i need to build that filter somehwere outside and pass it as a string into that filter. But you can't use variables as a filter-expression, just boolean values. So i thought about calling a function that will return a boolean.

Hi Kevin,

You´ d have to use SQL node instead of aggregate, passing in part or all of the sql as a parameter after preparing it beforehand.

But that is to be done with caution to avoid sql injection vulnerabilities. 

Dorine

mvp_badge
MVP
Solution

Hi Kevin,

You cannot do what you think you can (or what I think you think 😄). You can only use Functions in a Filter of an Aggregate if the output of the Function is not dependent on data from the Aggregate. That is, the Functions are evaluated first, before the Aggregate runs, and then its values are passed as input parameters for the SQL that is executed.

The reason for this is that the Aggregate runs as SQL code on the database server, and the database server cannot run OutSystems server code, which a) runs on a different server to begin with and b) is converted to .NET code running on IIS. There is no way to have the database server call .NET code on another server.

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.