Filter rows by foreign key, but keep foreign data unchanged

Question

Let's say I have a bands and a genres table who are connected through a join table called j_band_genres. Combined, this is my data:

ID	Names	Genres
1	The Beatles	Pop, Rock
2	The Rolling Stones	Rock
3	Boney M.	Funk, Disco

Now I'd like to filter for rock bands, so according to the supabase docs I write the following query:

const { data, error } = await supabase
  .from('bands')
  .select(`
    *,
    genres:j_band_genres!inner (
      name
    )
  `)
  .eq('genres.name', 'Rock')

Thanks to the !inner keyword this works as expected in the sense that I only get the Beatles and the Rolling Stones. But also the genres themselves are filtered, so the Beatles are now purely a rock band and the pop genre is missing:

ID	Names	Genres
1	The Beatles	Rock
2	The Rolling Stones	Rock

Is there any way in Supabase or PostgreSQL to filter the bands by genres but still keep all the genres?

Answer 1

I am not familiar with Supabase specifically, but the logic behind join tables is common to all SQL databases.

Logically, the join table you have provided in your question is right. In this case, the join table serves to establish a many-to-many relationship between bands and genres. However, if a band has multiple genres, each genre is represented as a separate tuple or row in the join table, like this:

j_band_genres

+----+--------------------+--------+
| ID | Names              | Genres |
+----+--------------------+--------+
| 1  | The Beatles        | Pop    |
| 2  | The Beatles        | Rock   |
| 3  | The Rolling Stones | Rock   |
| 4  | Boney M.           | Funk   |
| 5  | Boney M.           | Disco  |
+----+--------------------+--------+

When filtering by genre (such as Rock), the query usually employs a filtering condition in its WHERE clause. The query optimizer analyzes the query and the conditions to decide the most efficient way to retrieve the desired data. In this case, the optimizer selects only those tuples from the join table that satisfy the filtering condition, resulting in the inclusion of rock bands in the result set. Consequently, the genres themselves are filtered, resulting in the Beatles being considered purely a rock band and the Pop genre being excluded. In your question, let us call the final result set 'rock_bands'. So, from the j_band_genres table, only the tuples with IDs 2 and 3 will be selected into rock_bands.

To address this, one approach is to perform an additional join query. You can join the j_band_genres table with the rock_bands result set using the band name as a condition. This additional join operation allows you to obtain both the rock bands and their associated genres. This solution may not be the most efficient, but it can serve as a starting point for optimization.