Announcement

**Isomorphic** · 9 Nov 2021, 06:01

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>

Consider setting customSQL=true on ParentX_Name fields to exclude those from automatically generated SQL to avoid duplicates with manually added joins.

... 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);

In this case tableClause would include dynamic snippet:

Code:

<operationBinding operationType="fetch" includeAnsiJoinsInTableClause="true">
            <tableClause>
                $defaultTableClause
                $manuallyAddedJoins
            </tableClause>
        </operationBinding>

Let us know please how it worked for you.

**Isomorphic** · 18 Nov 2021, 05:22

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 "
/>

Although this approach would work in your case, your suggested feature would still make sense if a field would require multiple unrelated joins that would not be possible to trigger with a single includeFrom attribute. So, if after all workaround options you would still need the "fieldDependencies" feature it can be requested via the Feature Sponsorship.

**eschwegs** · 15 Dec 2021, 07:26

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

DataSource includeField SQL generation is not valid when grouped

Comment

Comment

Comment