Supabase: filter rows based on if any of their one to many relations has property

Question

In supabase I have a list of tracks and each track can have multiple artists (kept in a separate table). I only want tracks in which at least one of the artists associated with that track contains a certain property, we'll say their genre has to equal rap.

  tracksWithAtLeast1ArtistWhoseGenreIsRap = await supabase
      .from("tracks")
      .select("name, artists ( genre ), id")
      .filter("artists.genre", "eq", "rap")

here is what i'm workin right right now but the issue is that I think this filters the selected artists within each track by genre rather than filtering the tracks based on if any of its artists has the genre "rap".

Answer 1

You can use the !inner keyword to filter your top level table (tracks) by its relational table (artists).

In the docs, it says

If you want to filter a table based on a child table's values you can use the !inner() function. For example, if you wanted to select all rows in a message table which belong to a user with the username "Jane":

In your case, the query could look like this:

tracksWithAtLeast1ArtistWhoseGenreIsRap = await supabase
 .from("tracks")
 .select("name, artists!inner( genre ), id")
 .eq("artists.genre", "rap")

You can read more about !inner in our documentation here!