Supabase join and nested select

Question

Let's say I have 3 tables:

Table1 (id, col1, col2)
Table2 (id, col3, col4)
Table3 (table1_id, table2_id)

What I want to do is to join Table1 and Table2 and then select the rows with a specific column value (for example col4 == "123")

What I tried:

supabase.from("Table3").select(
 '''
  Table1 (
   col1,
   col2
  ),
  Table2 (
   col3,
   col4
  ),
 '''
).eq("Table2.col4", "123").execute();

The problem with this is that it returns a correct object with Table1 and Table2 attributes for the rows matching the query, but for the rows that does not match the query (and therefor should not be returned) it returns an object with Table1 attributes and Table2 attributes set to null.

Example with the above query:

Table1 |id | col1 | col2| |---|------|-----| |1 | val1 | val2| |2 | val3 | val4|

Table2 |id | col3 | col4 | |---|------|------| |3 | val5 | 123 | |4 | val7 | val8 |

Table3 |table1_id| table2_id| |---------|----------| |1 | 3 | |2 | 4 |

Result |col1 | col2 | col3 | col4 | |-----|------|------|------| |val1 | val2 | val5 | 123 | |val3 | val4 | null | null |

How do I prevent it from returning rows not matching the query?

score 4 · Answer 1 · answered Sep 28 '22 at 06:46

You can use !inner keyword for this. !inner allows you to filter the root table results by applying filters to the suitable.

supabase.from("Table3").select(
 '''
  Table1!inner (
   col1,
   col2
  ),
  Table2!inner (
   col3,
   col4
  ),
 '''
).eq("Table2.col4", "123").execute();

Supabase join and nested select

1 Answers1