Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
Clear All
new posts

    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

    #2
    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.
    Last edited by Isomorphic; 10 Nov 2021, 00:35.

    Comment


      #3
      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.

      Comment


        #4
        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.

        Comment

        Working...
        X