I have a view that I have joined and it has multiple joins also.
It all works well until I try to create a new entry with some of the fields empty.
The it doesn’t show anything in the view at all until i populate all of the fields.
The reason I want to do this is because I am making my own todo app inside my app.
I have a button that i want to create a new new entry that gives me a blank record (and creates a new line in the todo app that i can then populate the information in and then update the record in the database.
The problem is that if i create a blank line in the todo table in Supabase it doesnt create the same entry in the todo “view” as some of the relationships fields aren’t populated.
Hi. To be sure to understand. You have a Todo table, the one you’re showing in the capture. But also a Todo_view, and you’re using this view in WeWeb ?
Is your question related to supabase or weweb ? Do your view contain the missing row on supabase side ?
Need more info, also show us the queries if possible.
I think it’s related to supabase. I’m mainly asking is there a way to force it to still create the todo_view even with the null values
create view
public.todo_view as
select
todo.todo_uid,
todo.due_date,
todo.is_complete,
todo.task,
todo.todo_status_id,
todo.todo_category_id,
todo.date_modified,
tdc.todo_category_name,
tds.status_name,
p.email,
p.first_name,
p.last_name,
p.profile_uid
from
todo
join todo_category tdc on tdc.todo_category_uid = todo.todo_category_id
join todo_status tds on tds.todo_status_uid = todo.todo_status_id
join profiles p on p.profile_uid = todo.assignee_id;
Or is there a better way than I am trying to do it maybe?
Do you send atleast the IDs by which you join?
Not to create a new line. But after the new line is created I was going to do the choosing in the app then.
I just wanted to create a blank record to be filled out in the app
Any errors? I think that you need to make your joins LEFT joins, this way you show the data, even if there is no joinable records (it will return null), so it would look something like this
create view
public.todo_view as
select
todo.todo_uid,
todo.due_date,
todo.is_complete,
todo.task,
todo.todo_status_id,
todo.todo_category_id,
todo.date_modified,
tdc.todo_category_name,
tds.status_name,
p.email,
p.first_name,
p.last_name,
p.profile_uid
from
todo
left join todo_category tdc on tdc.todo_category_uid = todo.todo_category_id
left join todo_status tds on tds.todo_status_uid = todo.todo_status_id
left join profiles p on p.profile_uid = todo.assignee_id;
Perfect. Thanks. It’s all working now as intended.
Yes, for anyone wondering, the issue is, that if you make JOIN, it takes only the properties that you can JOIN, if there is nothing to JOIN, then the record doesn’t happen, unless you use a LEFT JOIN, which then considers also the other side of the equation.
1 Like
Also please, protect your VIEWS with a flag security_invoker = true, this way, the view is protected by the RLS, that will call the JOIN as the exact user that is invoking it.
CREATE VIEW ... WITH (security_invoker = true) ... -- Rest of your query
Ok thanks. I don’t have RLS on any of my tables yet. It’s on my list of Todo’s. I couldn’t get it to work originally so I just disabled it until I can figure out the policies properly.
1 Like
Just out of interests sake do you need to protect your tables with rls if it is only used on a page that requires the user to be authenticated to access? These pages will never be public
Yes, because I as a logged in user, can go and break havoc if you don’t have your RLS in place. I can delete other users and make myself an admin, or even drop your tables.