Can't apply filter on JOIN table as it returns array(ANTI JOIN)

[sasweb]

Bug report

Describe the bug

I was trying to implement a query which would normally be a subquery. The setup is something like this:

Table 1: articles(id, title, ...)

Table 2, join table (many to many): ratings(article_id, user_id, was_rated) -> article_id and user_id = compound primary key

I need to get all articles which are not listed in ratings. As we can't use subqueries I was thinking of a join table and a simple filter like described in the docs.

Written in SQL it would look like this:

select * from articles full join ratings on id = ratings.article_id where ratings.was_rated is null

Works ✅ The join is also correct as I can see the column was_rated with value null

So I assume that this should work in supabase:

client
  .from('articles')
  .select(`id, title, ratings(*)`)
  .is('ratings.was_rated', null)

Doesn't work ❌

As far as I understand this should be the exact translation of the SQL query, right?

After some investigation I found out that it doesn't work because ratings is returned as an empty array [] instead of null. That's why the filter for null is not working. I also found out that an inner join returns the value like expected. But I can't work with inner joins for this use case.

I may miss something because I am still new to supabase (and PostgREST) but I would consider this a bug.

Expected behavior

Expect

client
  .from('articles')
  .select(`id, title, ratings(*)`)

NOT to return [] for rating but single value like the SQL query does.

Additional context

Also happy for suggestions how to structure my data to get all articles which are not listed in ratings.

[steve-chavez]

I need to get all articles which are not listed in ratings.

Seems you want an anti-join(left). This feature is on master(not yet deployed):

client
  .from('articles')
  .select(`id, title, ratings(*)`)
  .is('ratings', null)

You'll be able to try this on the CLI soon.

[steve-chavez]

Anti-joins are available on the CLI for now but you can request on production by emailing to support@supabase.com.

[sasweb]

Anti-joins are available on the CLI for now but you can request on production by emailing to support@supabase.com.

Really cool. Thank you very much!