14.0p SetFilterItem filtering and sorting

[Blama]

Hi Isomorphic,

in this sample (v14.0p_2025-07-19), when you show the filterRow of the ListGrid via right-click and then open the SetFilterItem for the Category-field, you notice that it is not sorted - different to the SetFilterItem for the Category-field in the search form above.

I wondered why that is and started investigating and noticed that the ListGrid-SetFilterItem actually uses the ListGrid DataSource - other than the SearchForm SetFilterItem, which uses supplyCategory (cool that this is automatic - I assume because of the foreignKey in the .ds.xml).

I then noticed the groupBy: category in the RPCRequest-Tab of the Developer Console.
It also includes the ListGrid-criteria in that request, so that I will always only see Category-entries that are actually in my filtered dataset - pretty cool.

My improvement suggestion is to also send an automatic sortBy with the groupBy. This makes the end user experience better and as the database has to already do a GROUP BY, a ORDER BY does not make the request any slower (different to e.g. a ComboBoxItem, where there is no grouping involved).
Depending on DB and count of unique values for the groupBy the order of entries might actually change otherwise - hard to see why for an end user. Of course the developer can define a sort - but in this case the sort by the groupedBy-field is the only useful order.
So IMHO there are only wins by doing this automatically as a default.

Best regards
Blama

[Blama]

Hi Isomorphic,

writing that felt strangely familiar and I suggested it already in the old design thread in #17. Your last answer is in #30 there.
IMHO there is no "natural order of the data" when a GROUP BY is involved.
For the Search Form and the supplyCategory DataSource, which seems already sorted even without a orderBy this might be true, but as soon as GROUP BY (especially on a changing dataset because of including the ListGrid-filter) is involved, there is no natural order of the data anymore.

I also tried with defining sortField/sortDirection myself, but this results in other problems (see below).

Best regards
Blama

[Blama]

Hi Isomorphic,

I changed the ListGrid field definition in the sample like this:

showFilterEditor :true,
fields:[
   {name:"SKU"},
   {name:"itemName"},
   {name:"category", filterEditorProperties: {sortField: "category", sortDirection:"descending"} },
   {name:"units"},
],

Now, if you click "Filter Grid" an then open the "Category"-SetFilterItem in the filterRow, everything is fine. Also, sortField/sortDirection work as expected.

But if you open the "Category"-SetFilterItem directly, this happens:

• There is a pickList, but there is no text for the entries
• The request for the pickList does not have a groupBy
• A fetchData for the ListGrid is triggered

All these are unexpected.

Best regards
Blama

[Isomorphic]

We're not sure why you think the natural order of the data goes away when grouped. Groups are still formed based on the natural order - typically the order in which values were encountered.

We'll check on whether your filterEditorProperties are correct and make it clear how to do this correctly.

[Blama]

Hi Isomorphic,

no, because of the implementation of GROUP BY in the database (hash table of some description), the "natural" order of values is not kept.

See this little example in Postgres (locally or in sqlfiddle.com):

--ordered as expected
WITH base as (SELECT DIV(i, 10) AS v
              FROM generate_series(1, 100) i)
SELECT v
FROM base;

--"random" order because of group by implementation
WITH base as (SELECT DIV(i, 10) AS v
              FROM generate_series(1, 100) i)
SELECT v
FROM base
GROUP BY v;

In the second case the result starts with 3, 1, 4, 9, ..., while in the first case there are all the 0, then the 1, then the 2, ....

It's easy enough now to set the sortField as a developer, but as it does not make the SQL call any longer, I still think this would be a sensible default.



Regarding the other issue, I assume that this is because the SetFilterItem wants to access the Criteria of the ListGrid and somehow unexpectedly triggers a fetchData().

Best regards
Blama

[Blama]

Hi Isomorphic,

the three issues from #3 are still happening using v14.0p_2025-08-27.

Best regards
Blama