Implementing many-to-many relationships with the UI

[gaurangrshah]

I must be missing something, but I'm currently experimenting with supabase, and I can't seem to figure out how to create many-to-many or one-to-many relationships using the UI. Are there any blog posts, that discuss this? I am more than sure that I'm missing something. I've read through the docs pretty much cover -to-cover and can't seem to find anything other than this: https://supabase.io/docs/guides/database#relationships

Any help/suggestions/guidance would be greatly appreciated! Thanks in advance for the help!

[kiwicopple]

Hey @gaurangrshah, a many-to-many relationship is created by essentially creating 2 tables, then a final "join" table to connect these together.

manytomany.mov

[GaryAustin1]

@hansaliyad1 This is a pretty old issue, with alot of different comments added to be joining.
There could be several different things you are doing wrong as you don't really say what your issue is and set up is.
Did you make sure both foreign keys are part of the primary key?

[hansaliyad1]

Hey @GaryAustin1,

This is my first time posting to this community. I have been working on Supabase with Flutter for the past 2 months, so I am fairly new. I am encountering an error when trying to run the query below. The error I'm getting is: 'Could not find a relationship between 'profiles' and 'workflows' in the schema cache.'

final data = await _supabase 
  .from('profiles') 
  .select(', workflows()');

I have three tables that set up a many-to-many relationship:

1. 'profiles'
2. 'workflows'
3. 'rel_profiles_workflows'

I am pretty sure I am missing something here, but I'm not sure what it is. I have attached images below to show you how my tables and relationships are set up.

Profiles table:

Workflow table:

rel_profiles_workflows table:

Relationship between profile and rel_profiles_workflows table:

Relationship between workflows and rel_profiles_workflows table:

I would appreciate any advice or assistance in this matter. Thank you!

[GaryAustin1]

Both of your foreign keys in rel_profiles_workflows need to be marked as part of the primary key (there can be multiple columns in the primary key). This is a requirement for the API to look at them in a join table.

[hansaliyad1]

@GaryAustin1 You are the Savior!! That was it. I did had to re-create the table to mark the foreign keys as part of the primary key. I was getting "rel_profiles_workflows_pkey" of relation "rel_profiles_workflows" does not exist error when I was trying to mark the foreign keys as part of the primary key.

[visualjerk]

I ran into the same issue with an old relation table, that could not be updated. The migration was pretty straightforward:

• In "Table Editor" select the old relationship table > select all rows (make sure to click "Select all XXX rows") > Export to CSV
• Create the new relationship table with the same row names as the old one and foreign keys marked as part of the primary key
• Select the new table > Insert > Import data from CSV
• If needed: Copy the RLS policies from the old table to the new one

Hope it helps anyone experiencing this issue.

[gaurangrshah]

Worked like a charm, thanks...I could swear I tried this, but I def. must've done something wrong in the process. Either way thanks a ton!

[borngraced]

@kiwicopple sorry, but what If I want to insert to many-to-many relation using supabase api? currently, I'm able to do that effortlessly with prisma api.

code sample using Prisma

const publish = await prisma.job.create({
      data: {
        title,
        uri,
        body,
        published,
        lead,
        Profile: { connect: { id: userId } },
        expired: false,
        company: { connect: { id: company } },
        contract: { connect: { id: contract } },
        experience: { connect: { id: experience } },
        location: { connect: { id: location } },
        categories: { connect: categories },
        created_at: new Date(),
        updated_at: new Date(),
      },
    });

using supabase api

const user: any = supabase.auth.user();
      const newJobDetails: any = {
        title: dataJob.title,
        uri: dataJob.body,
        lead: dataJob.lead,
        body: dataJob.body,
        contract: dataJob.contractId,
        experience: dataJob.experienceId,
        location: dataJob.location,
        company: dataJob.companyId,
        published: dataJob.published,
        userId: user?.id,
        categories: [{"id":2}, {"id":4}],
        created_at: new Date(),
        updated_at: new Date(),
      };

      const { data, error } = await supabase
        .from("job")
        .insert([newJobDetails]);

[soedirgo]

I may be missing something, but inserting to many-to-many relationship is just inserting into the join table right?

[borngraced]

Yes but programmatically.

[trinhdh]

@Samuelonoja Yeah. Thanks. Hope supabase will have it in the future release. What is your experience using Prisma ORM ? i never tried that before.

[borngraced]

Oh, Prisma ORM I'll consider a great tool....I'd def recommend it to anyone

[mitchellb613]

This is the first page that came up when searching for a way to connect many to many relationships through the supabase api like in prisma so I thought I would share the solution I found. In order to do it with 1 query from the client we need to create a postgres function as a remote procedure call. Here is what I came up with for a todolist to create a task with related tags:

CREATE OR REPLACE FUNCTION add_task_with_tags (input_owner_id uuid, input_title text, input_description text DEFAULT NULL, input_priority text DEFAULT NULL, input_due_by timestamp DEFAULT NULL, input_tag_ids int[] DEFAULT NULL)
    RETURNS SETOF tasks
    AS $$
DECLARE
    new_task_id int;
    input_tag_id int;
BEGIN
    INSERT INTO tasks (owner_id, title, description, priority, due_by)
        VALUES (input_owner_id, input_title, input_description, input_priority, input_due_by)
    RETURNING
        id INTO new_task_id;
    IF input_tag_ids IS NOT NULL THEN
        foreach input_tag_id IN ARRAY input_tag_ids LOOP
            INSERT INTO tags_to_tasks (task_id, tag_id)
                VALUES (new_task_id, input_tag_id);
        END LOOP;
    END IF;
    RETURN query
    SELECT
        *
    FROM
        tasks
    WHERE
        tasks.id = new_task_id;
END;
$$
LANGUAGE plpgsql;