Database & APIs
TK
Timur Kramár

Fetching related tables with supabase

I am using supabase views and then fetching them in FF. This proved as a great way of fetching data from related tables with one database connection. For example I can define a view for a Todo, which also includes all information about the creator, priority, location and more, basically anything that is stored in a separate table.

Now I am trying to make this work for one-to-many relationship and I am curios if anyone solved this, if it is even possible. I want to define a view, that will return details about a given user and all of their Todos.

Here is what I have in the backend so far:

create or replace view user_todos as
select
    user.*
    (select array(
        select row(todo.*)::public.todo from todo where todo.user_id = user.id
    ))::public.todo[] as todos
from users;

This view works in the database, even in the FlutterFlow, but the type of the todos column is an array of string:

I can manually change the PostgreSQL Type, but only to other postgreSQL primitive, not to the type of another table. Any ideas?

I can also alter the query like so:

create or replace view user_todos as
select
    user.*
    (select array(
        select to_jsonb(todo.*) from todo where todo.user_id = user.id
    )) as todos
from users;

This returns an array of jsonb and when I get the schama in FF i get:

This is considerably better, but working with JSON is much worse than the already defined types.

It would be best if FlutterFlow correctly identified the type of the column as todos[], and thus automatically converted the incoming data to the correct type, but it seems like that doesnt work for nested data just yet.

2
1 reply