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

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.

CodePudding user response：

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).