many-to-many to the same table

[steve-chavez]

Having posters that can subscribe to other posters:

create table test.posters(
  id int primary key,
  name text
);

create table test.subscriptions(
  subscriber int references test.posters(id),
  subscribed int references test.posters(id),
  primary key(subscriber, subscribed)
);

Currently there's no way to get a poster subscribers nor to what posters is a poster subscribed to:

GET /posters?select=*,posters(*)

{
  "hint": "By following the 'details' key, disambiguate the request by changing the url to /origin?select=relationship(*) or /origin?select=target!relationship(*)",
  "details": [
    {
      "relationship": "test.subscriptions[subscriptions_subscriber_fkey][subscriptions_subscribed_fkey]",
      "origin": "test.posters",
      "cardinality": "m2m",
      "target": "test.posters"
    },
    {
      "relationship": "test.subscriptions[subscriptions_subscribed_fkey][subscriptions_subscriber_fkey]",
      "origin": "test.posters",
      "cardinality": "m2m",
      "target": "test.posters"
    }
  ],
  "message": "More than one relationship was found for posters and posters"
}

This is because currently it's not possible to disambiguate a junction that has two foreign keys. This is noted here: https://github.com/PostgREST/postgrest/blob/b3899e7aa456a65002e9b36b8bc0b9c05c6113a7/test/Feature/EmbedDisambiguationSpec.hs#L100-L103

[steve-chavez]

Doing

GET /posters?select=*,posters!subscriptions_subscribed_fkey!subscriptions_subscriber_fkey(*) 

Seems too long, perhaps we can disambiguate with just the end of the FK. Like

GET /posters?select=*,posters!subscriptions_subscriber_fkey(*) 

That would get the subscribers through the junction.

[wolfgangwalther]

Doing

GET /posters?select=*,posters!subscriptions_subscribed_fkey!subscriptions_subscriber_fkey(*)

Would that mean that the order in which hints are given matters? And the order is given "backwards" from the target to the origin?

I like that.

Seems too long, perhaps we can disambiguate with just the end of the FK

GET /posters?select=*,posters!subscriptions_subscriber_fkey(*) 

It's easy enough to just give FKs proper (short) names - those autogenerated names should not be what we're looking at.

You can easily rename them so, that you can do both:

GET /posters?select=*,posters!subscribed!subscriber(*)
GET /posters?select=*,posters!subscriber!subscribed(*)

However, I like the idea of disambiguating multiple hints by "distance" to the target. So when you currently do

GET /posters?select=*,posters!subscriptions_subscriber_fkey(*)

it would be ambiguous and could mean both:

GET /posters?select=*,posters!subscriptions_subscribed_fkey!subscriptions_subscriber_fkey(*)
GET /posters?select=*,posters!subscriptions_subscriber_fkey!subscriptions_subscribed_fkey(*)

But if distance mattered, it would be clear that the second request is meant (because subscriptions_subscriber_fkey is right next to posters!).

I think this approach would be flexible enough to allow m2m2m etc. embeddings, too.

[gautam1168]

I don't understand how this is working. The resource embedding docs say that if a table has a foreign key constraint then the referenced table can be embedded in the resource by giving hints. But here it looks like the subscription table has the foreign keys. So how are you querying the posters resource and embedding subscriptions in it? Shouldn't it be the other way?

Also, I don't see any docs about chaining hints. Ill try to understand what is going on here by reading the code, but it would be great if someone could point me to some docs that explain this chaining of hints.

[steve-chavez]

Also, I don't see any docs about chaining hints. Ill try to understand what is going on here by reading the code, but it would be great if someone could point me to some docs that explain this chaining of hints.

@gautam1168 This would be a new feature, it's not possible chain hints right now.

[gautam1168]

ah I see

[nickrivadeneira]

I'm trying to do this now - in the absence of this new feature, how should this be solved currently? The use case is "related products". I have a join table with foreign key columns product_id and related_product_id. Would I need to do multiple queries here?

[wolfgangwalther]

Given the two tables in the first post of this issue, I'd do the following:

create view api.posters as select * from test.posters;

create view api.subscriptions as
select
  subscriber,
  id, -- it's important that this is not `subscribed`
  name
from test.subscriptions
join test.posters on id = subscribed

So basically move one of the two joins needed for m2m to a view.

Then you can query like /posters?select=*,subscriptions(*).

Note the comment in the view definition. Because the column posters.id is taken instead of subscriptions.subscribed (even though they have the same value), parsing the view will find only one FK for api.posters <-> api.subscriptions, which makes the embedding unambiguous.

[nickrivadeneira]

@wolfgangwalther Thanks! I had to add the foreign key as a hint, but it works now. Using the tables above, the foreign key I had to use would have been subscriptions_subscribed_fkey, which made my query look like /posters?select=*,subscriptions!subscriptions_subscribed_fkey(*). The view, table, column and foreign key names I have are different than the example, so my actual query looks different.

[steve-chavez]

Flattening could provide a workaround for this one:

GET /posters?select=*,subscribers:subscriptions!subscriber(...posters!subscriber(*))

[steve-chavez]

With https://github.com/PostgREST/postgrest/pull/2564, this now can be solved.

Using the above schema and data, to get a poster subscriptions:

GET /posters?select=*,subscriptions!subscriber(..posters!subscribed(*))&limit=1

[{"id":1,"name":"Mark","subscriptions":[{"id":2,"name":"Elon"}]}]

To get a poster subscribers:

GET /posters?select=*,subscribers:subscriptions!subscribed(..posters!subscriber(*))&limit=1

[ {"id":1,"name":"Mark","subscribers":[{"id":3,"name":"Bill"}, {"id":4,"name":"Jeff"}]}]