Possible bug with server summary for field from different table

[rolf.woll]

Hi,

SmartGwt Power 4.1. build 2014-10-29 using MSSQL database.

I have a datasource where I join in multiple tables using tableClause:

<DataSource ID="portlet_summary_contractor_bingo_financial_reporting"	
	        serverType="sql" 
	        tableName="lor_lotteries">
	<fields>
		<field name="lottery_id" nativeName="id" type="sequence"	primaryKey="true" />
		<field name="contractor_orgno" tableName="o" nativeName="orgno"	title="Orgnr entreprenør" type="text" />
		<field name="lottery_valid_from" detail="true" nativeName="valid_from" type="date" title="Bingo dato fra" />
		<field name="lottery_valid_to" detail="true" nativeName="valid_to" type="date" title="Bingo dato til" />
	</fields>

	<operationBindings>

			<OperationBinding operationType="fetch">
				<tableClause><![CDATA[
          lor_lotteries 
            LEFT OUTER JOIN lor_contractor_approvals c ON lor_lotteries.contractor_approval_id = c.id
            LEFT OUTER JOIN organizations o ON c.organization_id = o.id 
        ]]>
				</tableClause>
  	        <summaryFunctions>
				<lottery_id>count</lottery_id>
				<contractor_orgno>count</contractor_orgno>
				<lottery_valid_from>max</lottery_valid_from>
			</summaryFunctions>
		</OperationBinding>
	</operationBindings>
</DataSource>

When running this query, I get a count of lottery_id and a max of lottery_valid_from. However, I donīt get a result for contractor_orgno.

The reason seems to be that summaries does not work for fields which have the tableName property set.

So, if I add a tableName="or_lotteries" to the lottery_valid_from field definition, I donīt get a summary for this field either.

Is this a bug or is it intended to behave this way?

Regards
Rolf

[rolf.woll]

Any feedback on this?

Regards
Rolf

[rolf.woll]

Any feedback on this issue would be appreciated.

This issue prevents us from having summaries in most of our grids due to the fact that we use tableClauses a lot.

Regards
Rolf

[Isomorphic]

You seem to be expecting something that doesn't make much sense..

In the absence of your tableClause, SmartClient would need to generate a join to the related table, or we'd just be producing bad SQL.

Your DataSource doesn't contain the relevant information to generate a join at all, and we have no way of knowing that your custom tableClause has joined the tables already (we don't try to parse your SQL).

Finally, the summaryFunction feature makes no mention of performing summaries across joins in any case.

[rolf.woll]

Not sure what you mean by expecting something that does not make sense?

I have a working SQL using tableClause, with nativeName and tableName set for some fields to retrieve the data. The SQL is working fine, so I would think it contains the relevant information to create a join.

Then I would want to add a server side summary for my fields. This is not working for the fields where I need to specify tableName. (Which happens to be for fields from tables joined in using tableClause)

So, to verify this, I tried to set the tableName attribute for a field in the main table, lottery_valid_from.

<field name="lottery_valid_from" tableName="lor_lotteries" ....

This should not change any behaviour, but it causes the server side summary to fail for this field as well. The SQL fetch itself is working fine.

So, basically, when tableName attribute for a field is present, there is no server side summary for this field. This is true independent of any joins.

I donīt understand why this should not work. However, if this is not supported, I would suggest that you mention this in the server summary documentation.

[Isomorphic]

We're not sure we can provide a clearer explanation than we already have - we suggest re-reading our post, slowly and carefully. It already addresses all the points you have raised.

[rolf.woll]

Okay.

I then conclude that server side summaries does not work for SQL data sources which operate on multiple tables.

BTW. Are you happy with the way you have responded to my question in this thread?

[Isomorphic]

To restate this again: you seem to be expecting that the framework should either:

a) generate SQL which will definitely fail unless you happen to have overridden the <tableClause> to compensate (a situation we can't detect)

OR

b) generate a join even though the framework doesn't have the information that would produce the right join (a "Cartesian join" being presumably not desirable)

It's indeed true that the framework will not generate summary-related SQL when the tableName attribute is present, because the framework doesn't have the information to reliably do so. And no, we do not attempt to detect that you have set an in valid/no-op tableName (same as DataSource.tableName).

If you're looking for a way to do this, consider either:

1. using database views so that only one "table" is involved from the framework's perspective

OR

2. sponsoring a feature where either additional settings are added that would allow the framework to generate the necessary tableClause for you, or a property you could set telling us that your tableClause provides a join so the framework can blindly generate SQL that wouldn't work with the default tableClause.

[Blama]

2. sponsoring a feature where either additional settings are added that would allow the framework to generate the necessary tableClause for you, or a property you could set telling us that your tableClause provides a join so the framework can blindly generate SQL that wouldn't work with the default tableClause.

@Isomorphic: Would the summary currently work with no hand-crafted <tableClause> but a generated one, using the correct foreignKey-attributes (and joinType-attributes) in all DataSources involved?

The join the OP hand-crafts seems to be easy and can already be generated IMHO.

If not, the VIEW option is really an easy and good suggestion.

Best regards,
Blama

[Isomorphic]

Sorry for the slow reply on this - yes, you can use operationBinding.summaryFunctions on an includeFrom field (including both many-one and one-many relationships).

The problem with tableName is again that it does not, on it's own, provide enough information for the framework to generate the join. But the combination of foreignKey and includeFrom does provide the needed information.