M2m relation in retool

💬 Queries and Resources
1

Hi,

Is there any standard way to work with m2m relations in RETOOL ? usually we have intermidiary tables with relations how does that work out in a table / deleting and updating rows etc.

Can anyone point to a standard documentaion / resource on m2m crud , in RETOOL thanks .

2

Hi @mpmohi,

Great question, and part of a good foundation for a clean relational database (RDB).

In many-to-many (M2M) setups, you will typically use a join/intermediary table to manage relationships. CRUD operations on these tables allow you to add/remove associations without duplicating data.

Understanding referential integrity is really important. Deciding when to delete (or leave) records in another table. eg If you delete a company, you probably want to delete all of its addresses.

In Postgres (or SQL in general), you can use ON DELETE CASCADE on your foreign key constraints if you decide that related records should be automatically removed when the parent record is deleted.

Microsoft is a great resource for this. Here is a basic explanation.

In addition to this, it is also worth considering if you really want to delete a record or just remove its access. eg If you have a list of users, you could easily add a column status and have either active or archived. Then in your query you only display active users but still have access any data that may be useful.

Here is a good response from perplexity with links (ai that can search the web).

3

Thanks @Shawn_Optipath ,

I need to know how we can work with m2m in the context of retool. like tags can use m2m easily , but they don't (afaik) , so how do people usually work with these types of relations in retool.

There might be some stablished simple way that I am missing.

thanks for your reply

4

Hi @mpmohi,

Retool does not provide out-of-the-box, automatic handling of many-to-many (m2m) relationships (such as tags) in the same way as some ORMs or frameworks. However, there are established patterns and community best practices for working with m2m relations in Retool apps.

  1. Using Join Tables
    Schema: As in standard SQL, you use a join (association) table to represent the m2m relationship (e.g., post_tags for posts and tags).

CRUD Operations: All insert, update, and delete actions on m2m relations are performed by manipulating this join table directly.

  1. Displaying m2m Data (e.g., Tags as Pills or Lists)
    Query and Join: Use SQL queries or Retool's Query JSON with SQL to join your main table with the join table and the related table (e.g., posts, post_tags, tags) to fetch all related tags for each record.

Table Component: In the Table component, you can display related tags as a list or as "tags/pills" by mapping the joined results into an array for each row.

Tags Component: Retool's Tags component can be used to display or edit multiple tags for a record, but you must handle the mapping and updates yourself.

I always have pgAdmin open as well. There, I have my database linked and can visualize the tables, script easily, check for indexes, constrainsts, etc. The ERD comes in handy as well. DM me if you need help setting this up.

1 Like
5

@Shawn_Optipath thanks again for your detailed explanation and letting me know that it doesn't have native m2m support.

@Tess hi, is there any plan / feature requests open for native m2m support. If we can give somekind of support on this , it will help so many people with their usecases.

1 Like