Counting record rows with HAVING GROUP_CONCAT

Question

My problem is (maybe) a bit complex, I try to write it down comprehensible. I have a Class in PHP which generates a MySQL query depending on several circumstances and parameters (then displays the query result in a dinamyc table). For example here are the tables and an example query:

table relations

It is simple, There are users, and each user can have one or more profiles assigned. 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 for example:

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 is:

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

When I want to use filters on a column that is originated from a subquery or a complex expression (like the GROUP_CONCAT(profile_name SEPARATOR ', ')) I get 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%'

This will result in: 'all_rows' => 2. This is because the GROUP_CONCAT in the HAVING won't group, but If I put it in the SELECT like: SELECT COUNT(GROUP_CONCAT(profile_name SEPARATOR ', ')) I get an error

So the question is how could I get the number of all rows using a GROUP_CONCAT in a filter (WHERE / HAVING) expression?

EDIT: Before I run the main query I need to know how many record rows would it result, without the LIMIT restriction. I use this to calculate the start row value for the LIMIT for pagination purposes.

Personally, I tend to avoid GROUP_CONCAT (and CONCAT) altogether. I prefer to handle those aspects of a given problem at the application level. — Strawberry, Mar 12 '14 at 11:00
possible duplicate of [GROUP\_CONCAT() row count when grouping by a text field](http://stackoverflow.com/questions/12862968/group-concat-row-count-when-grouping-by-a-text-field) — jmail, Mar 12 '14 at 11:02
You don't have a `GROUP BY` clause in your queries. So you're aggregating all the rows that match the `WHERE` condition into a single result. — Barmar, Mar 12 '14 at 11:02
Doesn't the `SQL_CALC_FOUND_ROWS` option solve the problem you're having? — Barmar, Mar 12 '14 at 11:08

score 0 · Answer 1 · answered Mar 12 '14 at 11:02

can you try the outcome of this one? You're not telling the query engine what to group for and it seems it's guessing wrong.

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%'

the answer is still the same my friend..its 2 – G one Mar 12 '14 at 11:08 — G one, Mar 12 '14 at 11:08

score 0 · Answer 2 · answered Mar 12 '14 at 11:07

You have an unusual query where the count would return either 0 or 1. You can definitely fix this problem by using a subquery:

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%';

You can also fix the problem by moving the logic to the where clause:

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%';

These are good ideas and they should work, but in my case the query is generated dinamycally and cannot distinct the original columns from columns that are results of a subquery or a complex expression. My class handles all columns in the SELECT part together, so I cannot put the GROUP_CONCAT(profile_name SEPARATOR ', ') part away form the others. — ACs, Mar 12 '14 at 11:52

score 0 · Answer 3 · answered Mar 12 '14 at 12:17

I fond a solution: This is how record counting query looks:

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

And this is the main query:

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

I have to use HAVING instead of WHERE everywhere, because as I said the main query is generated programically. This is how I set the columns in the SELECT part:

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

And when a user types the %root% expression in the filter input referring to the GROUP_CONCAT(profile_name SEPARATOR ', ') AS 'Profile(s)' column I cannot decide if GROUP_CONCAT(profile_name SEPARATOR ', ') LIKE %root% goes into the WHERE part or the HAVING part because it is handled together with any other columns (like the userid AS 'User id' or the login AS 'User name' columns)

The remaining technically question is that isn't it a mistake to use HAVING everywhere, even when a simple WHERE would be enough?

Counting record rows with HAVING GROUP_CONCAT

3 Answers3