Hello... trying to reference the auth users table ...
# javascriptl
lucasb
07/19/2022, 3:45 AMHello... trying to reference the auth users table via a field called 'owner' in the query below..
Copy code
js
const { data, error } = await supabase
.from('Comments')
.select(`id, owner:users(id,email)`)
.eq('post_id', params.id);Could not find a relationship between 'Comments' and 'users' in the schema cachelucasb
07/19/2022, 3:46 AMusers is from auth
g
garyaustin
07/19/2022, 4:10 AMYou can not access the auth schema from the API. You need to copy to a public profile/users table as suggested in the docs, or use an rpc call with a security definer function to get to the auth.users table.
garyaustin
07/19/2022, 4:12 AMl
lucasb
07/19/2022, 12:42 PMthanks for the link!
lucasb
07/19/2022, 1:06 PMthe trigger seems vulnerable to mistakes lol.. do you have one that you use for this more robust than the docs?
lucasb
07/19/2022, 1:08 PMdoesn't the function need to check if the user id already exists?
lucasb
07/19/2022, 1:11 PM Copy code
-- trigger the function every time a user is created
create trigger on_auth_user_created
after insert on auth.users
for each row
when (old.id is distinct from new.id)
execute procedure public.handle_new_user();g
garyaustin
07/19/2022, 1:16 PMIf you are using auth to create a new user and it inserts a user row into auth how would the user exist in your public table?
If you want to deal with updates to auth.users, you need a 2nd function on update trigger, or you can have one trigger function look at the op (update, insert) and decide what to do.
l
lucasb
07/19/2022, 1:19 PMvalid point!
g
garyaustin
07/19/2022, 1:19 PMThis is the sum of my insert user trigger function (I don't deal with updates of email yet, and don't use user_metadata)
Copy code
create function handle_new_user() returns trigger
security definer
language plpgsql
as
$$
begin
insert into public.users (uid, email)
values (new.id, new.email);
return new;
end;
$$;garyaustin
07/19/2022, 1:21 PMI recommend not to use public.users as the table, public.profiles is a better choice. I regret picking users....
l
lucasb
07/19/2022, 1:21 PMha thanks.. i almost did go with users
g
garyaustin
07/19/2022, 1:22 PMIt just causes confusion with the same name as auth.users in code.
l
lucasb
07/19/2022, 1:23 PMyeah for sure, i can see that happening
lucasb
07/19/2022, 1:40 PMhow did you check to see what values the "new" object has when setting up this trigger? Feels like a lot of trial and error.. i guess i could disable it and login client side to check the user object
g
garyaustin
07/19/2022, 1:42 PMnew has all columns of the table the trigger is on.
l
lucasb
07/19/2022, 1:46 PMohh so auth.users ... hmmm don't see avatar url
lucasb
07/19/2022, 1:47 PMoh wait i don't need that in this case