How would you filter a JOIN query in Supabase Plugin?

[Broberto]

Hey, I’m trying to filter JOIN statement in WeWeb’s Supabase Plugin.
I have a query like this in SQL

In Supabase I got this far

But when it comes to filtering, it is pretty tough,

[Matthieu]

Hey Rob,

There’s 2 things you can do here.

1. You can join in WeWeb the two tables you need :
   
   Here I tell table steps that it’d be nice to get the label from the type on the table linked by the type_id.
   You should be able to filter then.

BUT : i don’t like this approach anymore (for perfs). So solution 2 :
I tend to create VIEWs in Supabase
create view myview as select * from table1 join table2 on…
and then use that view as a collection in WeWeb. That’s waaaay faster (according to me anyway).

I hope it helps.
Have a nice night.

Matth-

[Broberto]

Hey Matthieu, thanks for your view on the topic
I’m not sure if I can go with views because they dont seem to support row level security. And as for the first proposal, I’m already joining tables with standard joins, but I’m unable to filter the joined result because it gives me back array and then the thitd screenshot happens, with an error also, which is not optimal either, as I can only access the array it seems.

I really appreciate your input though maybe @Alexis would know of a workaround

[Broberto]

Basically I’m doing the first one you mentioned but the filter seems to not work on the joined array result, as it treats it as a column for some reason?

[Broberto]

[Matthieu]

@Broberto

oposal, I’m already joining tables with standard joins, but I’m unable to filter the joined result because it gives me back array and th

Yes they do actually.
If you created your view with the correct syntax, the view will only show what the table can display to your user through the RLS.

drop view public.myview;
create view public.myview with (security_invoker=on) as select………

So yes to views

As for the filters, I have sometimes troubles to filter collections in WeWeb when the first row is not filled with non null values.

[Matthieu]

@Broberto

e first one you mentioned but the

I think you have to list what you want for all those tables instead of the stars.

employee_id,
name,
surname,
job: employee_id(job_id, date)

[Broberto]

If you check out, I corrected it, in the comment higher. When I filter it, I get an error though.

[Broberto]

@Alexis seems like it treats the array as a column. It would be fantastic, if instead there were the joined columns, that would be super fantastic : )

[Broberto]

Hmm, I might have to use views, but that is not really something I’d be wanting to do like always. As I also cannot filter them directly via a variable. But seems like the only viable option, security invoker is a new thing, isn’t it? Haven’t heard about it before

Edit: Seems like you were right and if this doesn’t get fixed somehow, views will be a good option. Thank you very much for the idea @Matthieu, could you elaborate on the performance concerns regarding the WeWeb “Advanced” WeWeb joins? I thought they were the same. I know there might be some JS behind it all, but in the end it is a call through SDK. Is it that the views are already “ready” there?

[Alexis]

I think what is missing is the ability to select a subproperty, or type what you want like “job.date” as field instead of only job

We plan to explore this blocker for the next supabase update

For now I think the only solution is view as @Matthieu suggested

Edit : And for the warning message when putting * symbol in the field, its a false flag, we forgot to handle this case when checking your input

[Broberto]

Hey, thanks for the answer! Is there any chance to see what features you have planned and the estimated date? You might want to post it here, so we can add some things that might be missing for us so you can consider adding them

[Matthew]

Super late response here but fwiw you can make an API call directly to supabase (not the plugin) using the following PostgREST format.

This example uses an email to look up what orders a particular profile has made (known by a profile_id on the order record, and we search the profiles table by email instead of profile id with an inner join)

*{your supabase url}*/orders?select=*,profiles!inner(email)&profiles.email=eq.test@example.com

1. /orders: This is the main endpoint we are querying, meaning we want to retrieve records from the orders table.
2. select=*,profiles!inner(email):

• * retrieves all columns from the orders table.
• profiles!inner(email) tells PostgREST to include data from the profiles table using an inner join on the foreign key.
  • Inner Join: This limits results to only orders rows that have a corresponding record in profiles.
  • (email): Specifies that only the email column from profiles should be included in the results.

3. profiles.email=eq.test@example.com:

• This filters profiles by email. Only records where profiles.email matches the specified email will be included, enforcing the filter through the join.

The combination of select=*,profiles!inner(email) with profiles.email=eq... ensures that:

• Only orders records with a related profile matching the email are returned.
• The inner join excludes any orders records without a matching profiles entry, filtering results to exactly what you need.

For more info see the PostgREST docs: Resource Embedding — PostgREST 12.2 documentation

[Broberto]

Hey, I meanwhile mastered Supabase and wrote a few articles including how to do this effortlessly without involving REST.

Become a Supabase Magician in WeWeb (Tutorial With Examples) – Broberto