Excessive joins within nested polymorphic table
- Dominant language
- TypeScript
- Stars
- 12.9k
- Forks
- 625
- Avg merge
- 5h 23m
- Merged PRs (30d)
- 24
Description
### Summary
In the reproduction provided below, the `Task` and `TaskTimeline` tables utilize polymorphic relationships (`relational` and `unionMember` modes, respectively).
When querying the `TaskComment` file, the generated SQL contains one identical `File` join for every `Task` type. For example, if there are 4 `Task` types, the generated query includes 4 identical joins to the same `File` table.
### Steps to reproduce
https://github.com/benjie/ouch/pull/46
run query:
```gql
query {
allTasks {
nodes {
taskTimelinesByTaskId {
nodes {
item {
... on TaskComment {
content
taskCommentFilesByTaskCommentId {
nodes {
fileByFileId {
id
}
}
}
}
}
}
}
}
}
}
```
### Expected results
expected query for TaskComment:
```sql
select
__task_comment__."content" as "0",
__task_comment__."id"::text as "1",
array(
select array[
__task_comment_file__."file_id"::text,
__file__."id"::text
]::text[]
from "public"."task_comment_file" as __task_comment_file__
left outer join "public"."file" as __file__
on (
/* WHERE becoming ON */ (
__file__."id" = __task_comment_file__."file_id"
))
where (
__task_comment_file__."task_comment_id" = __task_comment__."id"
)
order by __task_comment_file__."id" asc
)::text as "2"
from "public"."task_comment" as __task_comment__
where (
__task_comment__."id" = $1::"int4"
);
```
### Actual results
```sql
select
__task_comment__."content" as "0",
__task_comment__."id"::text as "1",
array(
select array[
__task_comment_file__."file_id"::text,
__file__."id"::text,
__file_2."id"::text,
__file_3."id"::text,
__file_4."id"::text
]::text[]
from "public"."task_comment_file" as __task_comment_file__
left outer join "public"."file" as __file__
on (
/* WHERE becoming ON */ (
__file__."id" = __task_comment_file__."file_id"
))
left outer join "public"."file" as __file_2
on (
/* WHERE becoming ON */ (
__file_2."id" = __task_comment_file__."file_id"
))
left outer join "public"."file" as __file_3
on (
/* WHERE becoming ON */ (
__file_3."id" = __task_comment_file__."file_id"
))
left outer join "public"."file" as __file_4
on (
/* WHERE becoming ON */ (
__file_4."id" = __task_comment_file__."file_id"
))
where (
__task_comment_file__."task_comment_id" = __task_comment__."id"
)
order by __task_comment_file__."id" asc
)::text as "2"
from "public"."task_comment" as __task_comment__
where (
__task_comment__."id" = $1::"int4"
);
```
Contributor guide
Assessment
This issue has not been assessed yet.