Supabase JS query question
i have a power_point table with a column for "media_id", it can be null or it can be a refernce to the media tables id column.
im trying to make this call
This works but returns nothing if media_id is NULL for all rows. Is there anyway to get that data when applicable but still return everything else without it when not?
Or what should my approach be here?
16 Replies
Does it return anything if you have an id to the media table?
Also not sure what media:media does. You are casting an alias to the same name you will get.
Normally you do media!inner if you only want parent table entries with an fk link valid. You are not doing that though, so not sure why it would filter the parent table.
oh you know what its not returning anything when i set an entry for media_id either
i do this in other places and it has worked fine, for example
but i guess in that table
last_user is the table name with the id referencing profiles, and its required, not nullable
whereas in this other table im talking about the column name is media_id, and its nullableIf you first query is exactly what you are sending I would guess you don't meet RLS on power_point as it should return all power_point rows with or without an fk to media.
ok i can check
because it returns fine when i drop the
media:media(*) part, with rls on for power_pointI assume you meet the select policy as you say dropping media works.
Try just media(*) without repeating media twice. Or change the alias to some other name. Not sure that would matter, but not seen that done before.
Also are you using next.js?
yes
i did try that
If so there could be caching involved if you are changing RLS or table data after you do a first select.
i have yea, and am using caching on my queries, ill poke around that idea
after consoling out the
error instead, i actually get somewhat helpful info
message: "Could not embed because more than one relationship was fou7nd for 'power_point' and 'media'
so this is because in my media table i added a column power_point_id referencing backSo you have more than one fk between the tables.
You should not do that.
You want just one table to do the linking.
in my media table i was trying to basically add an indicator that it was a power point item, but i guess i can use like a boolean or enumerated type
yea, that makes sense
works now after removing that, thank you
Or just have the link the other way.
right right
The joining will occur no matter which table you query as the parent.
that makes sense now haha
You many need to flush the PostgREST cache if you make these changes.
NOTIFY pgrst, 'reload schema' in the SQL editor.thank you!