Search code examples
mysqlfavorite

"favorite" column mysql

Sorry for the question, it was a bit vague, let me rephrase my question.

Tables:

  • users: id, name
  • activities: id, when, tagId
  • tags: id, name

So the user has activities, each activity can have a tag for example "reading mail" would be a tag for an activity. So lets take an example:

  • user: 1, mike
  • activity: 1, today, 1
  • tag: 1, reading email

And the user mike has another activity:

  • activity: 2, tomorrow, 2
  • tag: 1, reading email

Now you can see the user "mike" has 2 activities with "reading email", so when the user creates a lot of activities, I need to get the favorite (most used) tag.

Solution

  • The following SQL will return the name of the tag that is used the most by a specific user (in this case with is 123)

    if you want a listing of tags based on how often they have been used, you can change the number in the limit 1.

    select tags.name 
from tags 
left join items on tags.id=items.tagId 
where items.userId=123
group by tags.name 
order by count(items.id) 
desc limit 1;