Foreign Key Smart Tags on Types

[machineghost]

Summary

I have a function that returns a indexed_foo type, which comes from a foo table. indexed_foo results are just a SETOF foo with an extra index column (JOIN-ed from another table), so a SELECT * FROM indexed_foo(5) returns:

id | foo_field_1 | foo_field_2 | ...other foo fields | index
5  | 'abc'       | 123         | ...other foo fields | 0

My function sort of works, but in the API the returned indexed_foo results only have the foo columns (eg. foo.baz_id), not their relations (eg. not foo.baz.qux). This makes sense, because while foo has those relations, indexed_foo doesn't.

The @foreignKey documentation mentions that types are supported, but ...

Composite types (one direction only)

I wasn't clear what that meant exactly: isn't @foreignKey always one direction, from the thing being tagged, to its foreign table?

So, I experimented, and added a @foreignKey to connect my indexed_foo type to foo:

COMMENT ON TYPE public.indexed_foo IS E'@foreignKey (id) references foo(id);

Since each indexed_foo has one foo_id, I expected an API structure of:

indexed_foo(id:5) {
  foo {
    ...foo fields

However, I instead got:

indexed_foo(id: 5) {
  fooById {
    ...array of objects with foo fields

That works (my client can extract the single record from the array), but it confused me. Each indexed_foo only has one id, so shouldn't that be a one-to-one relationship in the API, not a one-to-many?

Two (hopefully quick) questions:

1. Why does a COMMENT ON TYPE public.indexed_foo IS E'@foreignKey (id) references foo(id); result in indexed_foo getting an array child fooById (ie. one-to-many) when each indexed_foo only has one ID?
2. Is there any way to convert my array endpoint fooById into a single-child endpoint foo?

Additional context

Not that it matters, but foo is actually the recommendation_groups table I mentioned in a previous question. I've reduced it to foo here to keep the irrelevant details as abstract as possible, and focus on the API/relationship question.

OS: Linux (relatively recent version)
Node: Latest (or like a minor version behind)
PostgreSQL: 14.15
Postgraphile: 4.12.11 (I swear I'm going to upgrade to 5 soon ...)

[benjie]

Sounds like a bug. Which version?

[machineghost]

Damn you are fast (on a Sunday no less)! I updated the comment while you were responding.

[benjie]

Separately, a composite type of create type indexed_foo as (index int, foo foo) would likely serve your needs better.

[benjie]

Stuck waiting in the airport 😉

[benjie]

Please check if the same issue still exists in 4.14

[machineghost]

Separately, a composite type of create type indexed_foo as (index int, foo foo) would likely serve your needs better.

You could write a book full of great advice like this. :)

I'll try switching to that structure and see what I get.

EDIT: Will try updating first
EDIT 2: Updating to 4.14.0 didn't help; I'll try re-making the indexed type next

[machineghost]

I have a problem. Currently my function returns a query:

SELECT foo.a, foo.b, foo.c, etc., elsewhere.index

If I change the function's query, from foo.a, foo.b, foo.c to foo.* AS foo I get:

return type mismatch in function declared to return indexed_foo

How can I SELECT in a way that the results will be:

index int, foo foo

[benjie]

Drop the .* and make sure the values are in the same order as the composite type: select index, foo from foo left join …

Don’t close this issue if this solves it, it still sounds like a bug but likely one that’s fixed in V5 (but I want to check).

[machineghost]

Awesome (learned something new!)

Unfortunately, I still have an issue. I neglected to mention a detail: my function is doing a recursive query, so it has the same basic query multiple times. One of them looks like this (going back to recommendation_groups instead of foo to avoid messy find/replace):

 WITH RECURSIVE nested_groups AS (
    -- The new signature you suggest!
    SELECT rg AS recommendation_group, gc.index
    FROM recommendation_groups rg
      JOIN group_children gc
        ON gc.recommendation_group_id = group_id
      WHERE parent_group_id = group_id

    UNION ALL
      -- The new signature you suggested again, but ...
      SELECT rg AS recommendation_group, gc.index
      FROM nested_groups ng,
           recommendation_groups rg
      JOIN group_children gc
        ON gc.recommendation_group_id = rg.id
     -- This was:
     --   WHERE rg.id = ng.parent_group_id
     -- but now I'm not selecting the parent ID, and when I try doing this:
      WHERE rg.id = ng.recommendation_group.parent_group_id
     -- I get "missing FROM-clause entry for table "recommendation_group""
    )
    -- more query, but it doesn't matter

As you can see, I'm stuck on JOIN-ing on the parent_id, because now I'm not returning it, I'm returning a recommendation_group with it.

Do I need to change my signature to SELECT rg AS recommendation_group, gc.index, parent_id, or is there a "trick" to extract it from inside of ng.recommendation_group (when doing my JOIN)?

[machineghost]

I tried adding parent_id to the type, and partially broke my recursive query in the process ... but I think the basic approach will work! (Postgraphile returns the correct API "shape" ... I just need to get it to return all the records I want.)

I'll report back if I run into anything else, but hopefully I can fix the recursion and call it a day. Thanks so much, as always!

[machineghost]

Re-opening because I just noticed:

Don’t close this issue if this solves it

[benjie]

Since each indexed_foo has one foo_id, I expected an API structure of:

indexed_foo(id:5) {
  foo {
    ...foo fields

However, I instead got:

indexed_foo(id: 5) {
  fooById {
    ...array of objects with foo fields

I've attempted to reproduce this, but the fooById field is not a list type for me.

Here's the SQL I've used against a fresh new database:

create table foo (id serial primary key, name text);
insert into foo (name) values ('Caroline'), ('Bob'), ('Alice'), ('Dave');

create type indexed_foo as (
  id int,
  index int
);

comment on type public.indexed_foo is E'@foreignKey (id) references foo(id)';

create function indexed_foos() returns setof indexed_foo as $$
select
  foo.id,
  row_number() over (order by name asc)
from foo;
$$ language sql stable;

create function indexed_foo(id int) returns indexed_foo as $$
select *
from indexed_foos() t
where t.id = indexed_foo.id;
$$ language sql stable;

And then I ran PostGraphile v4.14 against it using the command line postgraphile -c postgres:///crystal2346

I then executed this query:

{
  indexedFoos {
    nodes {
      ...IndexedFoo
    }
  }
  indexedFoo(id: 4) {
    ...IndexedFoo
  }
}

fragment IndexedFoo on IndexedFoo {
  id
  index
  fooById {
    id
    name
  }
}

and received these results:

{
  "data": {
    "indexedFoos": {
      "nodes": [
        {
          "id": 3,
          "index": 1,
          "fooById": {
            "id": 3,
            "name": "Alice"
          }
        },
        {
          "id": 2,
          "index": 2,
          "fooById": {
            "id": 2,
            "name": "Bob"
          }
        },
        {
          "id": 1,
          "index": 3,
          "fooById": {
            "id": 1,
            "name": "Caroline"
          }
        },
        {
          "id": 4,
          "index": 4,
          "fooById": {
            "id": 4,
            "name": "Dave"
          }
        }
      ]
    },
    "indexedFoo": {
      "id": 4,
      "index": 4,
      "fooById": {
        "id": 4,
        "name": "Dave"
      }
    }
  }
}

I've defined both a function that returns a single record (indexed_foo) and a function that returns a setof records (indexed_foos) but neither of the represent the results of fooById as an array of objects with foo fields. Please can you include a full reproduction, as I have above?