[Bug]: Multiple relationships to same entity causes empty sets

What happened?

When an entity has multiple relationships to the same entity through different linking objects, this will cause the results to be null. Only objects related by all links will be in the result.

Repro:


drop table if exists dbo.RelationATable;
drop table if exists dbo.RelationBTable;
drop table if exists dbo.ParentTable;
drop table if exists dbo.ChildTable;
go

create table dbo.ParentTable
(
    id int not null primary key,
    parent_value varchar(100) not null    
)
go

create table dbo.ChildTable
(
    id int not null primary key,
    child_value varchar(100) not null    
)
go

create table dbo.RelationATable
(
    id int IDENTITY(1,1) primary key,
    parent_id int not null references dbo.ParentTable(id),
    child_id int not null references dbo.ChildTable(id),
)
go
create table dbo.RelationBTable
(
    id int IDENTITY(1,1) primary key,
    parent_id int not null references dbo.ParentTable(id),
    child_id int not null references dbo.ChildTable(id),
)
go
insert into dbo.ParentTable values
(1, 'Parent A'),
(2, 'Parent B'),
(3, 'Parent C'),
(4, 'Parent D'),
(5, 'Parent E')
go

insert into dbo.ChildTable values
(1, 'Child 1'),
(2, 'Child 2'),
(3, 'Child 3'),
(4, 'Child 4'),
(5, 'Child 5')
go
insert into dbo.RelationATable values
(1, 1),
(2, 2),
(3, 3 )
go
insert into dbo.RelationBTable values
(3, 3),
(2, 4),
(5 ,5)
go

Config file:

{
  "$schema": "https://github.com/Azure/data-api-builder/releases/download/v1.1.7/dab.draft.schema.json",
  "data-source": {
    "database-type": "mssql",
    "connection-string": "@env('DATABASE_CONNECTION_STRING')",
    "options": {
      "set-session-context": false
    }
  },
  "runtime": {
    "rest": {
      "enabled": true,
      "path": "/api",
      "request-body-strict": true
    },
    "graphql": {
      "enabled": true,
      "path": "/graphql",
      "allow-introspection": true
    },
    "host": {
      "cors": {
        "origins": [],
        "allow-credentials": false
      },
      "authentication": {
        "provider": "StaticWebApps"
      },
      "mode": "development"
    }
  },
  "entities": {
    "Parent": {
      "source": {
        "object": "dbo.ParentTable",
        "type": "table",
        "key-fields": [
          "id"
        ]
      },
      "graphql": {
        "enabled": true,
        "type": {
          "singular": "Parent",
          "plural": "Parents"
        }
      },
      "rest": {
        "enabled": true
      },
      "permissions": [
        {
          "role": "anonymous",
          "actions": [
            {
              "action": "*"
            }
          ]
        }
      ],
      "relationships": {
        "relationToChildByA": {
          "cardinality": "many",
          "target.entity": "Child",
          "source.fields": [
            "id"
          ],
          "target.fields": [
            "id"
          ],
          "linking.object": "dbo.RelationATable",
          "linking.source.fields": [
            "parent_id"
          ],
          "linking.target.fields": [
            "child_id"
          ]
        },"relationToChildByB": {
          "cardinality": "many",
          "target.entity": "Child",
          "source.fields": [
            "id"
          ],
          "target.fields": [
            "id"
          ],
          "linking.object": "dbo.RelationBTable",
          "linking.source.fields": [
            "parent_id"
          ],
          "linking.target.fields": [
            "child_id"
          ]
        }
      }
    },
    "Child": {
      "source": {
        "object": "dbo.ChildTable",
        "type": "table",
        "key-fields": [
          "id"
        ]
      },
      "graphql": {
        "enabled": true,
        "type": {
          "singular": "Child",
          "plural": "Children"
        }
      },
      "rest": {
        "enabled": true
      },
      "permissions": [
        {
          "role": "anonymous",
          "actions": [
            {
              "action": "*"
            }
          ]
        }
      ],
      "relationships": {}
    }
  }
}

GraphQL query:

query {
  parents {
    items {
      relationToChildByA {
        items {
          child_value
        }
      }
      relationToChildByB {
        items {
          child_value
        }
      }
    }
  }
}

The results return empty sets for relation A and B. Only Children with both relationships return any children:

{
  "data": {
    "parents": {
      "items": [
        {
          "relationToChildByA": {
            "items": []
          },
          "relationToChildByB": {
            "items": []
          }
        },
        {
          "relationToChildByA": {
            "items": []
          },
          "relationToChildByB": {
            "items": []
          }
        },
        {
          "relationToChildByA": {
            "items": [
              {
                "child_value": "Child 3"
              }
            ]
          },
          "relationToChildByB": {
            "items": [
              {
                "child_value": "Child 3"
              }
            ]
          }
        },
        {
          "relationToChildByA": {
            "items": []
          },
          "relationToChildByB": {
            "items": []
          }
        },
        {
          "relationToChildByA": {
            "items": []
          },
          "relationToChildByB": {
            "items": []
          }
        }
      ]
    }
  }
}

Version

1.1.3-rc

What database are you using?

Azure SQL

What hosting model are you using?

Local (including CLI), Container Apps

Which API approach are you accessing DAB through?

GraphQL

Relevant log output

Azure.DataApiBuilder.Core.Resolvers.IQueryExecutor[0]
      c7702ffe-7e5e-46f2-b101-11f281df401f Executing query: SELECT TOP 100 JSON_QUERY (COALESCE([table1_subq].[data], '[]')) AS [relationToChildByA], JSON_QUERY (COALESCE([table5_subq].[data], '[]')) AS [relationToChildByB] FROM [dbo].[ParentTable] AS [table0] OUTER APPLY (SELECT TOP 100 [table1].[child_value] AS [child_value] FROM [dbo].[ChildTable] AS [table1] INNER JOIN [dbo].[RelationATable] AS [table3] ON [table3].[child_id] = [table1].[id] INNER JOIN [dbo].[RelationBTable] AS [table4] ON [table4].[child_id] = [table1].[id] WHERE [table3].[parent_id] = [table0].[id] AND [table4].[parent_id] = [table0].[id] ORDER BY [table1].[id] ASC FOR JSON PATH, INCLUDE_NULL_VALUES) AS [table1_subq]([data]) OUTER APPLY (SELECT TOP 100 [table5].[child_value] AS [child_value] FROM [dbo].[ChildTable] AS [table5] INNER JOIN [dbo].[RelationATable] AS [table7] ON [table7].[child_id] = [table5].[id] INNER JOIN [dbo].[RelationBTable] AS [table8] ON [table8].[child_id] = [table5].[id] WHERE [table7].[parent_id] = [table0].[id] AND [table8].[parent_id] = [table0].[id] ORDER BY [table5].[id] ASC FOR JSON PATH, INCLUDE_NULL_VALUES) AS [table5_subq]([data]) WHERE 1 = 1 ORDER BY [table0].[id] ASC FOR JSON PATH, INCLUDE_NULL_VALUES

Code of Conduct

[X] I agree to follow this project's Code of Conduct

Azure / data-api-builder

[Bug]: Multiple relationships to same entity causes empty sets #2223