Counting rows of records using HAVING GROUP_CONCAT

Question

My problem is (maybe) a little complicated, I'm trying to make it understandable. I have a class in PHP that generates a MySQL query depending on several circumstances and parameters (then it displays the result of the query in a dinamyc table). For example, here are tables and an example query:

It's simple, there are users, and each user can have one or more profiles. For exmaple, we have only one user, and he has two profiles:

[USER]
userid    login     password     status    reg_date
------    -------   ----------   ------    -------------------
1         JohnDoe   hd98349...   0         2014-01-09 16:00:55

[PROFILE]
profile_id  profile_name    
----------  ------------
1           admin
2           root
3           user

[USER_PROFILE]
relation_id   user   profile
-----------   -----  -------
1             1      1
2             1      2

My PHP code generates a query that looks like this:

SELECT 
    userid AS 'User id', 
    login AS 'User name', 
    GROUP_CONCAT(profile_name SEPARATOR ', ') AS 'Profile(s)', 
    `status`  
FROM `user` LEFT JOIN user_profile ON `user`.userid = user_profile.`user`
         LEFT JOIN `profile` ON user_profile.`profile` = `profile`.profile_id  
WHERE 
    status IN (0, 1) 
ORDER BY reg_date ASC 
LIMIT 0 ,10

And the result for this query:

[RESULT]
User id    User name    Profile(s)   status
-------    ---------    ----------   ------
1          JohnDoe      root, admin  0

When I want to use filters in a column that is created from a subquery or a complex expression (e.g. GROUP_CONCAT(profile_name SEPARATOR ', ')), I get a false result:

SELECT COUNT(*) AS 'all_rows'  
FROM `user` LEFT JOIN user_profile ON `user`.userid = user_profile.`user`
      LEFT JOIN `profile` ON user_profile.`profile` = `profile`.profile_id  WHERE          status IN (0, 1) 
HAVING GROUP_CONCAT(profile_name SEPARATOR ', ') LIKE '%root%'

: 'all_rows' = > 2. , GROUP_CONCAT HAVING , SELECT, : SELECT COUNT (GROUP_CONCAT (profile_name SEPARATOR ',')),

, , , GROUP_CONCAT (WHERE/HAVING)?

EDIT: , , , LIMIT. LIMIT .

Answer 1

? , , , .

SELECT `user`.userid,
    GROUP_CONCAT(profile_name SEPARATOR ', ') as roles,
    COUNT(*) AS 'all_rows'  
FROM `user` 
    LEFT JOIN user_profile ON `user`.userid = user_profile.`user`
    LEFT JOIN `profile` ON user_profile.`profile` = `profile`.profile_id  
WHERE          status IN (0, 1) 
GROUP BY `user`.userid
HAVING GROUP_CONCAT(profile_name SEPARATOR ', ') LIKE '%root%'

Answer 2

, 0, 1. , :

SELECT COUNT(*) AS 'all_rows'  
FROM (SELECT GROUP_CONCAT(profile_name SEPARATOR ', ') as pns
      FROM `user` LEFT JOIN
           user_profile
           ON `user`.userid = user_profile.`user` LEFT JOIN
           `profile`
           ON user_profile.`profile` = `profile`.profile_id
      WHERE status IN (0, 1) 
     ) t
WHERE pns LIKE '%root%';

, where:

SELECT COUNT(*)
FROM `user` LEFT JOIN
     user_profile
     ON `user`.userid = user_profile.`user` LEFT JOIN
     `profile`
     ON user_profile.`profile` = `profile`.profile_id
WHERE status IN (0, 1) and profile_name like '%root%';

Answer 3

: :

SELECT COUNT(*) AS 'all_rows' 
FROM (SELECT 
         userid AS 'User id', 
         login AS 'User name',    
         GROUP_CONCAT(profile_name SEPARATOR ', ') AS 'Profile(s)', 
         status  
      FROM `user` LEFT JOIN user_profile ON `user`.userid = user_profile.`user` 
            LEFT JOIN `profile` ON user_profile.`profile` = `profile`.profile_id  
      HAVING 
          status IN (0,    1) 
          AND  GROUP_CONCAT(profile_name SEPARATOR ', ') LIKE '%root%') t

:

SELECT 
    userid AS 'User id', 
    login AS 'User name', 
    GROUP_CONCAT(profile_name SEPARATOR ', ') AS 'Profil(ok)', 
    status  
FROM `user` LEFT JOIN user_profile ON `user`.userid = user_profile.`user`
            LEFT JOIN `profile` ON user_profile.`profile` = `profile`.profile_id  
HAVING 
    status IN (0, 1) 
    AND  GROUP_CONCAT(profile_name SEPARATOR ', ') LIKE '%root%'  
ORDER BY reg_date ASC 
LIMIT 0 ,10

HAVING WHERE , , , . SELECT:

$listObj->setColumns(array('userid' => 'User id',
               'login'  => 'User name',
               'GROUP_CONCAT(profile_name SEPARATOR \', \')' => 'Profile(s)'));

% root% , GROUP_CONCAT(profile_name SEPARATOR ', ') AS 'Profile(s)', , GROUP_CONCAT(profile_name SEPARATOR ', ') LIKE %root% WHERE HAVING, (, userid AS 'User id' login AS 'User name')

The remaining technical question is what should be used HAVINGeverywhere, even if simple enough WHERE?