3 Replies Latest reply on Feb 9, 2017 7:06 PM by Steven Hawkins

    odata4 multiple foreign keys issue

    Mirco Marchitiello Newbie

      Hi,

      after installing version 9.1.3 with the fix of TEIID-4298 the reverse navigation seems to work fine.

      We've facing another problem in the oData navigation in case of two foreign keys between two tables:

       

      Example:

      EmployeeMasterEntity (PK EmployeeID, FK Departments)

      OrganizationalUnitEntity (PK OrganizationalUnitID, FK Managers)

       

      With following data (case 1):

      EmployeeMasterEntity

      EmployeeID                  Department

      1                                  10000001 

      2                                  10000002 

      3                                  10000003 

       

      OrganizationalUnitEntity

      OrganizationaUnitID      UnitManager

      10000001                     1

      10000002                     2

      10000003                     3

       

       

      the following queries work fine:

       

      http://localhost:8080/odata4/TestOdata.1/InterlemView/EmployeeMasterEntity(1)/Departments?$format=json

      http://localhost:8080/odata4/TestOdata.1/InterlemView/EmployeeMasterEntity(2)/Departments?$format=json

      http://localhost:8080/odata4/TestOdata.1/InterlemView/EmployeeMasterEntity(3)/Departments?$format=json

       

      http://localhost:8080/odata4/TestOdata.1/InterlemView/OrganizationalUnitEntity('10000001')/Managers?$format=json

      http://localhost:8080/odata4/TestOdata.1/InterlemView/OrganizationalUnitEntity('10000002')/Managers?$format=json

      http://localhost:8080/odata4/TestOdata.1/InterlemView/OrganizationalUnitEntity('10000003')/Managers?$format=json

       

      http://localhost:8080/odata4/TestOdata.1/InterlemView/OrganizationalUnitEntity('10000001')/EmployeeMasterEntity_Departments?$format=json

      http://localhost:8080/odata4/TestOdata.1/InterlemView/OrganizationalUnitEntity('10000002')/EmployeeMasterEntity_Departments?$format=json

      http://localhost:8080/odata4/TestOdata.1/InterlemView/OrganizationalUnitEntity('10000003')/EmployeeMasterEntity_Departments?$format=json

       

      http://localhost:8080/odata4/TestOdata.1/InterlemView/EmployeeMasterEntity(1)/OrganizationalUnitEntity_Managers?$format=json

      http://localhost:8080/odata4/TestOdata.1/InterlemView/EmployeeMasterEntity(2)/OrganizationalUnitEntity_Managers?$format=json

      http://localhost:8080/odata4/TestOdata.1/InterlemView/EmployeeMasterEntity(3)/OrganizationalUnitEntity_Managers?$format=json

       

       

      With following data (Case 2):

      EmployeeMasterEntity

      EmployeeID                       Department

      1                                       10000001 

      2                                       10000002 

      3                                       10000001 

       

      OrganizationalUnitEntity

      OrganizationaUnitID           UnitManager

      10000001                          1

      10000002                          2

      10000003                          2

       

       

      the following queries don't work, since they return no values (blank page):

       

      http://localhost:8080/odata4/TestOdata.1/InterlemView/EmployeeMasterEntity(3)/Departments?$format=json

      http://localhost:8080/odata4/TestOdata.1/InterlemView/OrganizationalUnitEntity('10000003')/Managers?$format=json

       

      It seems that the query makes a sort of inner join between the two foreign keys. In case 1 EmployeeID 3 references to Department 10000003 and

      Departement 10000003 references to EmployeeID 3

      In case 2 EmployeeID 3 references to Department 10000001 but Department 10000001 references to EmployeeID 2

       

      The query

       

      http://localhost:8080/odata4/TestOdata.1/InterlemView/EmployeeMasterEntity(3)/Departments?$format=json

       

      should return Department 10000003, while query

       

      http://localhost:8080/odata4/TestOdata.1/InterlemView/OrganizationalUnitEntity('10000003')/Managers?$format=json

       

      Should return Employee 2

       

      Thanks,

      Mirco