https://supabase.com/ logo
Join Discord
Powered by
Hey folks! In my Supabase project I've got two tab...
# javascript
m
Hey folks! In my Supabase project I've got two tables called 
snapshots
and 
tracks
. They're connected in a many-to-many relationship via a join table called 
snapshots_tracks
. Apart from containing proper foreign keys, it has two extra columns named 
added_at
and 
position
. Now, I'm using the JavaScript SDK to find a 
snapshot
with an id equal to given 
snapshotId
, but I'd like my response to include a 
tracks
array with each track containing the aforementioned properites as well as all the other `tracks`' columns. While I'm aware that PostgREST is capable of detecting M-M relationships, I couldn't quite figure out how to craft a query described above. Here's what I have so far:
Copy code
ts
const { data } = await supabase
  .from<Snapshot>('snapshots')
  .select(
    `
    id,
    captured_at,
    description,
    num_followers,
    tracks(*)
  `
  )
  .throwOnError(true)
  .eq('id', snapshotId)
  .limit(1)
  .single();
b
You need to have a foreign key defined between the two tables (snapshots and tracks).
Sorry I misread the beginning part where you mentioned the many-to-many relationship. (It's been a long day :🥱 )
Typically when I get into a complex data model like this I handle it one of two ways:
1. Create views on the database and query those views
2. Create a PostgreSQL function and call that with 
.rpc()
3. (I know I said 2) Make a separate call to get the related data after-the-fact. not the best solution, but easiest to implement
m
Here 
entry_meta
is an array with a single object containing these properties
b
That gets you the joiner table info but not the info from tracks, though, no?
So you've flattened it to a one-many, which is good, though.
m
Archiving this now, thank you so much once again and have a good one!
@burggraf, just to tie up loose ends. I've opted to employ the second approach, ie. using Postgres functions and mapping each result to an appropriate 
track
object like so:
Copy code
ts
// "data" is a single snapshot record object

await Promise.all(
  data.tracks.map(async (track) => {
    const { data: foundEntry } = await supabase
      .rpc<SnapshotTrackEntry>('get_snapshot_track_entry', {
        snapshot_id_arg: snapshotId,
        track_id_arg: track.id
      })
      .throwOnError(true)
      .limit(1)
      .single();

    track.entry = foundEntry;
  })
);
b
👍
PreviousNext
Powered by