Верно ли составлен запрос?

Question

[Александр Шаповал]

Доброй ночи, у меня вопрос из разряда "есть ли вариант лучше".
Что мы имеем, имеем проекты и задания к ним, при создании задания указывается время когда оно должно быть выполнено (я их называю просроченными если время истекло а оно не выполненное).
Мне нужно получить список проектов с полями:
- название проекта
- количество заданий в нем
- количество еще не выполненных заданий
- количество уже просроченных заданий
Далее отсортировать так, что бы вверху были просроченные, далее по количеству еще незавершенных заданий, далее по общему их количеству.
Структура таблиц:

CREATE TABLE `tasks` (
  `id` int(11) NOT NULL,
  `message` varchar(1024) NOT NULL,
  `time_create` int(11) NOT NULL,
  `id_project` int(11) NOT NULL,
  `status` enum('1','2') NOT NULL DEFAULT '1' COMMENT 'не выполнено/выполнено',
  `deadlines` bigint(20) NOT NULL COMMENT 'сроки выполнения',
  `importance` int(11) NOT NULL DEFAULT '0' COMMENT 'важность выполнения',
  `id_user` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Список заданий';
CREATE TABLE `projects` (
  `id` int(11) NOT NULL,
  `title` varchar(32) NOT NULL,
  `time_create` int(11) NOT NULL,
  `color` varchar(16) NOT NULL,
  `id_user` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Проекты';

Моё решение:

$sql = "SELECT `tasks`.`id_project`,`projects`.`title`, COUNT(*) AS 'task_count',
        (SELECT COUNT(*) FROM `tasks` WHERE `deadlines` < '" . TIME . "' AND `id_project` = `projects`.`id` AND `status` = '1') AS 'task_lose',
        (SELECT COUNT(*) FROM `tasks` WHERE `status` = '1' AND `id_project` = `projects`.`id`) AS 'task_active'
         FROM `tasks`
         INNER JOIN `projects` ON `projects`.`id` = `tasks`.`id_project`
         GROUP BY `tasks`.`id_project`
         ORDER BY `task_lose` DESC, `task_active` DESC, `task_count` DESC";
        $q = DB::me()->query($sql);
        $res = $q->fetchAll();
        foreach($res AS $v) {
            echo $v['title'] . '<br />';
            echo 'Не выполненных заданий: ' . $v['task_active'] . '<br />';
            echo 'Всего заданий: ' . $v['task_count'] . '<br />';
            echo 'Просроченных заданий: ' . $v['task_lose'] . '<br /><br />';
        }

Есть способы выполнить поставленную задачу лучше?

Answer 1

[Rsa97]

Вложенные в SELECT запросы - не самая хорошая идея, они будут вызываться на каждой строке основной таблицы.

SELECT `t`.`id_project`, `p`.`title`, `t`.`task_count`, 
       `t`.`task_lose`, `t`.`task_active`
  FROM (
    SELECT `id_project`, COUNT(*) AS `task_count`, 
           SUM(`status` = '1' AND `deadlines` < :TIME) AS `task_lose`,
           SUM(`status` = '1') AS `task_active`
      FROM `tasks`
      GROUP BY `id_project`
  ) AS `t`
  RIGHT JOIN `projects` AS `p` ON `p`.`id` = `t`.`id_project`
  ORDER BY `t`.`task_lose` DESC, `t`.`task_active` DESC, 
           `t`.`task_count` DESC

Comments

[Александр Шаповал]

Спасибо, но у меня выводит пустой результат, не находит совпадений, на всякий случай убирал AND `deadlines` < :TIME не помогло.
Может с кавычками что то напутали, а то тут на ошибку жаловалось
SELECT `t`.`id_project`, `p`.`title`, `t`.'task_count' заменил на `t`.`task_count`
И дальше после AS иногда используются эти '' а иногда эти ``, посидел поигрался с кавычками, не хочет проекты выводить.

[Rsa97]

Александр Шаповал: Попробуйте отдельно выполнить подзапрос из FROM. Прямые одиночные кавычки '' обозначают строку, обратные `` - имя таблицы, поля, псевдоним и т.д., поправил.

[Александр Шаповал]

Rsa97: закинул в phpmyadmin запрос

SELECT `id_project`,  COUNT(*) AS `task_count`,  SUM(`status` = '1') AS `task_lose`,  SUM(`status` = '1') AS `task_active` 
from `tasks` group by id_project

отработал, дал результат
Поле deadlines не учитывал так как вручную все делал

[Rsa97]

Александр Шаповал: Значит и полный запрос должен сработать.

[Александр Шаповал]

Rsa97: угу сработал. интуитивно чуял что подобное можно сделать но не знал как)
буду сидеть разбираться, спасибо

[Александр Шаповал]

Rsa97: кстати хорошо что полез в phpmyadmin проверять подзапрос, так как вставил запрос в свой скрипт а он все равно не работает, я оказывается забыл вызвать $q->execute() =)

[Александр Шаповал]

Rsa97: я когда писал запрос ошибся, надо все таки не JOIN а RIGHT JOIN, так как если в проектах не будет заданий они не выведутся при JOIN'e, ну и добавить сортировку по ID что бы сортировать их когда заданий нет. Поправьте вдруг кому пригодится

SELECT `t`.`id_project`, `p`.`title`, `p`.`id`, `p`.`color`, `t`.`task_count`,
       `t`.`task_lose`, `t`.`task_active`
  FROM (
    SELECT `id_project`, COUNT(*) AS `task_count`,
           SUM(`status` = '1' AND `deadlines` < :TIME) AS `task_lose`,
           SUM(`status` = '1') AS `task_active`
      FROM `tasks`
      GROUP BY `id_project`
  ) AS `t`
  RIGHT JOIN `projects` AS `p` ON `p`.`id` = `t`.`id_project`
  ORDER BY `t`.`task_lose` DESC, `t`.`task_active` DESC,
           `t`.`task_count` DESC, `p`.`id` DESC