Multiple Combo Box selected filters against an an aggregate.

I have attached a picture of what I am attempting to achieve. I have asked multiple questions and I keep getting stuck so I thought I would request some architectural assistance.

I need to query the database to retrieve and display what will be around 100K records.
I achieved this with a simple aggregate but a forum question about my need to utilize the 'IN' SQL WHERE clause mechanism I was steered towards a Custom or Advanced SQL. The result of this I have stored both in a Entity and a Structure List.

I then attempt to do a select unique across a few individual columns of the results of the prior aggregate\Structure list but am unable to achieve this. When attempting to create these UNIQUE aggregate to get my potential search values I cannot choose a structure list as my source. When I select the entity that defines the overall result set it never has any records. A prior Forum (https://www.outsystems.com/forums/discussion/43816/build-an-aggregate-off-of-results-of-custom-sql-statement/) request about this stated that Aggregates go to database and if there are no records saved I won't get any results.

I followed the tutorials and understand the iteration of screen filtering in the movie database.
I need some direction on...
How to store the Query result set to be able to...
Derive unique filter candidates from that result set...
Pass those multiple selected filter attributes back into my Original Custom or Advanced query
At this point I can hit the database again for the filtered records..

But I think I do need to get my Unique values from the result of the initial SQL.

Hopefully I my scope description is clear enough to get some direction.

Thanks.




Hi Bradford,

Though there may be 100K records in the database, unless you also want to export them to an Excel file or the like, there's typically no need to actually retrieve all 100K records. In general, unless you have a very specific use case, all filtering is done in the database, and you retrieve only the records that are filtered (and then only the first few you actually want to display to the user).

If you are using an Aggregate, and you have simple filter criteria (so no SQL "IN"), and a filter criterion can be either present or not, you would typically add a Filter like this:

FilterValue = "" or MyEntity.MyAtribute1 like "%" + FilterValue + "%"

or, if it's an Id of some sort:

FilterValueId = NullIdentifier() or MyEntity.MyAtribute1Id = FilterValueId

If you want to be able to filter on multiple criteria of the same type, i.e. what you'd use a SQL "IN" for you can use the above only if you have a limited amount of criteria (say always max. 3), and you could do something like:

(FilterValue1 = "" or MyEntity.MyAtribute1 like "%" + FilterValue1 + "%") or
(FilterValue2 = "" or MyEntity.MyAtribute1 like "%" + FilterValue2 + "%") or
(FilterValue3 = "" or MyEntity.MyAtribute1 like "%" + FilterValue3 + "%")

However, in most cases (especially with a variable amount of choices, like when the possible selection criteria come from the database), you're stuck with creating a SQL query (a.k.a. "Advanced Query", from back in the days when we also had "Simple Queries" instead of Aggregates).

If you want to create an IN with the criteria the user has selected, you need to:

  1. Create the SQL list of criteria in your code (i.e. add single quotes around each term in case of text, seperate them by commas etc.);
  2. Add an Input Parameter of type Text to the SQL query to pass the "list", and set it's Expand Inline Property to "Yes";
  3. Add the Input Parameter between the parentheses of the IN, like this:
WHERE {MyEntity}.[MyAttribute1] IN (@FilterList)

So, concluding:

  1. Don't try to do any post-processing like filtering in memory. It will cripple your server (especially with large record sets), and the OutSystems Platform is just not suited for it;
  2. Instead, use Aggregates if possible or SQL queries otherwise applying the right filters so the database does the filtering for you;
  3. Retrieve only those records you're going to display, e.g. the number of records in the Table Records used for display.

Kilian Hekhuis wrote:

Hi Bradford,

Though there may be 100K records in the database, unless you also want to export them to an Excel file or the like, there's typically no need to actually retrieve all 100K records. In general, unless you have a very specific use case, all filtering is done in the database, and you retrieve only the records that are filtered (and then only the first few you actually want to display to the user).

If you are using an Aggregate, and you have simple filter criteria (so no SQL "IN"), and a filter criterion can be either present or not, you would typically add a Filter like this:

FilterValue = "" or MyEntity.MyAtribute1 like "%" + FilterValue + "%"

or, if it's an Id of some sort:

FilterValueId = NullIdentifier() or MyEntity.MyAtribute1Id = FilterValueId

If you want to be able to filter on multiple criteria of the same type, i.e. what you'd use a SQL "IN" for you can use the above only if you have a limited amount of criteria (say always max. 3), and you could do something like:

(FilterValue1 = "" or MyEntity.MyAtribute1 like "%" + FilterValue1 + "%") or
(FilterValue2 = "" or MyEntity.MyAtribute1 like "%" + FilterValue2 + "%") or
(FilterValue3 = "" or MyEntity.MyAtribute1 like "%" + FilterValue3 + "%")

However, in most cases (especially with a variable amount of choices, like when the possible selection criteria come from the database), you're stuck with creating a SQL query (a.k.a. "Advanced Query", from back in the days when we also had "Simple Queries" instead of Aggregates).

If you want to create an IN with the criteria the user has selected, you need to:

  1. Create the SQL list of criteria in your code (i.e. add single quotes around each term in case of text, seperate them by commas etc.);
  2. Add an Input Parameter of type Text to the SQL query to pass the "list", and set it's Expand Inline Property to "Yes";
  3. Add the Input Parameter between the parentheses of the IN, like this:
WHERE {MyEntity}.[MyAttribute1] IN (@FilterList)

So, concluding:

  1. Don't try to do any post-processing like filtering in memory. It will cripple your server (especially with large record sets), and the OutSystems Platform is just not suited for it;
  2. Instead, use Aggregates if possible or SQL queries otherwise applying the right filters so the database does the filtering for you;
  3. Retrieve only those records you're going to display, e.g. the number of records in the Table Records used for display.

Thank you for your response, but I am not trying to do in-memory filtering.

I also understand how the general filtering mechanism works and doesn't really support a variable number of filter criteria without moving to an Advanced or Custom SQL.

I do plan to do all filtering in the database by the use an Advanced or Custom SQL.

At the initial read I do not know what the search criteria is.

I want to be able to derive the search criteria value from the initially returned records.

Once the search criteria is displayed any selection will go back to the server for a filtered view.

I just want to a select distinct on a few columns of my original result set without having to go back to the database.

If i go back to the database for the distinct column values I will need to recreate the complicated filtering SQL in more than one place.

My use case is to not show filtering criteria that will not reduce the currently displayed records.

Thanks

Solution

Hi Bradford,

I see, I misunderstood your use case. So you want to make filters available to the user that are based on the unique occurences of certain Attributes?

If that's the case, I think you still have to duplicate the SQL and create a version that only selects that specific column, and use group by or distinct to get the unique occurences. If you use the trick with Expand Inline Query Parameters, you can have a single SQL Query you can call a number of times for the different filter Attributes, and build your lists of filter values that way.



Solution

Kilian Hekhuis wrote:

Hi Bradford,

I see, I misunderstood your use case. So you want to make filters available to the user that are based on the unique occurences of certain Attributes?



If that's the case, I think you still have to duplicate the SQL and create a version that only selects that specific column, and use group by or distinct to get the unique occurences. If you use the trick with Expand Inline Query Parameters, you can have a single SQL Query you can call a number of times for the different filter Attributes, and build your lists of filter values that way.



Thanks for your clarification, I will attempt this approach.


Ok, let me know if you bump into anything.