Case insensitive filter in ListGrid

[plesenb]

Hi,

I'm using smartGWT Pro 4.0. Can you explain how I can use a case insensitive filter in ListGrid?

I’ve tried to reproduce showcase example http://www.smartclient.com/smartgwt/showcase/#grid_sortfilter_filter. Filter is working correctly (case insensitive) while number of rows less then 75. If rows more then 75 then filter works as case sensitive.

DataSource, name is CustomerView.ds.xml:

<DataSource 
	dbName="Mysql"
	tableName="CustomerView"
	ID="CustomerView"
	serverType="sql"
>
 <operationBindings>
  <operationBinding requiresRole="Customerfetch" operationType="fetch" />
 </operationBindings>
	<fields>
		<field name="CustomerID" type="integer"></field>
		<field name="CustomerFName" length="50" type="text"></field>
		<field name="CustomerLName" length="50" type="text"></field>
		<field name="CustomerTel1" length="50" type="text"></field>
		<field name="CustomerEmail" length="250" type="text"></field>
		<field name="CustomerCanContact" type="integer"></field>
		<field name="TierName" length="100" type="text"></field>
		<field name="CustomerPointBalance" type="integer"></field>
	</fields>
</DataSource>

CustomerView.ds.js:

var CustomerView = {
    allowAdvancedCriteria:true,
    ID:"CustomerView",
    serverType:"sql",
    fields:[
        {
            name:"CustomerID",
            type:"integer",
            primaryKey:true
        },
        {
            name:"CustomerFName",
            length:50,
            type:"text",
            title:"First Name"
        },
        {
            name:"CustomerLName",
            length:50,
            type:"text",
            title:"Last Name",
            sort:true
        },
        {
            name:"CustomerEmail",
            length:250,
            type:"text",
            title:"Email"
        },
        {
            name:"CustomerTel1",
            length:50,
            type:"text",
            title:"Telephone"
        },        
        {
            name:"CustomerPointBalance",
            type:"integer",
            title:"Points"
        },
        {
            name:"CustomerCanContact",
            type:"boolean",
            title:"Can Contact"
        },
        {
            name:"TierName",
            length:100,
            type:"text",
            group:true,
            hidden:true
        }
    ]
}

Creating ListGrid:

private void drawListGrid() {
	// TODO add autoDisplayField to primField
	boolean isNew = listGrid == null;
	if (isNew) {
		listGrid = new ListGrid();
		
		listGrid.setHeight100();
		listGrid.setWidth100();

		listGrid.setCanEdit(false);
		listGrid.setShowFilterEditor(this.autoFetch);
		listGrid.setDataSource(getDataSourceView());
		listGrid.setAlternateRecordStyles(true);
		listGrid.setDataFetchMode(FetchMode.PAGED);

		String fieldWidth = ((int) (100 / getDataSourceView().getFields().length)) + "%";
		List<ListGridField> listGridFields = new ArrayList<ListGridField>();
		for (int i = 0; i < getDataSourceView().getFields().length; i++) {
			final DataSourceField field = getDataSourceView().getFields()[i];
			ListGridField lgf = new ListGridField(field.getName());
			
			listGridFields.add(lgf);
		}

		ListGridField[] lgf = new ListGridField[listGridFields.size()];
		lgf = listGridFields.toArray(lgf);
		listGrid.setFields(lgf);
		this.addMember(listGrid);
		listGrid.setFilterOnKeypress(true);
		listGrid.setAutoFetchData(true);
	}
}

I use following code to fetching data:

public void fetch() {
	if (fetchLocks.isEmpty()) {
		if (listGrid != null) {
			if (extraCriteria.isEmpty()) {
				listGrid.fetchData(criteria, new DSCallback() {
					@Override
					public void execute(DSResponse response, Object rawData, DSRequest request) {
						if (response.getStatus() == 0) {
							Record[] data = response.getData();
							RecordList filteredData = new RecordList(data);
							listGrid.setData(filteredData);
						}
						listGrid.invalidateCache();
					}
				});
			} else {
				getDataSourceView().filterData(criteria, new DSCallback() {
					@Override
					public void execute(DSResponse response, Object rawData, DSRequest request) {
						if (response.getStatus() == 0) {
							Record[] data = response.getData();
							RecordList filteredData = new RecordList();
							for (Record r : data) {
								for (Entry<String, Set<String>> crit : extraCriteria.entrySet()) {
									String val = r.getAttribute(crit.getKey());
									if (val != null) {
										if (crit.getValue().contains(val)) {
											filteredData.add(r);
											break;
										}
									}
								}
							}
							listGrid.setData(filteredData);
						}

					}
				});
			}
		}
	}
}

Console logs:

     [java] === 2014-01-30 13:20:09,855 [l0-4] DEBUG DSRequest - Caching instance 30 of DS CustomerView from DSRequest.getDataSource()
     [java] === 2014-01-30 13:20:09,856 [l0-4] DEBUG RPCManager - Request #1 (DSRequest) payload: {
     [java]     criteria:{
     [java]         CustomerFName:"alex"
     [java]     },
     [java]     operationConfig:{
     [java]         dataSource:"CustomerView",
     [java]         operationType:"fetch",
     [java]         textMatchStyle:"substring"
     [java]     },
     [java]     startRow:0,
     [java]     endRow:75,
     [java]     sortBy:[
     [java]         "CustomerLName"
     [java]     ],
     [java]     componentId:"isc_ListGrid_0",
     [java]     appID:"builtinApplication",
     [java]     operation:"CustomerView_fetch",
     [java]     oldValues:{
     [java]         CustomerFName:"alex"
     [java]     }
     [java] }
     [java] === 2014-01-30 13:20:09,856 [l0-4] DEBUG DeclarativeSecurity - Processing security checks for DataSource null, field null
     [java] === 2014-01-30 13:20:09,856 [l0-4] DEBUG DeclarativeSecurity - DataSource CustomerView is not in the pre-checked list, processing...
     [java] === 2014-01-30 13:20:09,856 [l0-4] DEBUG AppBase - [builtinApplication.CustomerView_fetch] No userTypes defined, allowing anyone access to all opera tions for this application
     [java] === 2014-01-30 13:20:09,857 [l0-4] DEBUG AppBase - [builtinApplication.CustomerView_fetch] No public zero-argument method named '_CustomerView_fetch' found, performing generic datasource operation
     [java] === 2014-01-30 13:20:09,857 [l0-4] INFO  SQLDataSource - [builtinApplication.CustomerView_fetch] Performing fetch operation with
     [java]     criteria: {CustomerFName:"alex"}        values: {CustomerFName:"alex"}
     [java] === 2014-01-30 13:20:09,857 [l0-4] INFO  SQLDataSource - [builtinApplication.CustomerView_fetch] derived query: SELECT $defaultSelectClause FROM $defaultTableClause WHERE $defaultWhereClause ORDER BY $defaultOrderClause

Thanks

[Isomorphic]

After calling fetchData() or filterData() on the grid, do not call setData(). You are removing the auto-generated ResultSet created by this methods, which is what is responsible for further filtering and paging of data. See the docs for ListGrid.fetchData() for details.

[plesenb]

The method setDate has been removed and has been cleaned cache, but the result is still the same.

Now the code for the fetching data looks like this:

public void fetch() {
	if (fetchLocks.isEmpty()) {
		if (listGrid != null) {
			listGrid.fetchData(criteria);
		}
	}
}

[Isomorphic]

Sorry, as you can see in the samples, this feature works as expected as soon as the errors in your code are removed.

If you think this is a framework issue, we would suggest you work toward a minimal, standalone test case demonstrating the issue - this will most likely reveal another problem in your code.

[plesenb]

I think the problem is that you are using the data from. data.xml, but I get data from a mysql database. As a result, the following sql query is generated:

SELECT CustomerView.CustomerCanContact, CustomerView.CustomerEmail, CustomerView.CustomerFName, CustomerView.CustomerID, CustomerView.CustomerLName, CustomerView.CustomerPointBalance, CustomerView.CustomerTel1, CustomerView.TierName FROM CustomerView WHERE (CustomerView.CustomerFName LIKE '%alex%') ORDER BY CustomerView.CustomerLName LIMIT 0, 75

which is case-sensitive.

[Isomorphic]

That would be case sensitive in other DBs. In MySQL, it's not.

[plesenb]

During a fetching of MySQL the following query comes (has been taken from logs):

SELECT CustomerView.CustomerFName, CustomerView.CustomerID, ... FROM CustomerView WHERE (CustomerView.CustomerFName LIKE '% alex%') ORDER BY CustomerView.CustomerLName LIMIT 0, 75

if I execute :
SELECT * FROM CustomerView WHERE (CustomerView.CustomerFName LIKE '% alex%')

I will get the wrong result:

result1

and if I execute :

SELECT * FROM CustomerView WHERE (lower (CustomerView.CustomerFName) LIKE lower ('% alex%'))

i'll get correct result:

result2

[Blama]

This might be related to this SO thread: http://stackoverflow.com/questions/4879846/how-to-configure-mysql-to-be-case-sensitive

Perhaps your MySQL instance is case sensitive while the standard installation is not.

Best regards,
Blama

[plesenb]

Perhaps your MySQL instance is case insensitive: http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_lower_case_file_system

Thanks!

[plesenb]

The problem was really encoded db (fields).

Thanks)

[Blama]

@Isomorphic: Given the fact that MySQL (and perhaps other supported databases as well) can be configured to be case-sensitive it might be a good idea to generate the LOWER(...) for all target systems.