Filtering Data Source with nested data source as List

[mgreenberg]

Hello,

We are looking for some advice on filtering. Take for example that we have a data source that returns shopping purchases which we use to populate a list grid. This may have information such as customer name, item purchased and date purchased. The item purchased could belong to different item categories or category-less. The category details won't be displayed on the list grid as columns since there could be 0 to many categories per purchase (per record). We were thinking of displaying them as a record component listing each category detail out. Below is a simple mockup:

---------------------------------------------------------------
Customer name | Item Purchased | Date Purchased
---------------------------------------------------------------
XYZ           | Item1          | 7/17/2013
category1 (High Popularity), category2 (Low Popularity)
ABC           | Item2          | 7/16/2013
category1 (Low Popularity)
---------------------------------------------------------------

We have a requirement to filter records in the list grid based on category values such as category name and category popularity. E.g. filter the items that belong to category1 only, or filter the grid based on items with Low popularity. We are unsure how we would go about this type of filtering.

We thought we could have the list grid data source have a field 'categories' with field type 'any' that returns a List of Categories but how would we pass criteria to it for a specific category name and / or popularity if its not a common field type such as string or integer.
Now we are thinking maybe it is better to use a nested data source for the categories but again we have the same questions on how to filter.
We looked at the filter examples in the Feature Explorer but not sure if any of them would help us with these types of data sources.

We are on SmartClient Version: v8.3p_2013-05-22/Pro Deployment.

Any ideas/tips/suggestions would be appreciated.

Thanks!

[mgreenberg]

We have done some more research on this but are stuck on how to successfully return results from a DataSource that has a one-to-many relationship with another datasource.

To try to prototype what we are trying to do, I am using country and cities to demonstrate the one-to-many relationship and filtering countries based on fields in a city. I have modified the existing countryOneToManyHB datasource in the smart client download to have a new field called cityId with its valueXPath set to cities/cityId and with multiple="true". We want to be able to ultimately filter the countries by properties in the cities data source such as cityId.

Behind the scenes we actually will be using our own custom datasource in spring that performs a fetch operation against our DAO.

Modified Country datasource:

<DataSource
    ID="countryOneToManyHBTEST"
    serverType="hibernate"
    beanClassName="com.isomorphic.examples.server.hibernate.relations.CountryOneToMany"
    schemaBean="com.isomorphic.examples.server.hibernate.relations.CountryOneToMany"
>
    <fields>
        <field name="countryId" hidden="false" primaryKey="true" />
        <field name="cities"        hidden="false" multiple="true" type="cityOneToManyHBTEST"
            foreignKey="cityOneToManyHBTEST.cityId" 
        />
        <field name="cityId" multiple="true" valueXPath="cities/cityId" />
        <!--<field includeFrom="cityOneToManyHBTEST.cityId"   />-->
    </fields>
</DataSource>

Modified CityOneToManyHB datasource:

<DataSource
    ID="cityOneToManyHBTEST"
    serverType="hibernate"
    beanClassName="com.isomorphic.examples.server.hibernate.relations.CityOneToMany"
    schemaBean="com.isomorphic.examples.server.hibernate.relations.CityOneToMany"
>
    <fields>
        <field name="cityId" hidden="false" primaryKey="true" />
    </fields>
</DataSource>

Below is the code sample with City ID displayed as a column in the Country List grid, and a filter row and filter builder. In our actual application, we would just want a filter builder and display the List of cities (and their fields such as city ID, city name, etc) within the grid as an expander or record component instead of as separate columns.

We are noticing that filtering the city ID (a nested data source field) from the filter row in the grid, correctly filters the records in the country grid. However, doing the same from the FilterBuilder returns no results. E.g. typing 1 in the filter row for city ID returns 2 out of 6 records, as expected, but from the filter builder, if we perform the filter City Id contains 1, it returns 0 results.

<%@ taglib uri="/WEB-INF/iscTaglib.xml" prefix="isomorphic" %>
<HTML><HEAD>
	<isomorphic:loadISC skin="Enterprise"/>
</HEAD>

<BODY>

<SCRIPT>

<isomorphic:loadDS ID="countryOneToManyHBTEST"/>

isc.FilterBuilder.create({
	ID:"advancedFilter",
	dataSource:"countryOneToManyHBTEST",
	topOperatorAppearance: "radio"
});

isc.ListGrid.create({
    ID: "grid",
    dataSource: "countryOneToManyHBTEST",
    autoFetchData: true,
    width: "100%",
    height: "100%",
	showFilterEditor: true,
	filterOnKeypress: true,
	setCanFilter:true
	/*,dataProperties: {
		useClientFiltering: false
	}*/
});
							
isc.IButton.create({
    ID:"filterButton",
    title:"Filter",
    click : function () {
        grid.filterData(advancedFilter.getCriteria());
    }
})

isc.VStack.create({
    membersMargin:10,
	width:"100%",
	height:"100%",
    members:[ advancedFilter, filterButton, grid ]
})

</SCRIPT>
</BODY></HTML>

In the developer console, I see the errors expecting the search string for city ID to be an array instead of an integer, so our data source is probably not hooked up correctly:

20:33:17.909:IBLR3:WARN:TextItem:isc_TextItem_7[value]:compareValues - this is a multiple FormItem but compareValues was called with a non-null first argument `value1` that is not an array.
20:33:17.909:IBLR3:WARN:TextItem:isc_TextItem_7[value]:compareValues - this is a multiple FormItem but compareValues was called with a non-null second argument `value2` that is not an array.

From reading the post, http://forums.smartclient.com/showthread.php?t=22932, I also tried setting ResultSet.useClientFiltering to false, but then started getting errors saying "DSRequest for DataSource 'countryOneToManyHBTEST' contained AdvancedCriteria, which the DataSource does not support."

Could you provide some guidance and/or examples that can help us achieve our ultimate goal both on how to set up our datasource correctly and on the UI side: Filtering results in a ListGrid whose datasource has a one-to-many relationship but based on filter criteria on the many data source. We need to start implementing this feature asap for a deadline, so we are looking forward to some guidance as we are stuck.

Thanks!

[mgreenberg]

Hi Isomorphic,

I tried simplifying the test case (complete code sample below) more to use just local data sources, OrdersDS and OrderItemDS where OrderDS has a list of order items. The datasources are set up using the second approach described here: http://forums.smartclient.com/showpost.php?p=12783&postcount=6. The goal is to filter results in the grid based on order item names and order item priorities using Advanced Criteria.

I tried multiple approaches in here.

Approach (1) ('Filter 1' button) creates an advanced criteria with fieldName "orderItems/itemName" but it only filters on the first order item for each order. E.g. Filtering by order item name "Paddle" returns no results but by "Canoe" returns two.

In (2) ('Filter 2' button) I tried setting multiple to true on the DataSourceField and flattening out itemName and itemPriority into separate fields using valueXPaths.

In (3) ('Filter 3' button) I tried just using a simple criteria with valueXPath set on the local DS but it doesn't filter. Looks like it doesn't even display in the columns which might be related. Maybe valueXPath isn't supported on client-side datasources?

Hopefully you can provide guidance on how to achieve filtering on collections. We have been stuck on this for a while.

Thanks.

<%@ taglib uri="/WEB-INF/iscTaglib.xml" prefix="isomorphic" %>
<HTML>
<HEAD>
	<isomorphic:loadISC skin="Enterprise"/>
</HEAD>

<BODY>
<SCRIPT>

var orderData = [
	{
		orderId: 1,
		orderDate: new Date(2013, 1, 1),
		orderDescription: "Order 1",
		orderItems: [
			{ itemName: "Canoe", itemPriority: 1 },
			{ itemName: "Paddle", itemPriority: 0 }
		]
	},
	{
		orderId: 2,
		orderDate: new Date(2013, 2, 2),
		orderDescription: "Order 2",
		orderItems: [
			{ itemName: "Cooler", itemPriority: 2 }
		]
	},
	{
		orderId: 3,
		orderDate: new Date(2013, 3, 3),
		orderDescription: "Order 3",
		orderItems: [
			/* Not sure why you'd have an empty order, but anyway... */
		]
	},
	{
		orderId: 4,
		orderDate: new Date(2013, 3, 3),
		orderDescription: "Order 4",
		orderItems: [
			{ itemName: "Canoe", itemPriority: 0 }
		]
	}
];

isc.DataSource.create({
	ID: "orderDS",
	fields:[
		{name:"orderID", title:"Order ID", type:"integer", primaryKey:"true"},
		{name:"orderDate", title:"Date"},
		{name:"orderDescription", title:"Description"},
		{name:"orderItems", title:"Order Items", type:"orderItemDS", multiple:"true"},
		{name:"orderItemName", title:"Order Item Name",  valueXPath:"orderItems/itemName", multiple:"true"},
		{name:"orderItemPriority", title:"Order Item Priority", valueXPath:"orderItems/itemPriority", multiple:"true"}
	],
	clientOnly:true,
	testData: orderData
});

isc.DataSource.create({
	ID:"orderItemDS",
	fields:[
		{name:"itemName", title:"Item Name", type:"text", primaryKey:"true"},
		{name:"itemPriority", title:"Item Priority", type:"integer"}
	],
	clientOnly:true
});

isc.ListGrid.create({
	ID: "orderGrid",
	dataSource: "orderDS",
	autoFetchData: true,
	width: "100%",
	height: "100%",
	showFilterEditor: true,
	filterOnKeypress: true,
	setCanFilter:true,
	allowFilterExpressions: true
});

isc.IButton.create({
    ID:"filterButton1",
    title:"Filter 1",
    click: function () {
    	//(1) This only filters by the first item in each orderItems. e.g. it returns Order 1 when itemName value is set to 'Canoe' but not to 'Paddle.' 
    	var filterCriteria = 
			{
				_constructor: "AdvancedCriteria",
				criteria: [
					{fieldName: "orderItems/itemName", operator: "iContains", value: "Paddle"}
				],
				operator: "and"
			};
		
		orderGrid.filterData(filterCriteria);
    }
});

isc.IButton.create({
    ID:"filterButton2",
    title:"Filter 2",
    click: function () {
		//2. Tried to flatten out the fields from the orderItems array but doesn't filter 
    	var filterCriteria = 
			{
				_constructor: "AdvancedCriteria",
				criteria: [
					{fieldName: "orderItemName", operator: "iContains", value: "Paddle"}
				],
				operator: "and"
			};
			
		orderGrid.filterData(filterCriteria);
    }
});

isc.IButton.create({
    ID:"filterButton3",
    title:"Filter 3",
    click: function () {
		//3. Tried to flatten out the fields from the orderItems array and use simple Criteria versus Advanced Criteria
    	var filterCriteria = 
			{
				orderItemName: "Paddle"
			};
			
		orderGrid.filterData(filterCriteria);
    }
});

isc.VStack.create({
    membersMargin:10,
	width: "100%",
	height: "100%",
    members:[ filterButton1, filterButton2, filterButton3, orderGrid ]
});

</SCRIPT>
</BODY></HTML>

[Isomorphic]

Looks like you are using Hibernate. Have you already seen this doc, which explains the different ways of exposing a JPA or Hibernate relation to SmartClient, and how filter criteria against such fields are interpreted?