Struggling with Get requests to Supabase

Question

Im using Flutterflow with Supabase

I have two tables: classification and classifaication_parent_child. Classification has an id and type. Classifaication_parent_child has parent and child both referencing the id of the classification table. I wanted to return the types of childern of a parent

I think the only way to do this is with the Flutterflow API.

I threw for need to write a get request that returns the types of the children of a parent.

classification_parent_child?select=jsonb_build_object('child_type', classification.type)&join=classification on classification.id=classification_parent_child.child&parent=eq.PARENT_ID

classification_parent_child?select=concat('{"child_type": "', classification.type, '"}')&join=classification on classification.id=classification_parent_child.child&parent=PARENT_ID

classification_parent_child?select=classification.type as child_type&join=classification on classification.id=classification_parent_child.child&parent=eq.PARENT_ID

None of this works

Answer 1

Creating views might make things easier in this case.

You can define a view using SQL and query against the view from your FlutterFlow app. Supabase allows you to run queries against views just like tables.

Here is a quick sample, but you would most likely have to edit it to fit your needs.

create view classification_view
with (security_invoker=on) as
    select
        *
    from classification as parent
    left join classifaication_parent_child on parent.id = classifaication_parent_child.parent_id
    left join classification as child on classifaication_parent_child.child_id = child.id;