graphile / graphile/crystal

Excessive joins within nested polymorphic table

Open
#3,036 1 comment 0 reactions 0 assignees View on GitHub
🐛 bug
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.