Many-to-one embedded resource is array when using in view

[stefan-girlich]

I am using PostgREST via supabase-js & postgrest-js. My main table batches references batch_category via FK batches.batch_category_id and collections via batches.collection_id.
I have a view batches_flat which flattens columns from the referenced tables, e.g. collection name and table name. I am omitting the full view code for now; please tell me if you have a suspicion that could be validated by looking into the full view definition.

Client code

  const { data, error } = await supabase
    .from('batches_flat')
    .select('*, collections(*), batch_category(*)'))

Problem

batch_category is correctly embedded as a single object in the result whereas collections incorrectly is an array with a single entry:

  batch_category: { /* */  },
  collections: [ { /* */  } ],

I reduced the problem to this view:

create or replace view foo as
select
  b.id as batch_id,
  b.collection_id as ref_collection_id, -- fix
  collections.id as collection_id
from batches b
left join collections on b.collection_id = collections.id;

Without the line marked as "fix", I'd get PGRST200 ("Could not find a relationship between 'foo' and 'collections' in the schema cache") as mentioned in the docs. When using the line, a single object or null is embedded as expected.
I tried using the line in my original view definition:

select 
-- ...
-- collections.id as collection_id, -- works, but provides array for many-to-one
  b.collection_id, -- fails

This will fail with "Could not embed because more than one relationship was found for 'batches_flat' and 'collections'".

[laurenceisla]

I'm a bit confused with your first example. Did you mean to put collections instead of locations here?

    .select('*, locations(*), batch_category(*)'))

This will fail with "Could not embed because more than one relationship was found for 'batches_flat' and 'collections'".

Ah, maybe there's another FK column selected in the view that has a to-many relationship with the collections table. What's the full error here? In the hint there should be a message that says "Try changing 'collections' to one of the following...". If the fix you mention is like this in your view b.collection_id as ref_collection_id, then this should get the relationship that you want:

  const { data, error } = await supabase
    .from('batches_flat')
    .select('*, collections!ref_collection_id(*), batch_category(*)'))

The error shows the possible relationships in the details key, so the !<foreign_key> is used to disambiguate that.

[stefan-girlich]

My example has a copy-paste error; fixed it!

Thanks @laurenceisla ; the hint helped. It mentions using the FK relationship name rather than the column name as you suggested, but both options work. Update: I need to use collections!public_batches_collection_id_fkey(*); collections!collection_id(*) will return the right data, but the TypeScript types will incorrectly describe an array for collections.

I am surprised that public_collections_project_id_fkey is considered a relevant relationship since collections.project_id is a FK, not a PK. That's probably because collections.project_id is used in a join statement in the view definition

Full error object:

{
  code: 'PGRST201',
  details: [
    {
      cardinality: 'one-to-many',
      embedding: 'batches_flat with collections',
      relationship: 'public_collections_project_id_fkey using batches_flat(project_id) and collections(project_id)'
    },
    {
      cardinality: 'many-to-one',
      embedding: 'batches_flat with collections',
      relationship: 'public_batches_collection_id_fkey using batches_flat(collection_id) and collections(id)'
    }
  ],
  hint: "Try changing 'collections' to one of the following: 'collections!public_collections_project_id_fkey', 'collections!public_batches_collection_id_fkey'. Find the desired relationship in the 'details' key.",
  message: "Could not embed because more than one relationship was found for 'batches_flat' and 'collections'"
}

[laurenceisla]

It mentions using the FK relationship name rather than the column name as you suggested, but both options work..

Ah, yes, go with the foreign key instead, using the column could still cause ambiguity in some cases IIRC.

That's probably because collections.project_id is used in a join statement in the view definition

PostgREST does not check info inside the JOIN, it builds relationships by detecting the FK relationships in tables.

I am surprised that public_collections_project_id_fkey is considered a relevant relationship since collections.project_id is a FK, not a PK.

This is actually expected. When you select a column in the VIEW, PostgREST detects if it's part of a FK relationship. Either if it's the FK column referencing a PK or the other way around. Here's an example in the docs that explains this better.

[laurenceisla]

Also, to clarify. In this case, the ambiguity is caused not because you're selecting collections.project_id, but because you're selecting projects.id (I'm assuming it's projects.id as project_id?), which allows you to embed collections. If you remove it, your original request without disambiguation should work.

The collections.project_id column also detects a relationship, but it allows you to embed projects instead.

[stefan-girlich]

Brilliant. Thanks for the detailed explanations @laurenceisla , you helped me a lot.