Mysql: Getting Count of Comma Separated Values With Like

Question

I decided to use favs (id's of users which marked that post as a favorite) as a comma separated list in a favs column which is also in messages table with sender,url,content etc..

But when i try to count those rows with a quey like:

select count(id) from messages where favs like '%userid%' of course it gives wrong results because all id's may be a part of another's

For example while querying for id=1 it also increase the counter for any other content that is favorited by user id 11...

Can you please tell me your idea or any solution to make this system work?

Answer 1

You might have to get the value, explode it with PHP and then count the array.

There are ways to do it in MySQL, but from what I've seen, they are a hassle.

Answer 2

With a few or's, you can have an ugly solution:

select count(id) from messages where favs like 'userid,%' or favs like '%,userid,%' or favs like '%,userid'

There's likely a more elegant solution, but that one will at least return the result you're looking for, I believe.

Answer 3

Is it possible to change your data model such that the association between users and their favorite messages is instead stored in another table?

Storing the associations in a single column negates the advantages of a relational database. You pay a performance cost using the like function, you can no longer store additional data about the relationship, and the data is harder to query.

An alternative model might looking something like this (can't include an image since I'm a new user, but I made one here):

users
 - id

messages
 - id

favorite_messages
 - user_id (foreign key to users.id)
 - message_id (foreign key to messages.id)

With that in place, your original query would be simplified to the following:

select count(1) from favorite_messages where user_id = userid

Additionally, you can do things like get a list of a user's favorite messages:

select 
    * 
from 
    messages 
    inner join favorite_messages 
     on messages.id = favorite_messages.message_id
where
    user_id = userid

Answer 4

should using this :

SELECT count(id) FROM messages WHERE FIND_IN_SET('userid',favs) > 0