-- Table to store person data
CREATE TABLE Person (
PersonID INT IDENTITY(1,1) PRIMARY KEY,
FirstName NVARCHAR(50),
LastName NVARCHAR(50)
);
-- Table to store address types (e.g., Home, Work, etc.)
CREATE TABLE AddressType (
AddressTypeID INT IDENTITY(1,1) PRIMARY KEY,
TypeName NVARCHAR(50) NOT NULL
);
-- Table to store phone number types (e.g., Mobile, Home, Work)
CREATE TABLE PhoneNumberType (
PhoneNumberTypeID INT IDENTITY(1,1) PRIMARY KEY,
TypeName NVARCHAR(50) NOT NULL
);
-- Table to store addresses, which reference a person and an address type
CREATE TABLE Address (
AddressID INT IDENTITY(1,1) PRIMARY KEY,
PersonID INT,
AddressTypeID INT,
StreetAddress NVARCHAR(100),
City NVARCHAR(50),
State NVARCHAR(50),
PostalCode NVARCHAR(10),
CONSTRAINT FK_Person_Address FOREIGN KEY (PersonID) REFERENCES Person(PersonID) ON DELETE CASCADE,
CONSTRAINT FK_AddressType_Address FOREIGN KEY (AddressTypeID) REFERENCES AddressType(AddressTypeID)
);
-- Table to store phone numbers, which reference a person and a phone number type
CREATE TABLE PhoneNumber (
PhoneNumberID INT IDENTITY(1,1) PRIMARY KEY,
PersonID INT,
PhoneNumberTypeID INT,
PhoneNumber NVARCHAR(20),
CONSTRAINT FK_Person_PhoneNumber FOREIGN KEY (PersonID) REFERENCES Person(PersonID) ON DELETE CASCADE,
CONSTRAINT FK_PhoneNumberType_PhoneNumber FOREIGN KEY (PhoneNumberTypeID) REFERENCES PhoneNumberType(PhoneNumberTypeID)
);
-- Insert some sample data into AddressType
INSERT INTO AddressType (TypeName) VALUES ('Home'), ('Work');
-- Insert some sample data into PhoneNumberType
INSERT INTO PhoneNumberType (TypeName) VALUES ('Mobile'), ('Home'), ('Work');
-- Insert two persons
INSERT INTO Person (FirstName, LastName)
VALUES
('John', 'Doe'),
('Jane', 'Smith');
-- Insert addresses for the persons (assuming PersonID 1 for John Doe, 2 for Jane Smith)
INSERT INTO Address (PersonID, AddressTypeID, StreetAddress, City, State, PostalCode)
VALUES
(1, 1, '123 Main St', 'New York', 'NY', '10001'), -- John Doe's Home Address
(1, 2, '456 Work Ave', 'New York', 'NY', '10002'), -- John Doe's Work Address
(2, 1, '789 Elm St', 'Los Angeles', 'CA', '90001'); -- Jane Smith's Home Address
-- Insert phone numbers for the persons
INSERT INTO PhoneNumber (PersonID, PhoneNumberTypeID, PhoneNumber)
VALUES
(1, 1, '123-456-7890'), -- John Doe's Mobile Number
(1, 3, '111-222-3333'), -- John Doe's Work Number
(2, 2, '987-654-3210'); -- Jane Smith's Home Number
What happened?
This is the scenario.
A SQL database with a Person table.
A Person may have 0:M Addresses. Each Address has an AddressType.
A Person may have 0:M PhoneNumbers. Each PhoneNumber has a PhoneNumberType.
When querying using GraphQL, we can query for Person and related Addresses and PhoneNumbers. However, we can only add the related AddressType/PhoneNumberType to the first related entity in the query. Retrieving a Person with Addresses and PhoneNumbers as well as the related AddressType and PhoneNumberType entities will fail, nullifying the latter of the two.
Here is a short example that shows the issue.
T-SQL statements to create tables and example data
dab-config.json
GraphQL query that will throw an error.
Result from query. Note that PhoneNumberType is null.
Removing the AddressType from the query above will still throw an error, unless the entire phoneNumbers is moved above addresses. Then the query will execute successfully. It is still not possible to retrieve both PhoneNumberType and AddressType at the same time. The nested entity always has to be at the top of the query.
Partially working query
Result from query
Version
1.2.10
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
Code of Conduct