## Why make this change? If there is a relationship defined for a (source,target) in the database with right cardinality as `ONE`, we add the relationship in both the directions (source->target and target->source) to the source's `RelationshipMetadata` corresponding to the target entity's entry in `RelationshipMetadata.TargetEntityToFkDefinitionMap` dictionary. We do this because the relationship can be 1:1 or N:1. In case of N:1, the first FK (from source->target) would be present. In case of 1:1, either or both of them can be present. But all this is known to us only at a later stage when we collect metadata from the database and actually infer which of the two relationships (or both) is/are valid. After validating the correct relationship, we don't remove the other relationship which we added earlier. The redundant relationship jadds a redundant `JOIN` condition (I think this is something which is visible in the sql query generated by DAB highlighted in this issue: #1940). ## What is this change? The functioning of `SqlMetadatProvider.FillInferredFkInfo()` is changed such that for all the FK definitions present for a target entity in the source entity's definition, the FK definitions for a target entity in the source entity's definition contain: 1. One entry for relationships defined between source to target in the database (via FK constraint) in the right order of referencing/referenced entity. 2. Two entries for relationships defined in the config between source to target (which don't exist in the database). The above is achieved via a call to a newly added method `SqlMetadataProvider.GetValidatedFKs()` which loops over all the foreign key definitions defined for the target entity in the source entity's definition and adds to the set of validated FK definitions: 1. All the FK definitions which actually map to a foreign key constraint defined in the database. In such a case, if the source/target fields are also provided in the config, they are given precedence over the FK constraint. 2. FK definitions for custom relationships defined by the user in the configuration file where no FK constrain t exists between the pair of (source, target) entities. ## How was this tested? - Added tests to `SqlMetadataProviderUnitTests` for Pg/Ms/MySql for the cases when an FK constraint exists between the source/target entities. ## Manual Testing 1. Did manual testing to ensure the redundant join condition is not added in the query. For a nested query like: <img width="422" alt="image" src="https://github.com/Azure/data-api-builder/assets/34566234/e49c98ab-5c6a-49d9-8cfd-97382dd1bf94"> where the configuration for Book entity looks like: <img width="299" alt="image" src="https://github.com/Azure/data-api-builder/assets/34566234/ab83e96e-8d13-4d71-b6d1-b89d20b13612"> the query generated before the fix (in main): ```tsql SELECT TOP 100 [table0].[title] AS [title], JSON_QUERY ([table1_subq].[data]) AS [publishers] FROM [dbo].[books] AS [table0] OUTER APPLY ( SELECT TOP 1 [table1].[name] AS [name] FROM [dbo].[publishers] AS [table1] WHERE [table0].[publisher_id] = [table1].[id] AND [table1].[id] = [table0].[publisher_id] ORDER BY [table1].[id] ASC FOR JSON PATH, INCLUDE_NULL_VALUES,WITHOUT_ARRAY_WRAPPER ) AS [table1_subq]([data]) WHERE 1 = 1 ORDER BY [table0].[id] ASC FOR JSON PATH, INCLUDE_NULL_VALUES ``` after the fix: ```tsql SELECT TOP 100 [table0].[title] AS [title], JSON_QUERY ([table1_subq].[data]) AS [publishers] FROM [dbo].[books] AS [table0] OUTER APPLY ( SELECT TOP 1 [table1].[name] AS [name] FROM [dbo].[publishers] AS [table1] WHERE [table0].[publisher_id] = [table1].[id] ORDER BY [table1].[id] ASC FOR JSON PATH, INCLUDE_NULL_VALUES,WITHOUT_ARRAY_WRAPPER ) AS [table1_subq]([data]) WHERE 1 = 1 ORDER BY [table0].[id] ASC FOR JSON PATH, INCLUDE_NULL_VALUES ``` <Observe the removal of redundant join condition in the 4th line in the second query> Similar observations for MySql: ```mysql SELECT COALESCE(JSON_ARRAYAGG(JSON_OBJECT(@param3, `subq6`.`title`, @param4, `subq6`.`publishers`)), JSON_ARRAY()) AS `data` FROM ( SELECT `table0`.`title` AS `title`, `table1_subq`.`data` AS `publishers` FROM `books` AS `table0` LEFT OUTER JOIN LATERAL (SELECT JSON_OBJECT(@Param2, `subq5`.`name`) AS `data` FROM ( SELECT `table1`.`name` AS `name` FROM `publishers` AS `table1` WHERE `table0`.`publisher_id` = `table1`.`id` ORDER BY `table1`.`id` ASC LIMIT 1 ) AS `subq5`) AS `table1_subq` ON TRUE WHERE 1 = 1 ORDER BY `table0`.`id` ASC LIMIT 100 ) AS `subq6` ``` For pgSql: ```psql SELECT COALESCE(jsonb_agg(to_jsonb("subq6")), '[]') AS "data" FROM ( SELECT "table0"."title" AS "title", "table1_subq"."data" AS "publishers" FROM "public"."books" AS "table0" LEFT OUTER JOIN LATERAL (SELECT to_jsonb("subq5") AS "data" FROM ( SELECT "table1"."name" AS "name" FROM "public"."publishers" AS "table1" WHERE "table0"."publisher_id" = "table1"."id" ORDER BY "table1"."id" ASC LIMIT 1 ) AS "subq5") AS "table1_subq" ON TRUE WHERE 1 = 1 ORDER BY "table0"."id" ASC LIMIT 100 ) AS "subq6" ``` 2. Did manual testing to confirm that the relationship fields provided by user take precedence: (Creating issue here to add a test later:#2104) Config: <img width="242" alt="image" src="https://github.com/Azure/data-api-builder/assets/34566234/3bf60082-309c-4fc2-bd6b-d307061d61ba"> GraphQL query: <img width="282" alt="image" src="https://github.com/Azure/data-api-builder/assets/34566234/6f9bf50d-f679-4c6c-87a3-377fc86c423b"> Generated SQL query: <img width="954" alt="image" src="https://github.com/Azure/data-api-builder/assets/34566234/9b082354-1217-4ecf-b05b-263d5e2e5a13"> 3. Manually tested that for relationships which are not backed by an FK constraint, we infer both the source/target entities as the referencing entity. Test to be added post the merge of : #2056 --------- Co-authored-by: Shyam Sundar J <shyamsundarj@microsoft.com> Co-authored-by: Sean Leonard <sean.leonard@microsoft.com>

Add test to ensure user provided source/target fields take precedence #2104

Description

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions