How to get all users which have more than 100 referrals?

Question

I have a service which has a referrer system. There's table users in service's database which contains id and referrers columns. Column referrer contains a string of referrer ids that separated by ,. Here's an example data from this table:

+------+---------------+
| id   | referrers     |
+------+---------------+
|    1 | 1             |
|    7 | 1             |
|    8 | 1             |
|    9 | 1             |
|   10 | 7,1           |
|   11 | 1             |
|   12 | 7,1           |
|   13 | 1             |
|   14 | 9,1           |
|   20 | 7,1           |

I need smt like: SELECT id, login FROM users u WHERE id IN (SELECT id FROM users u1 WHERE *referrers count more than 1000*), but I don't understand what should I use in *..*. To see this situation more clearly, I need to get the current user id from the first query and add it to the subquery into the %LIKE% statement, but how? Like:

`SELECT 
id, login 
FROM users u 
WHERE id IN 
  (SELECT id FROM users u1 WHERE count((SELECT * FROM users u2 WHERE refer LIKE %u1.id%) > 1000)` 

But how to do it in right way?

Answer 1

You can use string functions for this:

select id, login
from users
where char_length(referrers) - char_length(replace(referrers, ',', '')) >= 99

This works by counting how many , can be found in the referrers column: 99 commas means that there are 100 values.

Bottom line, you should fix your data model: instead of using delimited lists, you should have a separate table to store the user/referrer tuples, with one tuple per row. Storing delimited lists in a table column is typical antipattern in relational databases that should generally be avoided: you can read more about this in this famous SO post.