Sorting on derived (calculated) columns in ListGrid

[pkelley]

I have a DataSource for my ListGrid that combines data from several tables in a sql database.

I noticed if I provide the table name for each of the fields I can sort by those columns in the ListGrid.

Is there a way to do this for the derived (calculated) fields that get their value from something done in the SQL and not directly from the database?

One example of this would be sorting by averages returned by a datasource that is setup with a GROUPBY statement. Obviously I cannot point that field to a table name since it doesn't come from a table directly.

Thank you in advance for your help!
Patrick

[Isomorphic]

You can do this by combining dataSourceField.customSelectExpression and dataSourceField.customCriteriaExpression - the latter is what will appear in the generated where clause, so, to allow filtering on averages you need something like customCriteriaExpression="$value > AVG(columnName)" - or a more complex expression if you need to make the operator dynamic (the operator is available as the Velocity variable $criteriaOperator).

Note that customCriteriaExpression is a 3.0 feature, however prior to 3.0 you can get the same effect by customizing an operationBinding to exclude the field from SQL generation via operationBinding.customCriteriaFields (see QuickStart sections on SQL templating). The drawback is you'll just have to repeat such customizations if you have multiple fetch operations.

[pkelley]

Thanks for the quick reply!

Sorry, if I am just missing it, but this seems to help with filtering, but what I really want to do is enable sorting that works for these fields. It seems like there is an extra step to make that work that I can't figure out.

Can that be done?

[Isomorphic]

Sorry, you're correct, those instructions were about custom filtering. To do custom sorting you'll need to set the field to customSQL="true" instead, then customized both the <selectClause> and the <orderClause> to use your custom expression.

[pkelley]

That makes sense, but what about the case where the default ordering does not include the derived field, but you want to allow the user to sort by clicking on the derived field column heading. I believe that does not do the sorting.

I am wondering if there is a way to make this sort by column heading work for these derived fields.

[Isomorphic]

Keep in mind the SQL templates are Velocity - that means it allows conditionals. So, you can check whether dsRequest.sortBy contains your special field either in Velocity (if you're proficient in it) or in Java and then add a boolean result to the dsRequest via addToTemplateContext() so that you can do a simple #if in Velocity to use your custom orderClause or retain the built-in one.