How to show a many-to-many relationship in a table component as a list of tags?

[max.wiederholt]

Hello! I'm new to using Retool, and I'm trying to build a table that is similar to a demo data table (see screenshots below), but instead lists authors and the stories they have written.

Each row of the table will be a writer, and the second column of the table will be a list of stories that writer has written. Authors-to-stories is a many-to-many relationship, since each author may have written many stories, and each story may have many authors. (Screenshots of schema below)

How do I display a many-to-many relationship using a Retool Database in a Retool web app's table component as a list of format: Tags?

If I was solving this problem outside of Retool, I would likely query a SQL database from a web server once to get a list of all user IDs, names, and emails, then for each row in the table, query the SQL table with something like select title from stories where id in (select id from writers where user_id = {{row.userID}}), but I'm not sure how to do this for loop of SQL queries in Retool, or how to look up this information in the Retool docs.

Retool Web App:

Retool Database, users table:

Retool Database, stories table:

Retool Database, writers table (using foreign keys to map many-to-many relationship):

Thanks in advance!

[max.wiederholt]

Update: I solved this using Retool workflows:

Hope that helps anyone who faces a similar challenge!

[kschirrmacher]

I think that you can let the database do more of the work here and then just run a simple op on the resulting object.

Instead of select title from stories where id in (select id from writers where user_id = {{row.userID}}) you can just inner join the two tables.

select users.id, users.first_name || ' ' || users.last_name as full_name, stories.title from users
inner join writers on users.id = writers.user_id 
inner join stories on writers.story_id = stories.id

This gives you a row for every possible match based on the join table writers. It'll look something like:

Then you can use JS to reduce it down to one row per user.

return variable2.value.reduce((hash, value) => {
  // Desctructure the raw row data to remove the story_name.
  const {story_name, ...rest} = value
  //Each time we see an author we decide if we need to create a new nested object in the hash or update.
  if(hash[value.id]){
    //If an object already exists, push the story name into the array on the author object
    hash[value.id].story_names.push(story_name)
  }else{
    //Otherwise, create the object for the author and add the story name as a value in an array
    hash[value.id] = {...rest, story_names: [story_name]}
  }
  return hash
}, {}
)

The other option to simplify the JS a bit is to use a string_agg function in your query

select users.id, users.first_name || ' ' || users.last_name as full_name, string_agg(stories.title, ',') as titles from users
inner join writers on users.id = writers.user_id 
inner join stories on writers.story_id = stories.id
group by 1, 2

This would return one row per author and a comma separated value, which you'd then just need to split in a transformer.

return data.values.map((x)=> {
return {...x, titles: x.titles.split(',')}
})

I'd caution against this if you plan to modify the records from this table, though, as updating the values in the database gets tricky if it's already aggregated like that.