CustomSQL with IN <fieldValues>

[mpretorian]

Hi,

Suppose I have a form and a multiple select item field that is used to create a criteria to fetch data for a grid. The simple/advanced criteria looks like:

criteria:{
	countryId:[
		1,
		2
	]
}

criteria:{
	operator:"and",
	_constructor:"AdvancedCriteria",
	criteria:[
		{
			fieldName:"countryId",
			operator:"inSet",
			value:[
				1,
				2
			]
		}
	]
}

Using such tables definitions:
table1: id, ...
table2: id, countryId
One-to-multi relation from table1 to table2.

1) How can I get on the server generated such queries:

select * from table1
where table1.id in (
 select table2.id from table2 
 where table1.id = table2.id 
 and table2.countryId in (1,2)
)

select * from table1
where exists (
 select 1 from table2 
 where table1.id = table2.id 
 and table2.countryId in (1,2)
)

2) In the alternative of using customSQL to generated the whole "table1.id in ( select ... )" or the "exists ( ... )" how can I pass to the custom query the values of countryId field? Using $criteria.countryId (or advancedCriteria) generates a one string value "[1,2]" not suitable for IN expression.

Thanks,
Mihnea
PS: I attached a changed version of SQLIncludeFrom sample that sends multiple values for countryId field. But that example only creates joins to the included tables, so it seems it is not useful.

[Isomorphic]

The criterion.value as represented in Java is a java.util.List. Velocity can iterate over a List using #foreach (see Velocity reference).

An alternative would be to access the Criterion in Java using APIs on DSRequest, put together the SQL string in Java code, and make it available to your template via DSRequest.addToTemplateContext().

[DevMo]

I found that I was able to pass Arrays (String[] & Integer[]) directly to Velocity, as follows:

<whereClause>
...and 
	org_id IN ( #foreach($org_id in $criteria.ORG_ID_ARRAY)'$org_id'#if( $velocityCount != $criteria.ORG_ID_ARRAY.size()),#end #end) 
and
	status IN ( #foreach($status in $criteria.STATUS_ARRAY)'$status'#if( $velocityCount != $criteria.STATUS_ARRAY.size()),#end #end ) 
and 
	($defaultWhereClause)
</whereClause>

The $velocityCount checks whether to add a comma between IN elements, or not if it's the last item.

I hope this example code is helpful.