Im using supabase with a database which have 2 tables (that are implicates in this issue).
Tables are teachers and users. Both have ID and id_teacher/id_user respectively.
Im working in a query where i need to get all teacher, joining in users table, where theres a image column.
I need just to get the teachers where the user have an not null image.
const query = supabase.from(`teachers`).select(
`
*,
id_user(
image
)
`
)
This query works to get teachers joining in users table. Because i get my wanted response. This is a short example.
{
"id": 560,
"teacher_experience": 9,
"id_user":{
"image": "example-image.jpg"
}
}
The trouble is when i try to use some filter to avoid null images.
query.not('id_user.image', 'eq', null)
query.not('id_user.image', 'in', null)
query.ilike('id_user.image', 'null')
Are just an examples o filters tha i tryed for avoid the teachers which user.image have a null value.
Because, i want to NOT GET the entire item, but i get an item wiht a id_user = null
{
"id": 560,
"teacher_experience": 9,
"id_user": null // In this case image is null but still giving me the user
}
How is the correct form to solve this?
Just create a view in database for solve this problem. A view is a shortcut to queries and it possible apply where clause.
In sql editor on supabase https://app.supabase.io/project/{your_project_id}/editor/sql
create a new view with joins;
CREATE VIEW teachers_view AS
SELECT
t.*,
iu.image as image
FROM teachers as t
LEFT JOIN id_user as iu WHERE t.id = iu.teacher_id;
read more about left join here
and in application use
supabase.from('teachers_view').select().neq('image', null);
query.not("your_column_name", "is", "NULL")
worked for me!
odd enough, if you want to check for NULL
.filter("your_column_name", "is", "NULL")
seems to be the solution. No idea why it's not consistent
It is not possible atm.
You can see state of issue here.
Some posibilities are using views o start the query in the other table.