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?

Try this option you are missing select function which is used to select before applying filter : query.select().not('id_user.image', 'in', null). Ref : https://supabase.io/docs/reference/javascript/not — Senthil, Sep 15 '21 at 13:18

score 10 · Answer 1 · answered Jan 16 '23 at 10:32

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

score 3 · Answer 2 · answered Sep 15 '21 at 12:01

3

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

answered Sep 15 '21 at 12:01

Marcus Duarte

31
3

that should read "as ui ON t.id" ... – vikingsteve Nov 27 '22 at 20:09
The link on this answer returns a 404. It should link to: https://supabase.com/blog/postgresql-views – andrevenancio Feb 15 '23 at 18:07

score 2 · Answer 3 · answered Feb 08 '22 at 15:06

2

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

answered Feb 08 '22 at 15:06

Dmytro Izotov

21
1

this is worked for me – Jagarkin Jul 19 '23 at 11:00

score 0 · Answer 4 · answered Sep 21 '21 at 19:16

0

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

answered Sep 21 '21 at 19:16

ize8

95
7

Is a embebed column, and is not posiblle atm https://github.com/supabase/supabase/discussions/3155#discussioncomment-1310299 – Facundo Serrano Sep 23 '21 at 15:11

score 0 · Answer 5 · answered Sep 23 '21 at 15:12

0

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.

answered Sep 23 '21 at 15:12

Facundo Serrano

43
2
12

score 0 · Answer 6 · edited Oct 14 '21 at 15:41

0

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

edited Oct 14 '21 at 15:41

Francois Vanderseypen

1,432
1
12
22

answered Oct 14 '21 at 14:43

Mark Burggraf

456
2
6

score 0 · Answer 7 · answered Jan 30 '23 at 13:18

0

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

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

answered Jan 30 '23 at 13:18

Mubashar Hussain

3
1
4

1

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Feb 01 '23 at 09:32

Filtering in join in supabase

7 Answers7