Postgraphile: Clean reflection of many-to-many relations.

Created on 8 Aug 2016 · 20Comments · Source: graphile/postgraphile

I'm having trouble modelling many-to-many relations which reflect a straightforward GraphQL interface.

In the documentation, a schema like:

create table post (
  id serial primary key,
  author_id int non null references user(id),
  headline text,
  body text,
  …
);

can query relations like so:

{
  postNodes {
    nodes {
      headline
      body
      author: userByAuthorId {
        name
      }
    }
  }
}

Which works fine for one-to-many relations. However, I'm trying to write a schema which would, for example, allow a post to have many authors and still allow the same kind of straightforward queries:

{
  postNodes {
    nodes {
      headline
      body
      authors: someQueryName {
        name
      }
    }
  }
}

Using a relation table provides a postAuthorsByPersonIdAndPostId which seems tricky to use properly, and though I think I could write a query function providing computed columns, I'm not entirely sure that's the right way to proceed.

Then again, I'm basing my hacks on code from the kitchen sink example and integration code. I haven't found much mention of many-to-many relations here yet. 😢

✅ plugin-available 👨‍💻 Fix in v4 💅 enhancement

Source

engleek

👍15

Most helpful comment

Some pseudo-sql that may answer the question:

create table post (
  id serial primary key,
  headline text,
  body text
);
create table author (
  id serial primary key,
  name text
);
create table post_author (
  post_id integer references post,
  author_id integer references author,
  primary key (post_id, author_id)
);

create function "post_authorsByPostId"(p post)
returns setof author as $$
  select author.*
  from author
  inner join post_author
  on (post_author.author_id = author.id)
  where post_author.post_id = p.id;
$$ language sql stable;

Untested, tweak as necessary

benjie on 15 Jun 2017

❤9 🎉1

All 20 comments

Yep, this is a good space to explore. A computed column procedure (in this case) would actually work fairly well. If you write a procedure that returns setof person the procedure will return a connection. See the docs for connection procedure here. However, this is a common enough case that we should probably add automatic support for this. For the forum example we could add the concept of “stars” or “favorites” that exhibit a many-to-many relationship between posts and people.

The implementation for this would probably require detecting tables that have two one-to-many relationships and adding extra fields on the corresponding many-to-one tables (ideas on how this could be implemented would be great!).

I’m currently working on a refactor of PostGraphQL that will allow (in the future) for potential support of other database like MySQL and CockroachDB, support for other protocols like REST, richer authentication and subscriptions, and support for easy extension mechanisms. I’ll see how simple it is to roll out better support for many-to-many relationships with that. If it’s not easy I’ll try to get it implemented soon after.

In the mean time, a well written procedure should work great! If it doesn’t, that’s a bug in PostGraphQL and should be fixed. If you wanted to write a PR adding “favorites” to the forum example with a procedure to make selecting many-to-many easier, I’d gladly merge :blush:

calebmer on 8 Aug 2016

👍7

An update, this feature is actually not super easy to add and I’m not convinced there are not a lot of people who would benefit especially when a set returning Postgres function is an option. For now, I definitely recommend using a procedure. If there is enough interest in this feature, and/or a community PR I’ll reprioritize :+1:

calebmer on 18 Oct 2016

👍1

It took a while to get back here, sorry. Work, work, work…

I'm not expecting anyone to just add things for me, but was quite surprised more people didn't need such a thing. Posts with custom tags, for example, would warrant this.

However, I've been reading through the new schema readme in the forum example, which is in itself a wonderful course on how to write Posgres schemas. I see now that if I wrote a procedure just the right way, it would be added to a corresponding GraphQL type, and that's really really cool.

I had briefly thought that like some of the other features, Postgraphql could detect the junction tables which indicate many-to-many relationships, and I might work on that later.

Until then however, now that I understand procedures better, I can move back to using and enjoying Postgraphql :)

engleek on 8 Nov 2016

@engleek Don't be surprised, but actually I didn't expect this to work seamlessly if you don't ask correctly through tie table in graphql..
I expect that 1:n ties works and for each n, n:m as well so you just need walk through this intermediate table as well (as you need in SQL with joins) and you will get 1::m. I'm wrong?

But it is expensive...

langpavel on 9 Nov 2016

@calebmer Generic solution is not easy and it can not make sense sometimes..
And you have still purely generic solution.

Some inspiration how to do this with a customization

langpavel on 9 Nov 2016

I suggest this ticket is just a documentation task -- how do we do this with a procedure?

chadfurman on 14 Jun 2017

👍2

Some pseudo-sql that may answer the question:

create table post (
  id serial primary key,
  headline text,
  body text
);
create table author (
  id serial primary key,
  name text
);
create table post_author (
  post_id integer references post,
  author_id integer references author,
  primary key (post_id, author_id)
);

create function "post_authorsByPostId"(p post)
returns setof author as $$
  select author.*
  from author
  inner join post_author
  on (post_author.author_id = author.id)
  where post_author.post_id = p.id;
$$ language sql stable;

Untested, tweak as necessary

benjie on 15 Jun 2017

❤9 🎉1

I think it'd be cool feature.

As a reference - I'd recommend https://graph.cool

You can simply create many-to-many relation like:

And then set relation name:

and then query it like:

query {
  user {
    tasks {
      id
    }
  }
}

Note that graph.cool is hiding it's abstraction, so it might have some join tables created under the hood.

What do you think about having join table 'marked' somehow so postgraphql knows it's relation table and is able to add proper query fields.

pie6k on 17 Aug 2017

:+1: for computed columns. You're defining the many-to-many _in_ graph.cool, just with a wysiwyg. Therefore, we have to define the many-to-many in postgraphql, and the way we do that is with a computed column. Computed column procedures also gives us much more flexibility.

chadfurman on 27 Aug 2017

Has there been any more definitive solution on how to approach many-to-many relationships with postgraphile?

I'm pretty new to the DBing world and many-to-many relationships / permissions (on who can see what data in graphiql/graphql) are the only two things keeping me from using this package 100% all of the time.

DJuboor on 24 Dec 2017

Here is something I cobbled together to automate creation of the functions Postgraphile currently needs to glue many-to-many tables together until this issue gets resolved:

BEGIN;

CREATE TEMPORARY TABLE plurals("from" text, "to" text);
-- INSERT INTO plurals VALUES ...;

CREATE FUNCTION make_many_to_many(
    many_schema text, many_table text, many_column_1 text, many_column_2 text,
    schema_1 text, table_1 text, pk_1 text,
    schema_2 text, table_2 text, pk_2 text)
RETURNS void AS $$
DECLARE
    plural_2 text;
BEGIN
    SELECT "to" FROM plurals WHERE "from" = table_2 INTO plural_2;
    IF plural_2 IS NULL THEN
        plural_2 := table_2 || 's';
    END IF;

    EXECUTE format('CREATE OR REPLACE FUNCTION %I.%I(a %I.%I) ', schema_1, format('%s_%s', table_1, plural_2), schema_1, table_1)
         || format('RETURNS setof %I.%I AS $f$ ', schema_2, table_2)
         || format('SELECT %I.%I.* FROM %I.%I ', schema_2, table_2, schema_2, table_2)
         || format('INNER JOIN %I.%I ON %I.%I.%I = %I.%I.%I ', many_schema, many_table, schema_2, table_2, pk_2, many_schema, many_table, many_column_2)
         || format('WHERE %I.%I.%I = a.%I; ', many_schema, many_table, many_column_1, pk_1)
         || '$f$ LANGUAGE SQL STABLE';
END $$ LANGUAGE plpgsql;

CREATE FUNCTION add_many_to_many(schema_name text, table_name text)
RETURNS void AS $$
DECLARE
    fk1 RECORD;
    fk2 RECORD;
    count integer;
BEGIN
    CREATE TEMPORARY TABLE fks AS
        SELECT kcu.column_name,
            ccu.table_schema foreign_table_schema,
            ccu.table_name foreign_table_name,
            ccu.column_name foreign_column_name
        FROM information_schema.table_constraints tc
        INNER JOIN information_schema.key_column_usage kcu
            ON tc.constraint_name = kcu.constraint_name
            AND tc.table_schema = kcu.table_schema
        INNER JOIN information_schema.key_column_usage kcu2
            ON kcu2.column_name = kcu.column_name
            AND kcu2.table_name = kcu.table_name
            AND kcu2.table_schema = kcu.table_schema
        INNER JOIN information_schema.table_constraints tc2
            ON tc2.constraint_name = kcu2.constraint_name
            AND tc2.table_schema = kcu2.table_schema
        INNER JOIN information_schema.constraint_column_usage ccu
            ON ccu.constraint_name = tc2.constraint_name
            AND ccu.table_schema = tc2.table_schema
        WHERE tc.constraint_type = 'PRIMARY KEY'
            AND tc.table_schema = add_many_to_many.schema_name
            AND tc.table_name = add_many_to_many.table_name
            AND tc2.constraint_type = 'FOREIGN KEY';

    SELECT COUNT(*) FROM fks INTO count;

    IF count = 2 THEN
        SELECT * FROM fks LIMIT 1 INTO fk1;
        SELECT * FROM fks OFFSET 1 LIMIT 1 INTO fk2;

        EXECUTE make_many_to_many(
            schema_name, table_name,
            fk1.column_name, fk2.column_name,
            fk1.foreign_table_schema, fk1.foreign_table_name, fk1.foreign_column_name,
            fk2.foreign_table_schema, fk2.foreign_table_name, fk2.foreign_column_name);
        EXECUTE make_many_to_many(
            schema_name, table_name,
            fk2.column_name, fk1.column_name,
            fk2.foreign_table_schema, fk2.foreign_table_name, fk2.foreign_column_name,
            fk1.foreign_table_schema, fk1.foreign_table_name, fk1.foreign_column_name);
    END IF;
    DROP TABLE fks;
END $$ LANGUAGE plpgsql;

SELECT add_many_to_many(table_schema, table_name) FROM information_schema.tables WHERE table_schema = 'public';

DROP FUNCTION make_many_to_many;
DROP FUNCTION add_many_to_many;
DROP TABLE plurals;
COMMIT;

As can hopefully be seen, this scans for tables in the public schema that have two-column composite primary keys where the PK columns have FK references, which should handle most many-to-many tables. Insert to the plurals table as appropriate to handle any irregular pluralisations in the generated function name.

I’m not a Postgres expert, so there are probably some more optimal ways to do this, but this is working acceptably for the database I’ve been using to experiment with Postgraphile. I hope it is useful for others.

csnover on 21 Jul 2018

❤1

@csnover, could you provide an example of tables to which this would apply ?

jide on 22 Jul 2018

@jide The same as in https://github.com/graphile/postgraphile/issues/91#issuecomment-308664225.

csnover on 22 Jul 2018

Ok, thanks !

jide on 22 Jul 2018

Interestingly, with postgraphile 4, tables having a many to many relation from a two-column composite primary keys table already expose related content in generated queries. No need for a ad hoc function.

jide on 23 Jul 2018

Interestingly, with postgraphile 4, tables having a many to many relation from a two-column composite primary keys table already expose related content in generated queries. No need for a ad hoc function.

@jide Not sure what are you talking about, can you provide an example? I definitely don't see any generated query to get list of related entities without having to browse through the join table...