graphile / graphile/crystal

It should be possible to declare a shared `@ref` only in the base table when using relational polymporphism

Open
#2,087 2 comments 0 reactions 0 assignees View on GitHub
Dominant language
TypeScript
Stars
12.9k
Forks
625
Avg merge
5h 23m
Merged PRs (30d)
24

Description

### Feature description

*Note: I'm talking about "derived tables" as in "derived class" from OOP, not as in [PostgreSQL table inheritance](https://www.postgresql.org/docs/current/tutorial-inheritance.html)*

I have a `Profile` type which can concretely be an instance of `Individual` or `Organization`. Each profile can also be a member of one or more organizations (`OrganizationMember`). I therefore wish to define this property on the base table (using `@ref` and `@refVia`):

```sql
comment ON TABLE dansdata.profiles IS $$
@name Profile
@interface mode:relational type:type
@type individual references:individuals
@type organization references:organizations
@ref organizations to:OrganizationMember plural
@refVia organizations via:(id)->organization_members(member_id)
$$;
```

However, this does not work. Instead, I must define the property on each of my derived tables as well (Postgraphile is kind enough to throw an error if I don't so at least I won't forget):

```sql
comment ON TABLE dansdata.individuals IS $$
@name Individual
@ref organizations to:OrganizationMember plural
@refVia organizations via:profiles;(id)->organization_members(member_id)
$$;

comment ON TABLE dansdata.organizations IS $$
@name Organization
@ref organizations to:OrganizationMember plural
@refVia organizations via:profiles;(id)->organization_members(member_id)
$$;
```

It would be really nice if Postgraphile was able to infer the relationship based on the base table only. It would reduce code duplication and reduce the risk of bugs.

### More complete SQL

```sql
CREATE TYPE dansdata.profile_type AS ENUM('organization', 'individual');

CREATE TABLE dansdata.profiles (
id UUID DEFAULT gen_random_uuid () PRIMARY KEY,
type profile_type NOT NULL,
name TEXT NOT NULL
);

comment ON TABLE dansdata.profiles IS $$
@name Profile
@interface mode:relational type:type
@type individual references:individuals
@type organization references:organizations
@ref organizations to:OrganizationMember plural
@refVia organizations via:(id)->organization_members(member_id)
$$;

CREATE TABLE dansdata.individuals (
id UUID PRIMARY KEY REFERENCES dansdata.profiles (id) ON DELETE cascade
);

comment ON TABLE dansdata.individuals IS $$
@name Individual
# These two should not be necessary!
@ref organizations to:OrganizationMember plural
@refVia organizations via:profiles;(id)->organization_members(member_id)
$$;

CREATE TABLE dansdata.organizations (
id UUID PRIMARY KEY REFERENCES dansdata.profiles (id) ON DELETE cascade
);

comment ON TABLE dansdata.organizations IS $$
@name Organization
# These two should not be necessary!
@ref organizations to:OrganizationMember plural
@refVia organizations via:profiles;(id)->organization_members(member_id)
$$;

CREATE TABLE dansdata.organization_members (
organization_id UUID REFERENCES dansdata.organizations (id) ON DELETE cascade,
member_id UUID REFERENCES dansdata.profiles (id) ON DELETE cascade,
CHECK (organization_id <> member_id),
title TEXT,
PRIMARY KEY (organization_id, member_id)
);

comment ON TABLE dansdata.organization_members IS $$
@name OrganizationMember
$$;
```

### Supporting development

I [tick all that apply]:

- [ ] am interested in building this feature myself
- [ ] am interested in collaborating on building this feature
- [x] am willing to help testing this feature before it's released
- [ ] am willing to write a test-driven test suite for this feature (before it exists)
- [ ] am a [Graphile sponsor](https://www.graphile.org/sponsor/) ❤️
- [ ] have an active [support or consultancy contract](https://www.graphile.org/support/) with Graphile

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.