Thanks, this should work in this case, and is much cleaner then having to add the last Name field to stuff like outputs in optionsDataSource setups.
It would still be nice to see a dependsOn field or something that could be used to explicitly get the framework to include fields needed to create the JOIN of customSelect expressions. Just something to think about for the future.
Announcement
Collapse
No announcement yet.
X
-
On a second thought your exact use case may make use of a mixed includeFrom + customSelectExpression usage, so that we would auto generate SQL joins according to the includeFrom attribute, but still use the customSelectExpression in the select clause. In your case joins required for the includeFrom for Parent3_Name field cover the relations for other two fields, so all auto-joins may be triggered by the single inlcudeFrom:
Code:<field name="NestedName" type="text" length="63" width="100" title="Nested ExtraData Name" includeFrom="TestExtraDataNamesBenchView.TestExtraDataNamesBenchView.TestExtraDataNamesBenchView.Name" customSelectExpression=" CASE WHEN EDTBLX_EDTBLX_EDTBLX.FK_Test_ExtraData_Names IS NOT NULL THEN '[...].' + EDTBLX_EDTBLX_EDTBLX.Name + '.' + EDTBLX_EDTBLX.Name + '.' + EDTBLX.Name + '.' + Test_ExtraData_Names.Name WHEN EDTBLX_EDTBLX.FK_Test_ExtraData_Names IS NOT NULL THEN EDTBLX_EDTBLX_EDTBLX.Name + '.' + EDTBLX_EDTBLX.Name + '.' + EDTBLX.Name + '.' + Test_ExtraData_Names.Name WHEN EDTBLX_EDTBLX.Name IS NOT NULL THEN EDTBLX_EDTBLX.Name + '.' + EDTBLX.Name + '.' + Test_ExtraData_Names.Name WHEN EDTBLX.Name IS NOT NULL THEN EDTBLX.Name + '.' + Test_ExtraData_Names.Name ELSE Test_ExtraData_Names.Name END " />
Leave a comment:
-
SQL joins are generated for the bare minimum of related tables, this includes joins for the fields appearing in outputs, groupBy, criteria, etc. Smartclient cannot know what kind of joins are required in order to make custom SQL work properly. So, unless you provide additional information proving otherwise this is considered as expected behavior.
That said, you could try directly adding necessary joins via custom operationBinding.tableClause:
Code:<operationBinding operationType="fetch" includeAnsiJoinsInTableClause="true"> <tableClause> $defaultTableClause LEFT OUTER JOIN Test_ExtraData_Names EDTBLX ON Test_ExtraData_Names.FK_Test_ExtraData_Names = EDTBLX.PK_Test_ExtraData_Names LEFT OUTER JOIN Test_ExtraData_Names EDTBLX_EDTBLX ON EDTBLX.FK_Test_ExtraData_Names = EDTBLX_EDTBLX.PK_Test_ExtraData_Names LEFT OUTER JOIN Test_ExtraData_Names EDTBLX_EDTBLX_EDTBLX ON EDTBLX_EDTBLX.FK_Test_ExtraData_Names = EDTBLX_EDTBLX_EDTBLX.PK_Test_ExtraData_Names </tableClause> </operationBinding>
... or detect which joins are missing in the automatically generated SQL (by analyzing outputs for example) and add only those using User-defined snippets of SQL Templating feature in DMI:
Code:String joins = "LEFT OUTER JOIN Test_ExtraData_Names EDTBLX............."; edDsRequest.addToTemplateContext("manuallyAddedJoins", joins);
Code:<operationBinding operationType="fetch" includeAnsiJoinsInTableClause="true"> <tableClause> $defaultTableClause $manuallyAddedJoins </tableClause> </operationBinding>
Last edited by Isomorphic; 10 Nov 2021, 00:35.
Leave a comment:
-
DataSource includeField SQL generation is not valid when grouped
I believe I have found an SQL generation issue and possibly a missing feature (or I just can't figure out how to find an existing feature).
(SmartClient Version: v12.1p_2021-07-15/PowerEdition Deployment (built 2021-07-15))
For background and understanding:
For a given Test Bench I want to grab associated name value pairs of "extra data" in the form "Parent2.Partent1.Name", "Value X". The nested name construction is accomplished via a self referencing names table and a custom select expression that concatenates the first few nested names together separated by periods. IncludeFrom Parent fields are referenced in the custom select expression. They join in instances of the self referencing names tables. A Values table holds values and points to their associated first level name. A Bench table is also pointed to in order to group values within a certain bench.
As a Bench Name can exist under multiple revisions, I have to group on the Bench Name in order to get all of the unique name value pairs.
The issue:
I need to create Server side DMI code but I can't get it to generate valid SQL. i.e.
Code:DSRequest edDsRequest = new DSRequest("TestExtraDataValuesNamesBenchView", "fetch"); edDsRequest.setTextMatchStyle("exactCase"); edDsRequest.setAdvancedCriteria(advCrit); // criteria that filters by Bench Name String[] fieldNames = {"PK_Test_ExtraData_Names", "Name", "Parent1_Name", "Parent2_Name", "Parent3_Name", "NestedName", "BenchName"}; edDsRequest.setOutputs(Arrays.asList(fieldNames)); edDsRequest.setGroupBy(fieldNames); DSResponse resp = edDsRequest.execute();
1) The SQL generated for the example query is correct EXCEPT for the GROUP BY clause. I believe this could be a bug related to includes and customSelect expressions. That GROUP BY clause contains not only the customSelectExpression needed for the NestedName field but also an extra comma and the NestedName field's <Table>.<name> expression. Essentially, for the GROUP BY clause generated, it looks like the field is included twice in two different ways .
Extra credit:
2) I want to create that same query but without the need to include the dependent Parent1, Parent2 and Parent3 fields in the output . I found that when creating that query, I have to set the dSRequest outputs and groupby to include the dependent Parent1, Parent2 and Parent3 fields in order to have smartGWT generate the JOIN clauses for those include fields. Is there a way to construct the DSRequest without needing explicit knowledge of Parent to NestedName dependency? What I'm looking for is some sort of DSField attribute or something that I can set on the NestedName fields to let smartGWT know that it depends on the Parent fields. That way smartGWT would generate the Parent fields SQL when the NestedName field was involved in the query. i.e. an DSField attribute like fieldDependencies="Parent1_Name, Parent2_Name, Parent3_Name" that I may have missed?
Here are the two DataSources, and the smartGWT generated SQL code resulting from the DSRequest. The mystery generated code, that throws exceptions, has been commented out. Without it, the query executes as intended.
Code:<DataSource ID="TestExtraDataValuesNamesBenchView" serverType="sql" tableName="Test_ExtraData_Values"> <fields> <!-- ================================================================================ Text_ExtraData_Values fields ================================================================================ --> <field name="PK_Test_ExtraData_Values" type="sequence" width="60" primaryKey="true" /> <field name="FK_Test_ExtraData_Names" type="integer" hidden="true" width="60" foreignKey="TestExtraDataNamesBenchView.PK_Test_ExtraData_Names" /> <field name="String" type="text" length="999" width="300" title="String Value" /> <!-- ================================================================================ Included fields from the ExtraData Names view . The Parent/GrandParent/GreatGrandParent fields are included to bring in the joins to form the NestedNames field. ================================================================================ --> <field includeFrom="TestExtraDataNamesBenchView.BenchName" /> <field includeFrom="TestExtraDataNamesBenchView.PK_Test_ExtraData_Names" /> <field includeFrom="TestExtraDataNamesBenchView.Name" /> <field includeFrom="TestExtraDataNamesBenchView.Parent1_Name" /> <field includeFrom="TestExtraDataNamesBenchView.Parent2_Name" /> <field includeFrom="TestExtraDataNamesBenchView.Parent3_Name" /> <field includeFrom="TestExtraDataNamesBenchView.NestedName" /> </fields> </DataSource>
Code:<DataSource ID="TestExtraDataNamesBenchView" serverType="sql" tableName="Test_ExtraData_Names"> <fields> <!-- ================================================================================ Text_ExtraData_Names fields ================================================================================ --> <field name="PK_Test_ExtraData_Names" type="sequence" width="60" primaryKey="true" /> <field name="Name" type="text" width="150" length="63" title="Name" /> <field name="FK_Test_Bench" type="integer" width="100" hidden="true" title="Test_Bench FK" foreignKey="Test_Bench.PK_Test_Bench" /> <field name="FK_Test_ExtraData_Names" type="integer" width="120" hidden="true" title="Self referencing FK" foreignKey="TestExtraDataNamesBenchView.PK_Test_ExtraData_Names" relatedTableAlias="EDTBLX" joinType="outer" /> <field includeFrom="Test_Bench.BenchName" /> <field name="Parent1_Name" title="Parent1 Name" includeFrom="TestExtraDataNamesBenchView.Name" /> <field name="Parent2_Name" title="Parent2 Name" includeFrom="TestExtraDataNamesBenchView.TestExtraDataNamesBenchView.Name" /> <field name="Parent3_Name" title="Parent3 Name" includeFrom="TestExtraDataNamesBenchView.TestExtraDataNamesBenchView.TestExtraDataNamesBenchView.Name" /> <field name="NestedName" type="text" length="63" width="100" title="Nested ExtraData Name" customSelectExpression=" CASE WHEN EDTBLX_EDTBLX_EDTBLX.FK_Test_ExtraData_Names IS NOT NULL THEN '[...].' + EDTBLX_EDTBLX_EDTBLX.Name + '.' + EDTBLX_EDTBLX.Name + '.' + EDTBLX.Name + '.' + Test_ExtraData_Names.Name WHEN EDTBLX_EDTBLX.FK_Test_ExtraData_Names IS NOT NULL THEN EDTBLX_EDTBLX_EDTBLX.Name + '.' + EDTBLX_EDTBLX.Name + '.' + EDTBLX.Name + '.' + Test_ExtraData_Names.Name WHEN EDTBLX_EDTBLX.Name IS NOT NULL THEN EDTBLX_EDTBLX.Name + '.' + EDTBLX.Name + '.' + Test_ExtraData_Names.Name WHEN EDTBLX.Name IS NOT NULL THEN EDTBLX.Name + '.' + Test_ExtraData_Names.Name ELSE Test_ExtraData_Names.Name END " /> </fields> </DataSource>
Generated code:
Code:SELECT Test_ExtraData_Names.PK_Test_ExtraData_Names, Test_ExtraData_Names.Name, EDTBLX.Name AS Parent1_Name, EDTBLX_EDTBLX.Name AS Parent2_Name, EDTBLX_EDTBLX_EDTBLX.Name AS Parent3_Name, Test_Bench.BenchName, CASE WHEN EDTBLX_EDTBLX_EDTBLX.FK_Test_ExtraData_Names IS NOT NULL THEN '[...].' + EDTBLX_EDTBLX_EDTBLX.Name + '.' + EDTBLX_EDTBLX.Name + '.' + EDTBLX.Name + '.' + Test_ExtraData_Names.Name WHEN EDTBLX_EDTBLX.FK_Test_ExtraData_Names IS NOT NULL THEN EDTBLX_EDTBLX_EDTBLX.Name + '.' + EDTBLX_EDTBLX.Name + '.' + EDTBLX.Name + '.' + Test_ExtraData_Names.Name WHEN EDTBLX_EDTBLX.Name IS NOT NULL THEN EDTBLX_EDTBLX.Name + '.' + EDTBLX.Name + '.' + Test_ExtraData_Names.Name WHEN EDTBLX.Name IS NOT NULL THEN EDTBLX.Name + '.' + Test_ExtraData_Names.Name ELSE Test_ExtraData_Names.Name END AS NestedName, FROM Test_ExtraData_Values JOIN Test_ExtraData_Names ON Test_ExtraData_Values.FK_Test_ExtraData_Names = Test_ExtraData_Names.PK_Test_ExtraData_Names LEFT OUTER JOIN Test_ExtraData_Names EDTBLX ON Test_ExtraData_Names.FK_Test_ExtraData_Names = EDTBLX.PK_Test_ExtraData_Names LEFT OUTER JOIN Test_ExtraData_Names EDTBLX_EDTBLX ON EDTBLX.FK_Test_ExtraData_Names = EDTBLX_EDTBLX.PK_Test_ExtraData_Names LEFT OUTER JOIN Test_ExtraData_Names EDTBLX_EDTBLX_EDTBLX ON EDTBLX_EDTBLX.FK_Test_ExtraData_Names = EDTBLX_EDTBLX_EDTBLX.PK_Test_ExtraData_Names JOIN Test_Bench ON Test_ExtraData_Names.FK_Test_Bench = Test_Bench.PK_Test_Bench WHERE ( Test_Bench.BenchName = 'Memsys FW Bench' AND Test_Bench.BenchName IS NOT NULL ) GROUP BY Test_ExtraData_Names.PK_Test_ExtraData_Names, Test_ExtraData_Names.Name, EDTBLX.Name, EDTBLX_EDTBLX.Name, EDTBLX_EDTBLX_EDTBLX.Name, Test_Bench.BenchName, CASE WHEN EDTBLX_EDTBLX_EDTBLX.FK_Test_ExtraData_Names IS NOT NULL THEN '[...].' + EDTBLX_EDTBLX_EDTBLX.Name + '.' + EDTBLX_EDTBLX.Name + '.' + EDTBLX.Name + '.' + Test_ExtraData_Names.Name WHEN EDTBLX_EDTBLX.FK_Test_ExtraData_Names IS NOT NULL THEN EDTBLX_EDTBLX_EDTBLX.Name + '.' + EDTBLX_EDTBLX.Name + '.' + EDTBLX.Name + '.' + Test_ExtraData_Names.Name WHEN EDTBLX_EDTBLX.Name IS NOT NULL THEN EDTBLX_EDTBLX.Name + '.' + EDTBLX.Name + '.' + Test_ExtraData_Names.Name WHEN EDTBLX.Name IS NOT NULL THEN EDTBLX.Name + '.' + Test_ExtraData_Names.Name ELSE Test_ExtraData_Names.Name END --, -- Test_ExtraData_Names.NestedName
Tags: None
Leave a comment: