The proper way to deliver SQL One-to-Many Relation to ListGrid

[abthere]

v10.0p_2014-09-24/Enterprise Deployment 2014-09-24

We need to implement a ListGrid that shows several columns of data plus problem icons that indicate that a particular field in the row is not correct. There are could be more than one problematic field in any given row. What is the best way to deliver the data with the associated information about the incorrect fields to a ListGrid?

[Blama]

Hi abthere,

I'd solve it in the DB.

Either have a SELECT pk, LISTAGG(...) GROUP BY pk-VIEW and outer-join it to the main table in your .ds.xml with foreignKey="..." joinType="outer" or do a similar thing with a VIEW using PIVOT to transpose rows to columns.

Best regards,
Blama

[abthere]

Blama,
thank you for your input. I was hoping for something similar to JpaHibernateRelations http://www.smartclient.com/smartgwte...Relations.html where the framework would load subobjects, but wasn't able to find any information about implementing it within SQL DataSource.

[Isomorphic]

Loading entire lists of subobjects is usually a bad idea. It generally implies that for every record you see in a grid, an additional SQL query was performed to fetch subobjects which may never be accessed by the user.

To really be able to recommend a particular approach, we'd need more detail about the errors involved, whether the user can correct them in some way in the UI, and whether the user will access and modify data from subobjects.

But as a quick take, yes, Blama is correct: the most efficient way to deliver information from related objects is a DB-level join.

[abthere]

The idea is to provide visual notification to the user that some of the fields are problematic, so the user can enter the edit mode and correct the data. Ideally we want to mimic the visual appearance of a DynamicForm where the red exclamation sign is placed next to the field label to indicate the error when validation fails.
We have 2 DataSources. The first one contains the data that needs to be shown in the ListGrid:

<DataSource
	sequenceMode="jdbcDriver"
	serverType="sql" 
	dbName="Mysql"
	ID="import_data"
	dataSourceVersion="1"
	tableName="import_data"
>
	<fields>
		<field implicitSequence="true" sqlType="integer" primaryKey="true" sqlLength="10" name="import_data_id" type="sequence" hidden="true"></field>
		<field sqlType="varchar" sqlLength="50" name="FieldA" length="50" type="text"></field>
		<field sqlType="varchar" sqlLength="20" name="FieldB" length="20" type="text"></field>
</fields>
</DataSource>

The second DataSource contains problems and associated with the first:

<DataSource
	sequenceMode="jdbcDriver"
	serverType="sql" 
	dbName="Mysql"
	ID="import_data_problem"
	dataSourceVersion="1"
	tableName="import_data_problem"
>
	<fields>
		<field implicitSequence="true" sqlType="integer" primaryKey="true" sqlLength="10" name="import_data_problem_id" type="sequence" hidden="true"></field>
<field name="import_data_id" type="integer" foreignKey="import_data.import_data_id"></field>
<field sqlType="varchar" sqlLength="255" name="fieldname" length="255" type="text"></field>
<field sqlType="varchar" sqlLength="100" name="prob_type" length="100" type="text"></field>
		<field sqlType="varchar" sqlLength="2000" name="prob_msg" length="2000" type="text"></field>
</fields>
</DataSource>

fieldname field contains the name of the problematic field in the import_data DataSource and import_data_id is used to identify the row. It appears that this second DataSource would have to be sent as subobjects of the first one in order to be able to flag the fields that need attention. User will not be able to modify the data in import_data_problem DataSource, only import_data. The question is, how to implement it in the most efficient way?

[Blama]

Hi abthere,

the problem is still about converting rows to columns.
If your import-table really has only 2 data-columns, use a VIEW like

SELECT id.import_data_id,
c1.prob_type AS FieldA_prob_type,
c1.prob_msg AS FieldA_prob_msg,
c2.prob_type AS FieldB_prob_type,
c2.prob_msg AS FieldB_prob_msg
FROM import_data id
LEFT OUTER JOIN import_data_problem c1 ON id.import_data_id = c1.import_data_id AND c1.fieldname = 'FieldA'
LEFT OUTER JOIN import_data_problem c2 ON id.import_data_id = c2.import_data_id AND c2.fieldname = 'FieldB';

Outer-join to that view in your .ds.xml.

If the columns as more than FieldA, FieldB you'll have many joins to your errors-table. Make sure to have correct indexes then.
It seems you are using MySQL. I don't know about PIVOT functions here, in Oracle you could solve it this way without the many joins.

Once you have this, you can use the Hilite features of SmartGWT, ICON-columns or Record Components. Most likely Hilite and/or ICON-columns is the way to go.

Best regards,
Blama

[Isomorphic]

A join could be used to simply cause these errors to be displayed, and would indeed be much more efficient than loading them as subobjects.

Allowing the user to resolve these errors via edit mode in the grid is much more tricky.

The problem is that, once the user begins editing values, the grid needs a validator it can call with a revised value to determine whether the problem is fixed.

So in outline you would need to:

1. provide a DataSource that has "serverCustom" validators that can figure out whether a given field value is still invalid once the user has changed it

2. provide the import_data rows to the grid, using setEditValues() to separately populate any fields that have validation errors - using setEditValues() causes the values to be treated as user edits which require validation

3. either run validation as soon as the grid is populated (so that errors can be returned by your serverCustom validators) or apply the initial errors via listGrid.setRowErrors()

This complexity comes from the fact that you've got validation errors stored in a DB table, yet now you want to build an interface that allows a user to resolve them on the fly with real-time feedback, which is odd: if it's possible to get real-time feedback, why were the errors stored to a table to begin with?

Before pursuing the above approach, you should definitely revisit how you got into this situation, and there may be a globally simpler way to handle this.

[Blama]

Hi all,

I skipped the mention of the need to correct errors by user abthere.

As I'll have a similar usecase in a few month, I'm also interested in the solution myself.

What it not clear so far:
What is the source of the entries in import_data? If it is some DB load into a staging table, I'd guess I'd try it the way Isomorphic described.

If the source is a .csv, the Batch Uploader might also be an option.

Best regards,
Blama



PS:
@Isomorphic:
If I download and upload the file in the Batch Uploader sample, nothing is displayed in the top ListGrid (the one generated by the Batch Uploader). I'll open a new thread for this.