Database relationships query types not working

[bombillazo]

Bug report

• [x] I confirm this is a bug with Supabase, not with my own application.
• [x] I confirm I have searched the Docs, GitHub Discussions, and Discord.

Describe the bug

Given a schema types definition for a table like so:

  account: {
        Row: {
          id: string;
          name: string | null;
          type: string | null;
        };
  ...
  },
  my_resource: {
        Row: {
          id: string;
          name: string | null;
          big_account_id: string | null;
          small_account_id: string | null;
        };
        ...
        Relationships: [
          {
            foreignKeyName: 'my_resource_big_account_id_fkey';
            columns: ['big_account_id'];
            referencedRelation: 'account';
            referencedColumns: ['id'];
          },
          {
            foreignKeyName: 'my_resource_small_account_id_fkey';
            columns: ['small_account_id'];
            referencedRelation: 'account';
            referencedColumns: ['id'];
          },
        ];

When querying data from my_resource, one gets errors or an empty type:

const query = `id,key:id,
  name,
  state_location_code,
  big_account_id(*),
  small_account_id(*)`

// resulting type
type MyResource = {
    id: string;
    key: string;
    name: string | null;
    big_account_id: {}[];
    small_account_id: {}[];
}

const query2 = `id,key:id,
  name,
  state_location_code,
  big_account_id(id,name,type),
  small_account_id(id,name,type)`

// result type
type MyResource = {
    id: string;
    key: string;
    name: string | null;
    big_account_id: SelectQueryError<"Referencing missing column `id`">[];
    small_account_id: SelectQueryError<"Referencing missing column `id`">[];
}

This only happens when one tries to query data using relations in a query; the account type works fine when querying it directly. It also works on other table types where the foreign key id is called account_id instead of not matching the foreign table name + _id.

To Reproduce

1. Create any 2 tables, with 1:1 foreign key relations on one of them.
2. Name the column something other than foreign_table + _id
3. Generate the types using the supabase CLI
4. Create a query that select fields belonging to the foreign table using (*)
5. The types will be empty or if specifying a field a QueryResult error will appear.

Expected behavior

The correct types are produced based on the relationships generated by the Supabase DB schema.

Screenshots

System information

• Version of supabase-js: 2.33.1
• Version of supabase-cli: 1.91.1
• Version of Node.js: 16.17.0

[bombillazo]

I think the issue is the column name, once the name does not match the foreign table name, the query breaks down for relations.

[bombillazo]

ok, it works if I use account(*), the query sees the foreign fields, but how can I specify I want to query both big and small accounts ?

[bombillazo]

Closing, no response for a year, no longer using postgrest, a new issue can be opened if required