Question 205238 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Filter rows by foreign key, but keep foreign data unchanged

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?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Thanks to @Abstract_Monk I came up with a workaround. I now use two requests, the first to filter the genres which returns just the ids. The second request fetched all the data I want to show later in my application but uses the ids to filter the bands:

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

if (error) {
  throw error
}

const { data } = await supabase
  .from('bands')
  .select('*, genres:j_band_genres(*)')
  .in('id', ids.map(item => item.id))

  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search