Resource embedding with a one-to-one view

[Schniz]

Hi there! I'm trying to use Supabase (with PostgREST 😃), and I have the following schema:

• table auth.users

  • id
  • email
  • other columns by supabase...

• table profiles

  • id
  • display_name

• view derived_avatars that joins profiles with auth.users

  • profile_id
  • url (uses auth.users.email to generate the URL)

Extended definition from psql

postgres@localhost:postgres> \d+ profiles;
+--------------+--------+-------------+-----------+----------------+---------------+
| Column       | Type   | Modifiers   | Storage   | Stats target   | Description   |
|--------------+--------+-------------+-----------+----------------+---------------|
| id           | uuid   |  not null   | plain     |          |         |
| display_name | text   |  not null   | extended  |          |         |
+--------------+--------+-------------+-----------+----------------+---------------+
Indexes:
    "profiles_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "profiles_id_fkey" FOREIGN KEY (id) REFERENCES users(id)
Has OIDs: no
Time: 0.033s

postgres@localhost:postgres> \d+ derived_avatars;

+------------+--------+-------------+-----------+---------------+

| Column     | Type   | Modifiers   | Storage   | Description   |

|------------+--------+-------------+-----------+---------------|

| profile_id | uuid   |             | plain     |         |

| url        | text   |             | extended  |         |

+------------+--------+-------------+-----------+---------------+

View definition:

SELECT profiles.id AS profile_id,

'https://www.gravatar.com/avatar/'::text || md5(lower(users.email::text)) AS url

FROM profiles

JOIN users ON users.id = profiles.id;

I'm trying to query with a one-to-one mapping. Calling GET http://localhost:8000/rest/v1/profiles?select=display_name,derived_avatars.profile_id(*) I get the following error:

{
  "message": "Could not find foreign keys between these entities. No relationship found between profiles and derived_avatars"
}

Looking at the documentation, seems that embedding views should work. Why would that be? Am I doing something wrong?

At first I thought that maybe it's related to the join over auth.users, but I could not embed the following simple view as well:

SELECT profiles.id AS profile_id,
      'https://hello/'::text || profiles.id AS url
FROM profiles;

Any assistance will be much appreciated!
Thanks in advanced 😃

[steve-chavez]

Hey @Schniz,

Looking at the documentation, seems that embedding views should work. Why would that be? Am I doing something wrong?

Yes, embedding views work as long as there are foreign key relationships on the tables it's based on.

The derived_avatars view is basically the profiles table underneath, and there's no foreign key relationship between profiles and profiles - so it's not really a one-to-one relationship.

For this case, you could make a VIEW of the users table(hiding some columns), say my_users, and then you should be able to embed profiles with my_users(/profiles?select=*,my_users(*)).

[steve-chavez]

I think you could also make your original view work if you change it like this:

SELECT users.id AS profile_id, -- the base table for this view now would be users
'https://www.gravatar.com/avatar/'::text || md5(lower(users.email::text)) AS url
FROM profiles
JOIN users ON users.id = profiles.id;

[Schniz]

Amazing! Works! Thank you! 😃