MySQL, count и group by

Случается так, что нужно подсчитать количество записей в возвращаемом результате с использованием group by. И вот тут начинается магия. К примеру фреймворк CakePHP (я использую v 1.2) имеет хороший хелпер (helper) для разбиения на страницы (pagination). Он действительно хорошо работает на многих обычных запросах. Если копнуть глубже, то всё, что он делает, это подставляет в запрос LIMIT X предварительно посчитав количество записей в результате. Записи считаются тоже простым добавлением count(*) впереди исходя из чего и рисуются потом ссылки на страницы. Собственно вот она магия с этого момента.

Если запрос имеет в себе group by, то добавленный впереди count(*) не даст желаемого результата. Он не вернёт одно число, как ожидается, но добавится колонка с подсчитанным количеством сгруппированных записей. Мягко говоря не совсем то, что надо.

mysql> select count(*) from tracks where length > 1000;

+----------+

| count(*) |

+----------+

| 50 |

+----------+

1 row in set (0,60 sec)

замечательно, а теперь представим, что запрос был такой:

mysql> select count(*) from tracks where length > 1000 group by user_id;

+----------+

| count(*) |

+----------+

| 5 |

| 3 |

| 2 |

| 3 |

| 3 |

| 6 |

| 3 |

| 2 |

| 19 |

| 2 |

| 2 |

+----------+

11 rows in set (1,67 sec)

Не подходит. Нам нужен результат 11, но CakePHP получит 5, pagination сойдёт с ума и у насбудет только одна страница в пейджере. Если рассмаривать этот пример с параметром вывода по 5 на странице, то на остальные страницы просто не будет ссылок.

Первое, что нужно теперь сделать, это переопределить в модели paginateCount и далее возможны всяческие ухищрения.

Одно из самых простых в плане реализации, это сделать find('all') в этом переопределённом методе и после посчитать количество элементов в массиве посредством count() уже в PHP и возвратить полученное значение. Этот способ можно не проверять. Работает 100%. Но понятно, что тут тратится лишняя память и время. Оно и понятно. Не даром разбивают результаты на страницы, они могут быть очень ёмкими. Оставим этот метод для пионеров и тех, кому лень читать дальше.

Следующее решение - это упростить запрос так, чтобы не использовать group by. Т.е. выбрать из таблицы, в которой поле, по которому проводится группировка является уникальным. К примеру для моего запроса можно сделать так:

mysql> select count(distinct user_id) from users as u join tracks as t on (t.user_id = u.id) where t.length > 1000;

+-------------------------+

| count(distinct user_id) |

+-------------------------+

| 11 |

+-------------------------+

1 row in set (0,66 sec)

И это, наверное, самое ювелирное решение. Результат получен верный. Но для очень сложных запросов может так не получится когда, предположим, нет такой таблицы, где бы хранилось это поле уникально. К тому же требует некоторые человеко/часы. Хотя можно попытаться писать изначальный запрос именно таким образом, но кто же думает наперёд.

Оба указанных варианта могут быть реализованы «по-кейковски» и сохранят абстракцию от БД.

Есть ещё и третий вариант, самый простой:

mysql> select count(*) from (select id from tracks where length > 1000 group by user_id) query;

+----------+

| count(*) |

+----------+

| 11 |

+----------+

1 row in set (1,74 sec)

Но как такое сделать «по-кейковски» я ума не приложу. Так что тут уже ломается абстракция.