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

Answer 2

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);

Answer 3

This feature came up recently with the release of the support for PostgREST 9. You have to use !inner keyword, you can now filter rows of the top-level table.

   const query = supabase.from(`teachers`).select(
      `
        *,
        id_user!inner(image)
      `
    ).not("id_users.image", "is", "NULL")

Answer 4

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

Answer 5

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.

Answer 6

The Supabase client uses postgrest, so you can use all sorts of operators in your queries. See this page as a reference.

Answer 7

you can try like with inner joins by using the !inner operator in select

 const query = supabase.from(`teachers`).select(
  `
    *,
    id_user!inner(
      image
    )
  `
)