data-api-builder
data-api-builder copied to clipboard
Azure
[Bug]: Creating a relationship to the same table twice causes null data.
What happened?
Love this project! Though I am encountering an issue.
I have an Orders table which has a two columns CountryTypeId and ShippingCountryTypeId. I have a CountryTypes table which lists countries.
My C# Order model has a CountryType member and a ShippingCountryType member.
CountryType () {
int CountryTypeId
string CountryTypeName
string CountryTypeCode
}
The goal is to fill these two members from a graphQl request. I do not have a join table in the database. I want to pull data from CountryTypes table twice (2 joins), essentially, once for each the matching CountryTypeId and another for the ShippingCountryTypeId.
My dab.config looks like this:
"Order": {
"source": {
..... for brevity
},
"graphql": {
"enabled": true,
..... for brevity
}
},
"CountryType": {
"cardinality": "one",
"target.entity": "CountryType",
"source.fields": [
"CountryTypeId"
],
"target.fields": [
"CountryTypeId"
],
"linking.source.fields": ["CountryTypeId"],
"linking.target.fields": ["CountryTypeId"]
},
"ShippingCountryType": {
"cardinality": "one",
"target.entity": "CountryType",
"source.fields": [
"ShippingCountryTypeId"
],
"target.fields": [
"CountryTypeId"
],
"linking.source.fields": ["ShippingCountryTypeId"],
"linking.target.fields": ["CountryTypeId"]
}
}
Graphql retuned:
"CountryType": null,
"ShippingCountryType": null
If I do:
"CountryType": {
"cardinality": "one",
"target.entity": "CountryType",
"source.fields": [
"CountryTypeId"
],
"target.fields": [
"CountryTypeId"
]
}
}
It returns the proper data.
Version
0.8.50+69f45352c90fae016c33c4e6c1294105898269cc
What database are you using?
Azure SQL
What hosting model are you using?
Local (including CLI)
Which API approach are you accessing DAB through?
GraphQL
Relevant log output
No response
Code of Conduct
- [X] I agree to follow this project's Code of Conduct
Below is additional repro
generated sqltext
SELECT TOP 100
[table0].[id] AS [id],
[table0].[countrytypeid] AS [countrytypeid],
[table0].[shippingcountrytypeid] AS [shippingcountrytypeid],
JSON_QUERY ([table1_subq].[data]) AS [CountryType],
JSON_QUERY ([table4_subq].[data]) AS [ShippingCountryType]
FROM [dbo].[orders] AS [table0]
OUTER APPLY (
SELECT TOP 1
[table1].[id] AS [id],
[table1].[countryname] AS [countryname]
FROM [dbo].[countrytype] AS [table1]
WHERE [table0].[countrytypeid] = [table1].[id]
AND [table1].[id] = [table0].[countrytypeid]
AND [table0].[shippingcountrytypeid] = [table1].[id]
AND [table1].[id] = [table0].[shippingcountrytypeid]
ORDER BY [table1].[id] ASC
FOR JSON PATH, INCLUDE_NULL_VALUES,WITHOUT_ARRAY_WRAPPER)
AS [table1_subq]([data])
OUTER APPLY (
SELECT TOP 1 [table4].[id] AS [id],
[table4].[countryname] AS [countryname]
FROM [dbo].[countrytype] AS [table4]
WHERE [table0].[countrytypeid] = [table4].[id]
AND [table4].[id] = [table0].[countrytypeid]
AND [table0].[shippingcountrytypeid] = [table4].[id]
AND [table4].[id] = [table0].[shippingcountrytypeid]
ORDER BY [table4].[id] ASC
FOR JSON PATH, INCLUDE_NULL_VALUES,WITHOUT_ARRAY_WRAPPER)
AS [table4_subq]([data])
WHERE 1 = 1
ORDER BY [table0].[id]
ASC FOR JSON PATH, INCLUDE_NULL_VALUES
Erroneous predicates added to both subqueries joining the entities.
Repro tables
CountryType
/****** Object: Table [dbo].[countrytype] Script Date: 5/10/2024 8:41:02 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[countrytype](
[id] [int] NOT NULL,
[countryname] [varchar](50) NOT NULL,
CONSTRAINT [PK_countrytype] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
Orders
/****** Object: Table [dbo].[orders] Script Date: 5/10/2024 8:41:26 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[orders](
[id] [int] IDENTITY(1,1) NOT NULL,
[order_name] [varchar](50) NULL,
[countrytypeid] [int] NULL,
[shippingcountrytypeid] [int] NULL,
CONSTRAINT [PK_orders] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
Dab config
{
"$schema": "https://github.com/Azure/data-api-builder/releases/download/v0.11.132/dab.draft.schema.json",
"data-source": {
"database-type": "mssql",
"connection-string": "",
"options": { "set-session-context": false }
},
"runtime": {
"rest": { "enabled": false },
"graphql": {
"enabled": true,
"path": "/graphql"
},
"host": {
"cors": {
"origins": [ "http://localhost:5500" ],
"allow-credentials": false
},
"authentication": {
"provider": "Simulator"
},
"mode": "development"
}
},
"entities": {
"Orders": {
"source": {
"object": "dbo.orders",
"type": "table"
},
"graphql": {
"enabled": true,
"type": {
"singular": "order",
"plural": "orders"
}
},
"rest": {
"enabled": true
},
"permissions": [
{
"role": "anonymous",
"actions": [
{
"action": "*"
}
]
}
],
"relationships": {
"CountryType": {
"cardinality": "one",
"target.entity": "CountryType",
"source.fields": [ "countrytypeid" ],
"target.fields": [ "id" ]
},
"ShippingCountryType": {
"cardinality": "one",
"target.entity": "CountryType",
"source.fields": [ "shippingcountrytypeid" ],
"target.fields": [ "id" ]
}
}
},
"CountryType": {
"source": {
"object": "dbo.countrytype",
"type": "table"
},
"graphql": {
"enabled": true,
"type": {
"singular": "countrytype",
"plural": "countrytypes"
}
},
"rest": {
"enabled": true
},
"permissions": [
{
"role": "anonymous",
"actions": [
{
"action": "*"
}
]
}
]
}
}
}
GraphQL query
query q1718 {
orders {
items{
id
countrytypeid
shippingcountrytypeid
CountryType
{
id
countryname
}
ShippingCountryType
{
id
countryname
}
}
}
}
GraphQL result
{
"data": {
"orders": {
"items": [
{
"id": 1,
"countrytypeid": 2,
"shippingcountrytypeid": 1,
"CountryType": null,
"ShippingCountryType": null
},
{
"id": 2,
"countrytypeid": 3,
"shippingcountrytypeid": 4,
"CountryType": null,
"ShippingCountryType": null
}
]
}
}
}
What is the status on this issue in the backlog? I got a bit sad when it moved from a planned release to the general backlog.
