How do I insert values into a many:many relation table for posts and tags?

Question

I want to have multiple different tags on my posts so I could filter them later. I figured the best way to do that would be to have the next tables:

POSTS:
+----+-------------+
| ID | Other stuff |
+----+-------------+
| *  | *           |
+----+-------------+

TAGS:
+----+------+
| ID | TAGS |
+----+------+
| *  | *    |
+----+------+

AND TAG_POSTS:
+---------+--------+
| POST_ID | TAG_ID |
+---------+--------+
| *       | *      |
+---------+--------+

Now I have it set up so when someone posts the data is just inserted into the posts table after some checks.

If I understood it correctly I would need to check if each tag already exists with a select and insert it if it doesn't. But how do I enter the right values into the tag_post table?

INSERT INTO posts SET value1, value2... ; 
SELECT @@IDENTITY

This is for inserting my post and than getting it's id.

This would be in a for loop and would insert once for every tag entered.

SELECT id FROM tags WHERE tag = tag; 
//if results get returned then its already in and you have the ID

INSERT INTO tags SET tag; SELECT @@IDENTITY 
//this is for inserting the tag and getting its id if it's not in

Would saving both IDs in a variables and inserting it into the table in the middle do the job?

Also is there an easier way to do it? Calling MySQL 10x+ times for an insert seems a bit much to me. I will be using NodeJS to do this.

Maybe look into the INSERT INTO ... VALUES (...),(...) syntax? — Sam020
– Sam020, Commented Sep 29, 2021 at 13:18
Inserts work just fine. I'm just curious if there is an easier way to insert into the middle table. I'm on version 10.4.19-MariaDB — Nik
– Nik, Commented Sep 29, 2021 at 13:45

Salman Arshad · Accepted Answer · 2021-09-29 13:56:27Z

1

First you capture the id of the post:

INSERT INTO posts VALUES(...);
SET @postid = LAST_INSERT_ID();

Then you create the tags like so:

INSERT INTO tags(tag)
SELECT tag
FROM (VALUES -- MySQL 8 syntax; for MariaDB use CTEs
  ROW('foo'),
  ROW('bar'),
  ROW('baz')
) AS todo(tag)
WHERE NOT EXISTS (
  SELECT *
  FROM tags
  WHERE tags.tag = todo.tag
);

Finally you insert post-tag pairs:

INSERT INTO posts_tags(post_id, tag_id)
SELECT @postid, id
FROM tags
WHERE tag IN ('foo', 'bar', 'baz');

You might want to create unique indexes on tags(tag) and posts_tags(post_id, tag_id).

edited Sep 29, 2021 at 13:56

answered Sep 29, 2021 at 13:51

Salman Arshad

274k85 gold badges450 silver badges540 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Nik Over a year ago

Hey, can you just explain or link this syntax? I can't really get SELECT FROM VALUES, ROW('foo'), or SELECT FROM AS todo(tag) to work. I can only do SELECT FROM as todo without the (tag), and selecting each row with union...

Salman Arshad Over a year ago

This works in MySQL 8. For earlier you need union all to generate the 3 rows. In mariadb you need to use cte (dba.stackexchange.com/q/177312/12916 and stackoverflow.com/q/48511376/87015)

Collectives™ on Stack Overflow

How do I insert values into a many:many relation table for posts and tags?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related