SmartGWT.mobile AdvancedCriteria

[bda@celerity.be]

Any AdvancedCriteria equivalent in mobile version ? I would like to build a Criteria of the type "A and B and (C or D)" where
C and D are of the type "fieldName ICONTAINS someValue".

Tried this, but it does not combine operations as I expect:

Criterion searchCriteria = new Criterion();
if (searchString.length()>0){
   Criterion c[] = new Criterion[SEARCHABLE_FIELDS.length];
   for (int i=0; i<c.length;i++){
      c[i] = new Criterion(SEARCHABLE_FIELDS[i],OperatorId.ICONTAINS,searchString);
   }
   searchCriteria.addCriteria(new Criterion(OperatorId.OR,c));
}
    	
Criterion ownerCriterion = new Criterion(ownerItem.getName(),OperatorId.EQUALS,ownerItem.getValue());
Criterion nonfinalCriterion = new Criterion(finalItem.getName(),OperatorId.EQUALS,Boolean.FALSE);

Criteria criteria = new Criteria();
criteria.addCriteria(ownerCriterion);
criteria.addCriteria(nonfinalCriterion);
criteria.addCriteria(searchCriteria);

[bda@celerity.be]

Actually Criterion just seems not to be used at all ...

[Isomorphic]

Can you provide more details - how did you use this criteria, what happened, what did you expect?

[bda@celerity.be]

I think the problem comes from the fact that adding a criterion using the Class Criterion does not work.

The following code works well:

Criteria criteria = new Criteria();
criteria.addCriteria(ownerItem.getName(),ownerItem.getValue());
tableView.invalidateCache();
tableView.fetchData(criteria);

In the log you can see that the criteria is indeed applied (criteria: {person_samCode:"ygo"} values: {person_samCode:"ygo"}):

=== 2012-12-14 11:35:45,435 [08-4] INFO  RequestContext - URL: '/isomorphic/DataSourceLoader', User-Agent: 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_7_5) AppleWebKit/537.11 (KHTML, like Gecko) Chrome/23.0.1271.97 Safari/537.11': Safari with Accept-Encoding header
=== 2012-12-14 11:35:45,441 [08-3] INFO  RequestContext - URL: '/isomorphic/DataSourceLoader', User-Agent: 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_7_5) AppleWebKit/537.11 (KHTML, like Gecko) Chrome/23.0.1271.97 Safari/537.11': Safari with Accept-Encoding header
=== 2012-12-14 11:35:45,443 [08-4] INFO  Compression - /isomorphic/DataSourceLoader: 2268 -> 474 bytes
=== 2012-12-14 11:35:45,446 [08-3] INFO  Compression - /isomorphic/DataSourceLoader: 816 -> 271 bytes
=== 2012-12-14 11:35:46,209 [08-3] INFO  RequestContext - URL: '/isomorphic/RESTHandler', User-Agent: 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_7_5) AppleWebKit/537.11 (KHTML, like Gecko) Chrome/23.0.1271.97 Safari/537.11': Safari with Accept-Encoding header
=== 2012-12-14 11:35:46,210 [08-3] DEBUG RestRequestParser - Parameter:'_operationType'. Value:'fetch'.
=== 2012-12-14 11:35:46,210 [08-3] DEBUG RestRequestParser - Parameter:'person_samCode'. Value:'ygo'.
=== 2012-12-14 11:35:46,210 [08-3] DEBUG RestRequestParser - Parameter:'_dataSource'. Value:'ui_grid_prospects'.
=== 2012-12-14 11:35:46,210 [08-3] DEBUG RestRequestParser - Parameter:'isc_metaDataPrefix'. Value:'_'.
=== 2012-12-14 11:35:46,211 [08-3] INFO  RESTHandler - Performing 1 operation(s)
=== 2012-12-14 11:35:46,212 [08-3] DEBUG AppBase - [builtinApplication.ui_grid_prospects_fetch] No userTypes defined, allowing anyone access to all operations for this application
=== 2012-12-14 11:35:46,212 [08-3] DEBUG AppBase - [builtinApplication.ui_grid_prospects_fetch] No public zero-argument method named '_ui_grid_prospects_fetch' found, performing generic datasource operation
=== 2012-12-14 11:35:46,212 [08-3] INFO  SQLDataSource - [builtinApplication.ui_grid_prospects_fetch] Performing fetch operation with
	criteria: {person_samCode:"ygo"}	values: {person_samCode:"ygo"}
=== 2012-12-14 11:35:46,213 [08-3] INFO  SQLDataSource - [builtinApplication.ui_grid_prospects_fetch] derived query: SELECT $defaultSelectClause FROM $defaultTableClause WHERE $defaultWhereClause
=== 2012-12-14 11:35:46,213 [08-3] INFO  SQLDataSource - [builtinApplication.ui_grid_prospects_fetch] Executing SQL query on 'Mysql': SELECT ui_grid_prospects._statusAge, ui_grid_prospects._temperature, ui_grid_prospects._warnings, ui_grid_prospects.companyName_short, ui_grid_prospects.countryCode, ui_grid_prospects.icon, ui_grid_prospects.id, ui_grid_prospects.name_short, ui_grid_prospects.person_samCode, ui_grid_prospects.sectorGroupName, ui_grid_prospects.sectorName, ui_grid_prospects.sectorSortkey, ui_grid_prospects.statusBudget_total, ui_grid_prospects.statusBudget_weighted, ui_grid_prospects.statusCurrency, ui_grid_prospects.statusDate_close, ui_grid_prospects.statusIs_final, ui_grid_prospects.statusLikelihood, ui_grid_prospects.statusName FROM ui_grid_prospects WHERE (ui_grid_prospects.person_samCode='ygo')
=== 2012-12-14 11:35:46,236 [08-3] DEBUG PoolableSQLConnectionFactory - [builtinApplication.ui_grid_prospects_fetch] Returning pooled Connection
=== 2012-12-14 11:35:46,236 [08-3] INFO  SQLDriver - [builtinApplication.ui_grid_prospects_fetch] Executing SQL query on 'Mysql': SELECT ui_grid_prospects._statusAge, ui_grid_prospects._temperature, ui_grid_prospects._warnings, ui_grid_prospects.companyName_short, ui_grid_prospects.countryCode, ui_grid_prospects.icon, ui_grid_prospects.id, ui_grid_prospects.name_short, ui_grid_prospects.person_samCode, ui_grid_prospects.sectorGroupName, ui_grid_prospects.sectorName, ui_grid_prospects.sectorSortkey, ui_grid_prospects.statusBudget_total, ui_grid_prospects.statusBudget_weighted, ui_grid_prospects.statusCurrency, ui_grid_prospects.statusDate_close, ui_grid_prospects.statusIs_final, ui_grid_prospects.statusLikelihood, ui_grid_prospects.statusName FROM ui_grid_prospects WHERE (ui_grid_prospects.person_samCode='ygo')
=== 2012-12-14 11:35:46,298 [08-3] INFO  DSResponse - [builtinApplication.ui_grid_prospects_fetch] DSResponse: List with 93 items
=== 2012-12-14 11:35:46,299 [08-3] DEBUG RPCManager - Content type for RPC transaction: text/html; charset=UTF-8
=== 2012-12-14 11:35:46,300 [08-3] DEBUG RPCManager - non-DMI response, dropExtraFields: false

If you now change to the following expectedly equivalent code, which uses Criterion class instead of directly defining the criteria at the Criteria class constructor:

Criteria criteria = new Criteria();
criteria.addCriteria(new Criterion(ownerItem.getName(),OperatorId.EQUALS,ownerItem.getValue()));
tableView.invalidateCache();
tableView.fetchData(criteria);

Then the criterion is not applied as shown in the following log ( criteria: {} values: {}):

=== 2012-12-14 11:43:11,575 [86-6] INFO  RequestContext - URL: '/isomorphic/DataSourceLoader', User-Agent: 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_7_5) AppleWebKit/537.11 (KHTML, like Gecko) Chrome/23.0.1271.97 Safari/537.11': Safari with Accept-Encoding header
=== 2012-12-14 11:43:11,581 [86-4] INFO  RequestContext - URL: '/isomorphic/DataSourceLoader', User-Agent: 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_7_5) AppleWebKit/537.11 (KHTML, like Gecko) Chrome/23.0.1271.97 Safari/537.11': Safari with Accept-Encoding header
=== 2012-12-14 11:43:11,585 [86-6] INFO  Compression - /isomorphic/DataSourceLoader: 2268 -> 474 bytes
=== 2012-12-14 11:43:11,587 [86-4] INFO  Compression - /isomorphic/DataSourceLoader: 816 -> 271 bytes
=== 2012-12-14 11:43:12,386 [86-4] INFO  RequestContext - URL: '/isomorphic/RESTHandler', User-Agent: 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_7_5) AppleWebKit/537.11 (KHTML, like Gecko) Chrome/23.0.1271.97 Safari/537.11': Safari with Accept-Encoding header
=== 2012-12-14 11:43:12,386 [86-4] DEBUG RestRequestParser - Parameter:'_operationType'. Value:'fetch'.
=== 2012-12-14 11:43:12,386 [86-4] DEBUG RestRequestParser - Parameter:'_dataSource'. Value:'ui_grid_prospects'.
=== 2012-12-14 11:43:12,386 [86-4] DEBUG RestRequestParser - Parameter:'isc_metaDataPrefix'. Value:'_'.
=== 2012-12-14 11:43:12,387 [86-4] INFO  RESTHandler - Performing 1 operation(s)
=== 2012-12-14 11:43:12,388 [86-4] DEBUG AppBase - [builtinApplication.ui_grid_prospects_fetch] No userTypes defined, allowing anyone access to all operations for this application
=== 2012-12-14 11:43:12,388 [86-4] DEBUG AppBase - [builtinApplication.ui_grid_prospects_fetch] No public zero-argument method named '_ui_grid_prospects_fetch' found, performing generic datasource operation
=== 2012-12-14 11:43:12,388 [86-4] INFO  SQLDataSource - [builtinApplication.ui_grid_prospects_fetch] Performing fetch operation with
	criteria: {}	values: {}
=== 2012-12-14 11:43:12,389 [86-4] INFO  SQLWhereClause - [builtinApplication.ui_grid_prospects_fetch] empty condition
=== 2012-12-14 11:43:12,389 [86-4] INFO  SQLDataSource - [builtinApplication.ui_grid_prospects_fetch] derived query: SELECT $defaultSelectClause FROM $defaultTableClause WHERE $defaultWhereClause
=== 2012-12-14 11:43:12,389 [86-4] INFO  SQLDataSource - [builtinApplication.ui_grid_prospects_fetch] Executing SQL query on 'Mysql': SELECT ui_grid_prospects._statusAge, ui_grid_prospects._temperature, ui_grid_prospects._warnings, ui_grid_prospects.companyName_short, ui_grid_prospects.countryCode, ui_grid_prospects.icon, ui_grid_prospects.id, ui_grid_prospects.name_short, ui_grid_prospects.person_samCode, ui_grid_prospects.sectorGroupName, ui_grid_prospects.sectorName, ui_grid_prospects.sectorSortkey, ui_grid_prospects.statusBudget_total, ui_grid_prospects.statusBudget_weighted, ui_grid_prospects.statusCurrency, ui_grid_prospects.statusDate_close, ui_grid_prospects.statusIs_final, ui_grid_prospects.statusLikelihood, ui_grid_prospects.statusName FROM ui_grid_prospects WHERE ('1'='1')
=== 2012-12-14 11:43:12,413 [86-4] DEBUG PoolableSQLConnectionFactory - [builtinApplication.ui_grid_prospects_fetch] Returning pooled Connection
=== 2012-12-14 11:43:12,414 [86-4] INFO  SQLDriver - [builtinApplication.ui_grid_prospects_fetch] Executing SQL query on 'Mysql': SELECT ui_grid_prospects._statusAge, ui_grid_prospects._temperature, ui_grid_prospects._warnings, ui_grid_prospects.companyName_short, ui_grid_prospects.countryCode, ui_grid_prospects.icon, ui_grid_prospects.id, ui_grid_prospects.name_short, ui_grid_prospects.person_samCode, ui_grid_prospects.sectorGroupName, ui_grid_prospects.sectorName, ui_grid_prospects.sectorSortkey, ui_grid_prospects.statusBudget_total, ui_grid_prospects.statusBudget_weighted, ui_grid_prospects.statusCurrency, ui_grid_prospects.statusDate_close, ui_grid_prospects.statusIs_final, ui_grid_prospects.statusLikelihood, ui_grid_prospects.statusName FROM ui_grid_prospects WHERE ('1'='1')
=== 2012-12-14 11:43:12,547 [86-4] INFO  DSResponse - [builtinApplication.ui_grid_prospects_fetch] DSResponse: List with 264 items
=== 2012-12-14 11:43:12,548 [86-4] DEBUG RPCManager - Content type for RPC transaction: text/html; charset=UTF-8
=== 2012-12-14 11:43:12,549 [86-4] DEBUG RPCManager - non-DMI response, dropExtraFields: false

I hope this will help you to better understand.

Thanks, Ben

[bda@celerity.be]

Even simpler:

This works (meaning WHERE clause contains the criteria in the SQL call):

tableView.fetchData(new Criteria(ownerItem.getName(),ownerItem.getValue()));

This does not work (no WHERE clause in the SQL call):

tableView.fetchData(new Criterion(ownerItem.getName(),OperatorId.EQUALS,ownerItem.getValue()));

Additional question: what is the purpose of "markAdvancedCriteria" on Criterion?

[bda@celerity.be]

Any follow-up on this please ?

[Isomorphic]

This is fixed for tomorrow's builds, but note, the same convenience constructors exist on AdvancedCriteria, which is a bit clearer. The problem is that a Criterion serialized to JSON can be ambiguous as to whether it's advanced or simple Criteria. Creating an AdvancedCriteria or using markAdvancedCriteria makes it explicit.

[bda@celerity.be]

Thanks for your fix tentative... AdvancedCriteria / Criterion are now formed in the SQL BUT there is a problem. For any reason, you are adding a 'true' clause of the form '1'='1' to any term of the criteria. This does not cause any problem if the operator is AND, however when the operator is OR, adding true to the list of criteria combined with a OR obviously always returns true.

Here is an example of an AdvancedCriteria supposed to represent a test of the form:
field1=value1 AND field2=value2 AND (field3 contains value3 OR field4 contains value4 OR field5 contains value5)

which is automatically transformed in the SQL as:
'1'='1' AND field1=value1 AND field2=value2 AND ('1'='1' OR field3 contains value3 OR field4 contains value4 OR field5 contains value5)

1) I don't know why you are adding those '1'='1' terms?
2) Adding '1'='1' in the OR case changes the purpose of the test

	criteria: {criteria:[{},{criteria:[{fieldName:"company_nameShort",operator:"iContains",value:"Pilot"},{fieldName:"sectorName",operator:"iContains",value:"Pilot"},{fieldName:"name_short",operator:"iContains",value:"Pilot"},{fieldName:"statusName",operator:"iContains",value:"Pilot"},{fieldName:"person_samCode",operator:"iContains",value:"Pilot"}],operator:"or"},{fieldName:"person_samCode",operator:"equals",value:"cdr"},{fieldName:"statusIs_final",operator:"equals",value:"false"}],operator:"and",_constructor:"AdvancedCriteria"}	values: {criteria:[{},{criteria:[{fieldName:"company_nameShort",operator:"iContains",value:"Pilot"},{fieldName:"sectorName",operator:"iContains",value:"Pilot"},{fieldName:"name_short",operator:"iContains",value:"Pilot"},{fieldName:"statusName",operator:"iContains",value:"Pilot"},{fieldName:"person_samCode",operator:"iContains",value:"Pilot"}],operator:"or"},{fieldName:"person_samCode",operator:"equals",value:"cdr"},{fieldName:"statusIs_final",operator:"equals",value:"false"}],operator:"and",_constructor:"AdvancedCriteria"}
=== 2012-12-18 21:22:25,540 [0-10] INFO  SQLDataSource - [builtinApplication.ui_grid_prospects_fetch] derived query: SELECT $defaultSelectClause FROM $defaultTableClause WHERE $defaultWhereClause
=== 2012-12-18 21:22:25,540 [0-10] DEBUG SQLDataSource - [builtinApplication.ui_grid_prospects_fetch] Executing row count query: SELECT COUNT(*) FROM $defaultTableClause WHERE $defaultWhereClause
=== 2012-12-18 21:22:25,541 [0-10] DEBUG SQLDataSource - [builtinApplication.ui_grid_prospects_fetch] Eval'd row count query: SELECT COUNT(*) FROM ui_grid_prospects WHERE ('1'='1' AND ('1'='1' OR (ui_grid_prospects.sectorName LIKE '%pilot%' AND ui_grid_prospects.sectorName IS NOT NULL) OR (ui_grid_prospects.name_short LIKE '%pilot%' AND ui_grid_prospects.name_short IS NOT NULL) OR (ui_grid_prospects.statusName LIKE '%pilot%' AND ui_grid_prospects.statusName IS NOT NULL) OR (ui_grid_prospects.person_samCode LIKE '%pilot%' AND ui_grid_prospects.person_samCode IS NOT NULL)) AND (ui_grid_prospects.person_samCode = 'cdr' AND ui_grid_prospects.person_samCode IS NOT NULL) AND (ui_grid_prospects.statusIs_final = 0 AND ui_grid_prospects.statusIs_final IS NOT NULL))
=== 2012-12-18 21:22:25,541 [0-10] DEBUG PoolableSQLConnectionFactory - [builtinApplication.ui_grid_prospects_fetch] Executing pingTest 'select 1 from dual' on connection 1861478771
=== 2012-12-18 21:22:25,541 [0-10] DEBUG SQLConnectionManager - [builtinApplication.ui_grid_prospects_fetch] Returning borrowed connection '1861478771'
=== 2012-12-18 21:22:25,541 [0-10] DEBUG SQLDriver - [builtinApplication.ui_grid_prospects_fetch] About to execute SQL query in 'Mysql' using connection '1861478771'
=== 2012-12-18 21:22:25,541 [0-10] INFO  SQLDriver - [builtinApplication.ui_grid_prospects_fetch] Executing SQL query on 'Mysql': SELECT COUNT(*) FROM ui_grid_prospects WHERE ('1'='1' AND ('1'='1' OR (ui_grid_prospects.sectorName LIKE '%pilot%' AND ui_grid_prospects.sectorName IS NOT NULL) OR (ui_grid_prospects.name_short LIKE '%pilot%' AND ui_grid_prospects.name_short IS NOT NULL) OR (ui_grid_prospects.statusName LIKE '%pilot%' AND ui_grid_prospects.statusName IS NOT NULL) OR (ui_grid_prospects.person_samCode LIKE '%pilot%' AND ui_grid_prospects.person_samCode IS NOT NULL)) AND (ui_grid_prospects.person_samCode = 'cdr' AND ui_grid_prospects.person_samCode IS NOT NULL) AND (ui_grid_prospects.statusIs_final = 0 AND ui_grid_prospects.statusIs_final IS NOT NULL))
=== 2012-12-18 21:22:25,665 [0-10] DEBUG SQLDataSource - [builtinApplication.ui_grid_prospects_fetch] Using SQL Limit query
=== 2012-12-18 21:22:25,665 [0-10] DEBUG SQLDataSource - [builtinApplication.ui_grid_prospects_fetch] SQL windowed select rows 0->100, result size 100. Query: SELECT ui_grid_prospects._statusAge, ui_grid_prospects._temperature, ui_grid_prospects._warnings, ui_grid_prospects.companyName_short, ui_grid_prospects.countryCode, ui_grid_prospects.icon, ui_grid_prospects.id, ui_grid_prospects.name_short, ui_grid_prospects.person_samCode, ui_grid_prospects.sectorGroupName, ui_grid_prospects.sectorName, ui_grid_prospects.sectorSortkey, ui_grid_prospects.statusBudget_total, ui_grid_prospects.statusBudget_weighted, ui_grid_prospects.statusCurrency, ui_grid_prospects.statusDate_close, ui_grid_prospects.statusIs_final, ui_grid_prospects.statusLikelihood, ui_grid_prospects.statusName FROM ui_grid_prospects WHERE ('1'='1' AND ('1'='1' OR (ui_grid_prospects.sectorName LIKE '%pilot%' AND ui_grid_prospects.sectorName IS NOT NULL) OR (ui_grid_prospects.name_short LIKE '%pilot%' AND ui_grid_prospects.name_short IS NOT NULL) OR (ui_grid_prospects.statusName LIKE '%pilot%' AND ui_grid_prospects.statusName IS NOT NULL) OR (ui_grid_prospects.person_samCode LIKE '%pilot%' AND ui_grid_prospects.person_samCode IS NOT NULL)) AND (ui_grid_prospects.person_samCode = 'cdr' AND ui_grid_prospects.person_samCode IS NOT NULL) AND (ui_grid_prospects.statusIs_final = 0 AND ui_grid_prospects.statusIs_final IS NOT NULL)) LIMIT 0, 100

This is the code that generates the AdvancedCriteria

    	AdvancedCriteria criteria = new AdvancedCriteria();
    	
    	if (searchItem!=null){
        	String searchString =  searchItem.getValueAsString();
        	if (searchString!=null&&searchString.length()>0){
            	Criterion c[] = new Criterion[searchableFields.length];
            	for (int i=0; i<c.length;i++){
            		c[i] = new Criterion(searchableFields[i],OperatorId.ICONTAINS,searchString);
            	}
            	criteria.addCriteria(new AdvancedCriteria(OperatorId.OR,c));
        	}
    	}
    	
    	if (ownerItem!=null){
        	String owner = ownerItem.getValueAsString();
        	if (owner!=null&&owner.length()>0){
        		criteria.addCriteria(new AdvancedCriteria(ownerItem.getName(),OperatorId.EQUALS,ownerItem.getValue()));
        	}
    	}
    	
    	if (activeItem!=null){
        	boolean active = (Boolean)activeItem.getValue();
        	criteria.addCriteria(new AdvancedCriteria(activeItem.getName(),OperatorId.EQUALS,!active));
    	}
    	
    	tableView.fetchData(criteria);

[Isomorphic]

Generally this happens when we get nonsense criteria. You can see in the logs there's an empty Criterion:

criteria:[{},{fieldName:"person_samCode",operator:"equals",value:"cdr"}

At first glance, your code seems fine and shouldn't have generated the nonsense criterion. We'll look into this.

We'll also see if we can improve the handling for nonsense criteria - as you say, in an OR clause, we should probably generate 1=0 or a similar hardcoded "false" value to avoid affecting the rest of the clause.

[Isomorphic]

The generation of bad criteria should be fixed for tomorrow's build.

[bda@celerity.be]

tested in 2012-12-21 (end of the world version ;-) ) and this is not fixed. For still the same query, here follows the generated SQL from the log which still contains 1=1 in the OR group:

SELECT ui_grid_prospects._statusAge, ui_grid_prospects._temperature, ui_grid_prospects._warnings, ui_grid_prospects.companyName_short, ui_grid_prospects.countryCode, ui_grid_prospects.icon, ui_grid_prospects.id, ui_grid_prospects.name_short, ui_grid_prospects.person_samCode, ui_grid_prospects.sectorGroupName, ui_grid_prospects.sectorName, ui_grid_prospects.sectorSortkey, ui_grid_prospects.statusBudget_total, ui_grid_prospects.statusBudget_weighted, ui_grid_prospects.statusCurrency, ui_grid_prospects.statusDate_close, ui_grid_prospects.statusIs_final, ui_grid_prospects.statusLikelihood, ui_grid_prospects.statusName FROM ui_grid_prospects WHERE (('1'='1' OR (ui_grid_prospects.sectorName LIKE '%pilot%' AND ui_grid_prospects.sectorName IS NOT NULL) OR (ui_grid_prospects.name_short LIKE '%pilot%' AND ui_grid_prospects.name_short IS NOT NULL) OR (ui_grid_prospects.statusName LIKE '%pilot%' AND ui_grid_prospects.statusName IS NOT NULL) OR (ui_grid_prospects.person_samCode LIKE '%pilot%' AND ui_grid_prospects.person_samCode IS NOT NULL)) AND (ui_grid_prospects.person_samCode = 'cdr' AND ui_grid_prospects.person_samCode IS NOT NULL) AND (ui_grid_prospects.statusIs_final = 0 AND ui_grid_prospects.statusIs_final IS NOT NULL))

[Isomorphic]

What we fixed was generation of bad criteria when using the client-side APIs (new AdvancedCriteria, addCriteria(), etc, as you showed). For now, if you create bad criteria some other way, yes you would still get SQL that isn't ideal. But you can just avoid passing bad criteria, as this only happens with clearly invalid criteria.

[bda@celerity.be]

What do you mean by bad criteria? I am still using the same code as the one posted before which you said it "shouldn't have generated the nonsense criterion" ? The non-sense 1=1 in the OR clause is not added by my code ...

Let me copy a more comprehensive log, you will see that the criteria looks fine (first line) but the translation to SQL is wrong (last line) :

	criteria: {criteria:[{criteria:[{fieldName:"company_nameShort",operator:"iContains",value:"pilot"},{fieldName:"sectorName",operator:"iContains",value:"pilot"},{fieldName:"name_short",operator:"iContains",value:"pilot"},{fieldName:"statusName",operator:"iContains",value:"pilot"},{fieldName:"person_samCode",operator:"iContains",value:"pilot"}],operator:"or"},{fieldName:"person_samCode",operator:"equals",value:"cdr"},{fieldName:"statusIs_final",operator:"equals",value:"false"}],operator:"and",_constructor:"AdvancedCriteria"}	values: {criteria:[{criteria:[{fieldName:"company_nameShort",operator:"iContains",value:"pilot"},{fieldName:"sectorName",operator:"iContains",value:"pilot"},{fieldName:"name_short",operator:"iContains",value:"pilot"},{fieldName:"statusName",operator:"iContains",value:"pilot"},{fieldName:"person_samCode",operator:"iContains",value:"pilot"}],operator:"or"},{fieldName:"person_samCode",operator:"equals",value:"cdr"},{fieldName:"statusIs_final",operator:"equals",value:"false"}],operator:"and",_constructor:"AdvancedCriteria"}
=== 2012-12-21 22:54:55,471 [l0-0] INFO  SQLDataSource - [builtinApplication.ui_grid_prospects_fetch] derived query: SELECT $defaultSelectClause FROM $defaultTableClause WHERE $defaultWhereClause
=== 2012-12-21 22:54:55,471 [l0-0] INFO  SQLDataSource - [builtinApplication.ui_grid_prospects_fetch] Executing SQL query on 'Mysql': SELECT ui_grid_prospects._statusAge, ui_grid_prospects._temperature, ui_grid_prospects._warnings, ui_grid_prospects.companyName_short, ui_grid_prospects.countryCode, ui_grid_prospects.icon, ui_grid_prospects.id, ui_grid_prospects.name_short, ui_grid_prospects.person_samCode, ui_grid_prospects.sectorGroupName, ui_grid_prospects.sectorName, ui_grid_prospects.sectorSortkey, ui_grid_prospects.statusBudget_total, ui_grid_prospects.statusBudget_weighted, ui_grid_prospects.statusCurrency, ui_grid_prospects.statusDate_close, ui_grid_prospects.statusIs_final, ui_grid_prospects.statusLikelihood, ui_grid_prospects.statusName FROM ui_grid_prospects WHERE (('1'='1' OR (ui_grid_prospects.sectorName LIKE '%pilot%' AND ui_grid_prospects.sectorName IS NOT NULL) OR (ui_grid_prospects.name_short LIKE '%pilot%' AND ui_grid_prospects.name_short IS NOT NULL) OR (ui_grid_prospects.statusName LIKE '%pilot%' AND ui_grid_prospects.statusName IS NOT NULL) OR (ui_grid_prospects.person_samCode LIKE '%pilot%' AND ui_grid_prospects.person_samCode IS NOT NULL)) AND (ui_grid_prospects.person_samCode = 'cdr' AND ui_grid_prospects.person_samCode IS NOT NULL) AND (ui_grid_prospects.statusIs_final = 0 AND ui_grid_prospects.statusIs_final IS NOT NULL))
=== 2012-12-21 22:54:55,472 [l0-0] DEBUG PoolableSQLConnectionFactory - [builtinApplication.ui_grid_prospects_fetch] Executing pingTest 'select 1 from dual' on connection 1456567023
=== 2012-12-21 22:54:55,472 [l0-0] DEBUG SQLConnectionManager - [builtinApplication.ui_grid_prospects_fetch] Returning borrowed connection '1456567023'
=== 2012-12-21 22:54:55,472 [l0-0] DEBUG SQLDriver - [builtinApplication.ui_grid_prospects_fetch] About to execute SQL query in 'Mysql' using connection '1456567023'
=== 2012-12-21 22:54:55,472 [l0-0] INFO  SQLDriver - [builtinApplication.ui_grid_prospects_fetch] Executing SQL query on 'Mysql': SELECT ui_grid_prospects._statusAge, ui_grid_prospects._temperature, ui_grid_prospects._warnings, ui_grid_prospects.companyName_short, ui_grid_prospects.countryCode, ui_grid_prospects.icon, ui_grid_prospects.id, ui_grid_prospects.name_short, ui_grid_prospects.person_samCode, ui_grid_prospects.sectorGroupName, ui_grid_prospects.sectorName, ui_grid_prospects.sectorSortkey, ui_grid_prospects.statusBudget_total, ui_grid_prospects.statusBudget_weighted, ui_grid_prospects.statusCurrency, ui_grid_prospects.statusDate_close, ui_grid_prospects.statusIs_final, ui_grid_prospects.statusLikelihood, ui_grid_prospects.statusName FROM ui_grid_prospects WHERE (('1'='1' OR (ui_grid_prospects.sectorName LIKE '%pilot%' AND ui_grid_prospects.sectorName IS NOT NULL) OR (ui_grid_prospects.name_short LIKE '%pilot%' AND ui_grid_prospects.name_short IS NOT NULL) OR (ui_grid_prospects.statusName LIKE '%pilot%' AND ui_grid_prospects.statusName IS NOT NULL) OR (ui_grid_prospects.person_samCode LIKE '%pilot%' AND ui_grid_prospects.person_samCode IS NOT NULL)) AND (ui_grid_prospects.person_samCode = 'cdr' AND ui_grid_prospects.person_samCode IS NOT NULL) AND (ui_grid_prospects.statusIs_final = 0 AND ui_grid_prospects.statusIs_final IS NOT NULL))

Please tell me what is wrong now in my code that could introduce a 'bad-criteria' and do you mean by that ?

Thanks

[Isomorphic]

Previously the bad criteria was an empty object ({}). This is now different.

You need to look over this criteria as compared to your DataSource definition. The types of things that become 1=1 are attempts to apply criteria to a field that isn't declared in the DataSource, or criteria that use operators that don't apply to the field type.

[bda@celerity.be]

Ok I have found the problem, there was indeed a typo in one of the field names used in the OR group. May be this should throw an Exception rather than generating non-sense criteria.

Many thanks for your help and sorry for not having found this sooner, Ben.