customSelectExpression using Velocity

[pmihnea]

Can customSelectExpression use a Velocity expression?
I'd like to generate the SQL for a field a where clause depending on the value of the field.

[Isomorphic]

At the moment it's not a dynamic expression. Can you explain your use case and show the SQL you're trying to generate?

[pmihnea]

I use an SQLDataSource that receives an advance criteria (because I have used also a DateRangeItem) and I have a field named "field1" for which I'd like to generate an SQL based on its value like:
#if ($criteria.field1 == "ALL")
FIELD2 > 0 and FIELD3>0
#elseif ($criteria.field1 == "ANY")
FIELD2 > 0 or FIELD3>0
#else
FIELD2 = 0 and FIELD3=0
#end

Writing these line I realized that with an advance criteria the "$criteria.field1" will not worked, because I've tried already to use the same kind of velocity expression in the where clause of the custom sql.

If the advance criteria looks like:
field10=100
or field1="ANY"
and field20=200
or field30=300
I cannot write a custom sql to replace only the expression field1="ANY", so I was thinking to use the customSelectExpression for that.

Another idea I had was to intercept in java the Criteria and find the Criterion("field1","=","ANY") and replace it with another criteria that will be used to generate the SQL I need.

[Isomorphic]

The latter approach (using Java) is the best. Add a DMI that changes the criteria so that it is equivalent to the SQL you're trying to write directly. This is also more portable.

[damend]

And is there a best way, how to find a specific criteria of the AdvancedCriterias (client and server side)?

setCriterion on a formItem in client doesn't work for me.

Currently I try something to get and override Criterias like:

AdvancedCriteria critAll = form.getValuesAsAdvancedCriteria()
Map map = cr.getValues();

How can I set this map of criterias back to type of AdvancedCriteria

[Isomorphic]

A Map cannot express AdvancedCriteria. Look at the docs for AdvancedCriteria, it explains what the data structure looks like when translated to Java Collections. You can see the structure as well, as JSON, in the server-side logs when a request comes in.