How to work with joins that don't rely on foreign keys (unique)

[dns-developers]

I've got a situation where I'd like to be able to perform a join across two collections/tables where they match on two separate columns. I would like to query table_a joining on table_b and do not need to perform this join in the other direction. It's also worth noting perhaps that in table_b, the two columns we are trying to match on are not individually unique but work together as a composite primary key.

table_a	id	color	size	type
1	red	12	...
2	blue	15	...
3	green	1	...
4	blue	15	...

table_b	color	size	description
red	1	...
red	99	...
blue	15	...
red	12	...
blue	1	...
green	99	...

Desired Results:	id	color	size	type	description
1	red	12	...	...
2	blue	15	...	...
4	blue	15	...	...

The SQL to perform this:

SELECT a.id, a.color, a.size, a.type, b.description
FROM table_a AS a
LEFT JOIN table_b AS b
ON a.color = b.color AND a.size = b.size

How can I go about sorting this type of query via GraphQL? Any tips?

[olirice]

I wasn't sure on the direction your foreign key was supposed to go because the data in your example doesn't align fully in either direction but I think you're looking for

create table table_b (
    color text not null,
    size int not null,
    description text not null,
    primary key (color, size)
);

create table table_a (
    id int primary key,
    color text not null,
    "size" int not null,
    "type" text not null,
    foreign key (color, "size") references table_b (color, "size")
);

insert into table_b(color, "size", "description")
values
    ('red', 1, 'aaaa'),
    ('red', 99, 'bbb'),
    ('blue', 15, 'ccc'),
    ('red', 12, 'ddd'),
    ('blue', 1, 'eee'),
    ('green', 99, 'fff');

insert into table_a(id, color, "size", "type")
values
    (1, 'red', 12, '1111'),
    (2, 'blue', 15, '2222'),
    (3, 'green', 99, '3333'),
    (4, 'blue', 15, '4444');

Which will allow you to use the default tableACollection to perform your join.

{
  tableACollection(first: 1) {
    edges {
      node {
        id
        color
        size
        type
        tableB {
          description
        }
      }
    }
  }
}

which returns

{
  "data": {
    "tableACollection": {
      "edges": [
        {
          "node": {
            "id": 1,
            "size": 12,
            "type": "1111",
            "color": "red",
            "tableB": {
              "description": "ddd"
            }
          }
        }
      ]
    }
  }
}

[olirice]

I'd recommend using ^ approach but if you need the data to be flat for whatever reason, you can use a view to do any arbitrary join logic and expose it in your API https://supabase.github.io/pg_graphql/views/

you can experiment with both approaches using GraphiQL built into Supabse Studio. See the docs for how to view that for your project

[olirice]

closing due to inactivity