MySQL Tag System (3 tables) - How to select the last 10 published posts and all the tags associated to each post?

user_10668052 0
Viewed 238

I'm implementing a Tag System for my website, using PHP + MySQL.

In my database, I have three tables:

posts

id title datetime

Primary Key: id

tags

id tag slug

Primary Key: id | Key: slug

tagsmap

id tag

Primary Key: both

(id = post's id in posts; tag = tag's id in tags)

Every post may have one or more tags, or no tags, associated to it.

Now I need to select - and show on the same page - the last 10 (no less, no more) published posts and all the tags associated to those posts.

This is what I've tried:

SELECT 
  p.title, 
  t.slug, 
  t.tag 
FROM 
  posts p 
  LEFT JOIN tagsmap tm ON p.id = tm.id 
  LEFT JOIN tags t ON tm.tag = t.id 
WHERE 
  p.datetime <= NOW() 
ORDER BY 
  p.id DESC 
LIMIT 
  10

It works, but, doing so, posts having two tags are showed twice, while I need to show each post just once.

Then I've added

GROUP BY p.id

But, this way, I get only one tag for each post.

I don't know how to solve this problem (I'm not very experienced with MySQL).

Would you give me any suggestions?

SQL Fiddle

P.S.: What I need is a SQL-side (not PHP-side) solution (the best in terms of performance). Nevertheless, any further suggestions would be much appreciated.

mysql
2 Answers

First select the latest 10 posts and then join to the other tables.
Then group by post and use GROUP_CONCAT() to combine all slugs and tags in a comma separated string for each post:

SELECT p.title, 
       GROUP_CONCAT(t.slug ORDER BY t.id) slugs, 
       GROUP_CONCAT(t.tag ORDER BY t.id) tags 
FROM (SELECT * FROM posts WHERE datetime <= NOW() ORDER BY datetime DESC LIMIT 10) p 
LEFT JOIN tagsmap tm ON p.id = tm.id 
LEFT JOIN tags t ON tm.tag = t.id 
GROUP BY p.id
ORDER BY p.datetime DESC;

See the demo.

user_104988280

As others have said, GROUP_CONCAT is the 'answer'. But to make the schema better and faster, I suggest:

  • Get rid of slug (if possible). If "posts" need slugs, then put them into Posts. If a "slug" is a type of "tag", then treat it as such. For that matter, consider throwing category, date, author, etc, into Tags.

  • Combine tagmap and tag. The loss in normalization is more than made up for by avoiding the double-JOIN.

  • (for clarity) id should only refer to the current table; post_id should be used to refer to the posts table.

      CREATE TABLE Tags (
      post_id INT UNSIGNED NOT NULL,
      tag VARCHAR(191) CHARACTER SET utf8mb4 NOT NULL,
      PRIMARY KEY(post_id, tag),
      INDEX(tag)
  ) ENGINE=InnoDB;

  SELECT p.title, 
         ( SELECT GROUP_CONCAT(tag SEPARATOR ', ') tags WHERE post_id = p.id ) AS tags
      FROM posts AS p
      ORDER BY p.datetime DESC
      LIMIT 10;

When the tables get big, this formulation has the advantage of avoiding the "explode-implode" that is caused by JOIN + GROUP BY.

That subquery will return NULL when there are no tags; use COALESCE() if you want to turn NULL int '' or (no tags) or whatever.

When you get into "pagination", don't use OFFSET. Instead, "remember where you left off", See http://mysql.rjweb.org/doc.php/pagination

If you need the latest 10 posts with a certain tag, then see http://mysql.rjweb.org/doc.php/lists

user_17668310
Related