Filtering in join in supabase

Question

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?

Answer 1

This has now been implemented with PostgREST 9!

Here's an example:

const { data, error } = await supabase
  .from('messages')
  .select('*, users!inner(*)')
  .eq('users.username', 'Jane'

In your, case you'd have to do id_user!inner(image)

Source: https://supabase.com/blog/postgrest-9#resource-embedding-with-inner-joins