1

This might sound little stupid but i don't have much experience in Mysql and database related things.

I have a table named posts and it has a field (Tags). Below is the sample data.

ID, Title,           Tags
1   Friend Title       Friend, School, City
2   College            College, City
3   Work               Work, City, Friend

Tags are comma separated.

I need to get the count of these tags. I am Expecting

School - 1
City - 3
College - 1
Work - 1

something like this.

I know before asking the question we need to show some code or query but i don't know where to start. I am not looking for complete solution.

Just guide me what to use then i will try.

Edit As advised by Forpas i have anohter table which contains the name of the tags. That looks like below.

Tagid  Tag      active
1      School    0
2      College   0
3      City      0
4      Home      0
5      Hotel     0
Roxx
  • 3,738
  • 20
  • 92
  • 155
  • Is there a table Tags? – forpas Jun 21 '20 at 08:25
  • @forpas yes i have a table for tags.I have updated the details in question. – Roxx Jun 21 '20 at 08:26
  • The column `active` in the table `tags` cries for a better name. When looking at it for the first time i thought this field was there to indicate if the tag as `active` or `not active`. – Luuk Jun 21 '20 at 08:56

3 Answers3

1

Join the tables and aggregate:

select t.tag, count(p.id) counter
from tags t inner join posts p
on find_in_set(t.tag, p.tags)
group by t.tag

The function find_in_set() used in the ON clause will work if there are no spaces after the commas in the column tags of the table posts. If there are spaces then replace() must be used to remove them.

See the demo.
Results:

| tag     | counter |
| ------- | ------- |
| City    | 3       |
| College | 1       |
| School  | 1       |
| Work    | 1       |
forpas
  • 160,666
  • 10
  • 38
  • 76
  • thanks for the answer. Let me check the space part and will let you know. Voted up. – Roxx Jun 21 '20 at 08:37
  • There are no space in between the tags but thanks for keeping that in answer. It can help others. Just want to understand one thing. If tags name not present in posts but exist in tags field then what happen. – Roxx Jun 21 '20 at 08:43
  • I want to show the count of tags those exist in posts table. – Roxx Jun 21 '20 at 08:46
  • Thanks for the answer. Do you think it is a good practice? Or is there any better way to handling such scenarios. – Roxx Jun 21 '20 at 08:50
  • Storing comma separated lists is not a good practice in general. Depending on what you want to do it may be hard to solve other types of problems. It would be better to normalize your data. – forpas Jun 21 '20 at 08:52
  • 1
    Thanks i will read more about it. Your solution is working fine. I will raise new question if required. – Roxx Jun 21 '20 at 08:55
1

Using WITH, works only in Mysql8.0+:

with tags as (
 select 'School' as tag union 
 select 'City' union 
 select 'College' union 
 select 'Work') 
select tag, count(*) from (
  select tag 
  from tags
  left join posts on instr(posts.tags,tags.tag)>0
) x
group by tag
;
Luuk
  • 12,245
  • 5
  • 22
  • 33
  • Thanks for the answer. I need to read it as its goes over my head. :) – Roxx Jun 21 '20 at 08:48
  • If there is a Tag `'Working'` the function instr() will count it in the tag `'Work'` also. – forpas Jun 21 '20 at 08:55
  • Yes, but with `FIND_IN_SET()` you also have problems. Like: `select find_in_set('City, Friend, Work','Work');`, which returns `0`. So one should always try to understand the usability of the function which is used.... – Luuk Jun 21 '20 at 09:00
  • Read about find_in_set() before you post: the 1st argument is the string to search and the 2nd is the list: `select find_in_set('Work', 'City, Friend, Work');` – forpas Jun 21 '20 at 09:06
  • Ok, the problem is that with INSTR the parameters are the other way around, but still `select find_in_set('Work','City, Friend, Work');` returns `0`. – Luuk Jun 21 '20 at 09:07
  • It returns 0 because there is a space after the comma. I mention this case in my answer. Read about find_in_set() before you post. – forpas Jun 21 '20 at 09:08
  • And you should read the comment i gave above ("So one should always try to understand the usability of the function which is used....") – Luuk Jun 21 '20 at 09:09
  • It is obvious that you did not understand the usability of find_in_set(). I hope that now you do understand. Read the documentation. And instr() is not a solution because it returns wrong results. – forpas Jun 21 '20 at 09:12
  • You are missing the point that i am trying to make, which is a pity, but... – Luuk Jun 21 '20 at 09:17
  • The point is: (1) you did not know how find_in_set() works and (2) you posted code that may return wrong results. – forpas Jun 21 '20 at 09:24
0

Depending on how many tags do you have, if you have only a few, you could just make an integer variable for each one of them and then make a bunch of if statements, that would add 1 to the variable that has the name of the tag.

Oliver Hnat
  • 797
  • 4
  • 19