Хитрые сортировки в MySQL

Недавно столкнулся с интересной задачей и хочу поделиться с читателями своим решением на простом примере. Предположим у нас есть список дел, фактически это так называемый список TODO. Каждая запись в этом списке может иметь дату к которой она должна быть выполнена (due_date), приоритет (priority) и дату создания (create_date).

Теперь предположим, что вам необходимо получить из базы записи, отсортированные следующим образом – сначала показать записи, дата выполнения которых приближается, затем показать записи без даты окончания, но с учетом приоритета от более высокого к более низкому и наконец показать записи, которые не имеют даты окончания, не имеют приоритета и отсортировать их по дате создания. Первое что приходит в голову, это сочинить вот такой запрос:

SELECT * FROM todo WHERE ... ORDER BY due_date asc, priority desc, create_date asc

Казалось бы вот и все, вопрос закрыт, однако есть загвоздка – первое поле (due_date) не обязательное и вот что мы получаем в результате


Как видно первыми идут записи без даты окончания, а у нас была задача первыми показать именно записи, которые скоро заканчиваются. Т.е изменение способа сортировки с ASC на DESC тоже не спасут. Обычно в таких случаях используется UNION двух SELECT запросов. В первом запросе ставится условие выбрать только записи с датой окончания, во втором – наоборот, только записи без даты окончания. Т.е видимо надо использовать вот такой запрос:

SELECT * FROM todo WHERE due_date IS NOT NULL ... ORDER BY due_date asc
UNION
SELECT * FROM todo WHERE due_date IS NULL ... ORDER BY priority desc, create_date asc

Все согласны с этим запросом? А если еще раз внимательно посмотреть на него? Ладно, не буду тянуть резину в долгий ящик, этот запрос не работает по той простой причине, что ORDER BY при использовании UNION должен быть только один, влияющий на всю выборку. Но в таком случае, скажете вы, UNION никак не решает нашу задачу, ведь результат ничем не будет отличаться от первого запроса. И правильно скажете, в данном случае результат действительно не отличается. НО, использование UNION дает нам возможность все-таки повлиять на результат. И притом сделать это очень просто. Что нас не устраивало в первом запросе? То, что записи без даты шли в самом начале. А почему они шли в самом начале? Потому что при ASC сортировке по полю, содержащему значение NULL, всегда первыми идут записи со значением NULL в этом поле. Значит что нужно сделать, чтобы эти записи оказались в конце? Правильно, сделать так, чтобы в сортировке вместо NULL использовалось некоторое значение, заведомо большее любой существующей в базе даты. Согласно мануалу максимально допустимое значение поля типа DATETIME составляет ’9999-12-31 23:59:59′, его и будем использовать. Получаем следующий запрос:

SELECT priority, description, due_date, due_date AS sort_due_date, create_date FROM todo
WHERE due_date IS NOT NULL ...
UNION
SELECT priority, description, due_date, '9999-12-31 23:59:59' AS sort_due_date, create_date FROM todo
WHERE due_date IS NULL ...
ORDER BY sort_due_date ASC, priority DESC, create_date ASC

и вот так выглядит результат:


В данном запросе оставлена нетронутой оригинальная колонка due_date, а сортировка производится по синтетической колонке ‘sort_due_date’. Нужно это для того, чтобы не исказить в выдаче реальные данные, мы ведь не хотим сами себя запутать и показать пользователю дату конца света? В принципе задача решена. Не знаю как вы, а я люблю изящные решения, данный же вариант изящным назвать трудно. Хотите узнать какое на мой взгляд решение изящное? Тогда читайте дальше.

Последнее время мы работаем с CodeIgniter-ом, до этого работали в основном с ZendFramework-ом. Так вот, и в CI, и в ZF, да и во многих других фрэймворках, извините за каламбур, активно используется паттерн Active Record. Это значит, что первый запрос создается примерно следующим образом:

$this->db_app->select('priority, description, due_date, create_date');
$this->db_app->from('todo');
$this->db_app->where( ... );
$this->db_app->order_by('due_date asc, priority desc, create_date asc');

и при этом я не нашел в CI возможности составлять UNION запросы с использованием этого паттерна. Если кто из читателей знает, буду благодарен. Если условие WHERE в запросе несложное, то в принципе можем сформировать первый запрос, скомпилировать его. Затем создать таким же образом второй запрос и скомпилировать его, а затем соединить их руками при помощи UNION и затем выполнить получившийся запрос. Однако зачастую условия в запросе формируются в зависимости от переданных параметров и логика формирования условий может быть довольно сложной. В итоге получим сильное дублирование кода. Однако безвыходных ситуаций не бывает и на помощь нам приходит очень удобное свойство MySQL – использование условия прямо в ORDER BY конструкции. Работает это следующим образом:

SELECT priority, description, due_date, create_date
FROM todo WHERE ...
ORDER BY IFNULL(due_date, '9999-12-31 23:59:59') ASC, priority DESC, create_date ASC

Вот это я и называю изящным решением! Несогласные со мной пишите в комментариях, я все-равно их удалю :) . Таким образом не нужно дублировать код, поскольку запрос у нас один, нужно лишь правильным образом создать ORDER BY условия. И с этим у CI не очень хорошо дело обстоит. Если вы попробуете (а я попробовал, не сомневайтесь) используя Active Record задать сортировку вот так

$this->db_app->order_by("IFNULL(due_date, '9999-12-31 23:59:59') ASC, priority DESC, create_date ASC");

то вас, как и меня ждет разочарование. Сверхумный фрэймворк разобъет переданную строку и окружит первое слово кавычками, в результате запрос будет выполнен с ошибкой. Кстати, если кто-нибудь из читателей знает, как попросить CI этого не делать, также буду очень благодарен. Значит нам остается только обходной маневр – сортировку нужно будет добавить в запрос не используя Active Record, руками. Да, это немного нарушает красоту кода, но на мой взгляд это меньшее зло. Я это сделал следующим образом:

$this->db_app->select('priority, description, due_date, create_date');
$this->db_app->from('todo');
$this->db_app->where( ... );
 
$sql = $this->db_app->_compile_select();
$sql .= " ORDER BY IFNULL(due_date, '9999-12-31 23:59:59') ASC, priority DESC, create_date ASC";
$result = $this->db_app->query($sql)->result_array();

Внимательный читатель заметит тут одну нестыковку – вызывается метод _compile_select(), определенный в классе CI_DB_active_record как protected. Сознаюсь, хотя мне и не по душе менять код самого фрэймворка, но другого способа сгенерировать запрос без его выполнения я не смог найти и сделал данный метод публичным. Может вы знаете такой способ? Пишите комментарии, не стесняйтесь.

И напоследок небольшое замечание. В указанном примере в колонке due_date значением по умолчанию является NULL, однако бывают ситуации, когда в качестве значения по умолчанию используется например ’0000-00-00′, как быть в этом случае? Для такого случая можно воспользоваться конструкцией CASE, например вот так:

SELECT priority, description, due_date, create_date
FROM todo WHERE ...
ORDER BY
  CASE WHEN due_date ='0000-00-00' THEN '9999-12-31 23:59:59'
    ELSE due_date
  END ASC, priority DESC, create_date ASC
Print this post | Home

One comment

  1. Анастася says:

    Спасибо вам, жизнь просто спасли!