获得最常用的标签

Question

Hello guys i am trying to get the most used tags from the database as show below:

+------------------------+
| post |       tags      |
+------------------------+
|  1   | ["php", "sql"]  |
|  2   | ["php", "html"] |
|  3   | ["css", "html"] |
|  4   |["php", "html5"] |
|  5   | ["php", "css"]  |
+------------------------+

I want to get the n most used tags in this table I hoping you guys can help me set up a SQL Query, i cant find myself a way to start, thanks!

Answer 1

You should probably consider re-designing your database to something like this:

Table: Posts
+=======================================================+
id    |    title    |    body    | user_id    |    etc..
+-------------------------------------------------------+

Table: Tags
+=====================+
id    |    name   
+---------------------+

Table: Post_tags
+======================+
post_id    |    tag_id
+----------------------+

Suppose you insert a new post with tags [php] and [html]. Then you will add the post to the Posts table, and add two rows to the Post_tags table, based on values stored in the Tags table:

Table: Posts
+========================================================+
id    |    title    |    body    | user_id    |    etc..
+-------------------------------------------------------+
1     |  New Post!  | Post body! |   2564     |    ...

Table: Tags
+======================+
id    |    name   
+----------------------+
  1   |    html
  2   |     php

Table: Post_tags
+======================+
post_id    |    tag_id
+----------------------+
 1         |   1
 1         |   2

Now, to get the most popular tags, you would just do something like this:

SELECT 
    tag_id,
    COUNT(tag_id)
FROM Post_tags
GROUP BY tag_id

which will give you a list of each tag and the number of times it has been used in a post.

Sorry, I know this doesn't exactly answer your question. If you really need to store the JSON style string of tags, you could do that as well in Posts, but to use that to get tag statistics is probably going to add far more complexity than necessary, and separating your data into relational tables is a much more effective way of doing things. Hope this helps!