Cначала пройдусь по тем решениям, которые были предложены в комментариях.
Тут ничего сложного не было, тем неменее, верных решений оказалось немного. Самая распространенная ошибка — мало кто догадался учитывать вариант, когда категория могла бы быть не опубликована. Попадались с неправильной группировкой или просто грубыми синтаксическими ошибками в коде. Были решения, после просмотре EXPLAIN которых возникали суицидальные мысли. :-)
Но после исправления ошибок, у многих работало правильно, если не считать некоторых «мелких» грехов.
На самом деле она получилась легче, чем я думал — но это больше моя вина, поскольку условие не было достаточно точным, а уточнить никто не догадался. В итоге, это превратилось в подвох, на который никто не обратил внимание. Дело вот в чем — поле ordi отвечает за сортировку, но не было уточнено что оно уникально (правда обратного тоже не было, и это моя вина да). По этому полю действительно идет сортировка, но для каждой принадлежности свои значения, то есть если в одном фотоальбоме идет 1,2,3 то в другом не 4,5,6 а тоже 1,2,3. Из-за этого половина решений одновременно правельны, и одновременно — нет.
Из ошибок — почти нигде нет проверок на флаги вхождений выше. Если не считать этого, то почти у всех работало.
Если же учитывать приведенные мной условия, то это задача достаточно сложная, а именно — запрос получается очень громоздкий. Далее стант ясно почему.
Решений как и следовало ожидать было мало. Сработало относительно как надо только одно — предложеное pharod. Потому что задача действительно очень сложная, и тут уже дело не в громоздкости запросов как во второй, а непосредственно в самом решении.
Для начала построим шаблонный запрос, который будет корректно учитывать все приведенные условия и выбирать вообще все. Я так всегда делаю, если одни и те же связанные в таблицах данные надо показывать, но в зависимости от некоторых внешних условий. Сейчас это не важно, я просто хочу пояснить как я решаю подобное по шагам.
Свяжем все три таблицы в едином запросе выставляя условия присоединения с помощью JOIN… ON (...). Получится примерно следующее:
Условия присоединения я пишу в ON (...) что бы сразу было понятно что к чему относится и не загромождать WHERE (на самом деле один черт где они написаны). Группировка нужна чтоб фильтровать пустые галереи и категории (для них условие HAVING COUNT(i.id) будет NULL). Сортировка по полям присутствует.
В этом запросе выполнены все исходные условия, хотя это вообще никакое не решение. Мы сделали заготовку. А вот теперь поехали.
Берем исходный запрос, который уже все учитывает. Добавляем условие выборки по ID категории и еще один JOIN таблицы с учетом флага is_main_foto. Но поскольку данного может и не быть то сделаем его LEFT JOIN:
Теперь если значение im2.id IS NULL то в i.id будет лежать совершенно любая произвольная фота. Кстати, нормальная СУБД в этом случае должна ругаться потому как не будет ясно, какое значение брать в i. Теперь для полного счастья напишем ответ, используя IF:
Для тех кто не знает как работает IF поясню — IF (<условие>, <значение выражения если истина>, <значение выражения если ложь>). Тренарный оператор практически.
Все, задача решена.
Решение не очень сложное: надо получить ORDI текущего элемента, далее сделать MAX для удовлетворяющих условием выборки при условии меньше полученного ordi и тем самым получить предидущую фотку, затем аналогично — следующую. Если не вдаваться в подробности, то все достаточно просто, но если вдаваться получаются достаточно сложные запросы.
Сначала получим ordi (поле сортировки) текущего элемента — это очень просто:
Далее, будем учитывать что искать надо при условии вхождения только в текущую галерею, то есть надо определить id галереи (по условию он может быть получен из вне, но для полноты картины вычислим его сами):
Теперь построим запрос на получение предидущего ORDI (мы ищем только опубликованные):
Аналогично для следующего:
Теперь мы знаем ORDI следующего и предидущего, нужно получить ID соответствующих элементов, опять же при условии что мы находимся только в текущей галерее:
Вроде бы все, только вот проверок условий у нас все еще нет. А так же не ясно, как отделить следующее от предидущего если у нас в выборке будет один результат. Для решения этого добавим в выборку нашу фотографию (для которой вычисляем) и еще воспользуемся нашим «шаблонным» запросом, и в итоге получим:
Условие
уберет из выборки все, если текущая фота, галерея или категория не опубликованы. В выражении HAVING и группировки тут никакого смысла нет, так как если в галерее нет фоток то выбока все равно будет пустой. Результат будет три, два или один ряд. Соответственно искомой фотографией будет та, которая имеет заданный ID а предидущая-следующая определяется исходя из ordi — меньше оно или больше того который с данным ID.
Вроде бы тут очень много подзапросов и работать это будет медленно. На самом деле нет — MySQL кеширует повторяющиеся заросы (можно для пущей уверенности сделать SQL_CACHE), поэтому каждый из подзапросов будет выполнен всего 1 раз. При условии существования индексов (ну куда же без них-то) это будет работать достаточно быстро.
Задача решена.
Примечание: я говорил, что не знаю как решить данную задачу одним запросом. Это так, поскольку в реальности мне еще нужно было получать большую кучу параметров для каждой фотки, и если это делать одни запросом он становится просто огромным и реально тормозит. Здесь — это теже 2 запроса (на следующую и предидущую) правда с общим фильтром.
Засада в том, что фактически нужно сделать LIMIT для определенной группы. Этого не сделаешь обычным лимитом, поскольку он лимитирует результат всего запроса и только его. Объединять UNION-ом запросы для каждой категории с использованием LIMIT… — ну решение конечно, но если категорий очень много? Не катит. Следующее что приходит в голову — это фильтровать с помощью WHERE. Но тогда нужен какой-то признак по чему будет фильтрация, то есть нужно в условии WHERE отличать ряды которые по счету больше нашего N. А как его получить? Только задав свой порядковый номер ряду альбомов для каждой из категорий. Как его сделать? Только при помощи локальных переменных. Я чуть меннее чем полностью уверен, что данная задача и ей подобные не решаются как-либо иначе.
Если мы возьмем исходный запрос и чуть его модифицируем, вот так:
То получим пронумерованные ряды. Для каждого ряда значение @a будет увеличиваться на единицу. Отлично. Но нам надо посчитать все определенной группы, значит при условии что c_id меняется, нужно сбрасывать @a. Введем еще одну переменную для этого, и условие сброса (заодно вместо звездочки добавим нужные нам поля):
Что это значит. Если значение переменной @cid равно текущему значению id категории, то переменная @а увеличивается на 1, после чего результат равен @a. В противном случае значение @cid становится равным текущему id категории, @a становится равно 1 и результат выражения становится = 1 (то есть тому же самому @a, хотя на самом деле это просто удачное совпадение). Теперь казалось бы можно просто отфильтровать условием WHERE @a < N но… это не будет работать.
И вот почему. Данная схема будет выполняться правильно только при условии что ряды уже отсортированы. Но сортировка выполняется уже после того, как проработает запрос, а не во время. Получается, что нужно сначала отсортировать, и только потом уже нумировать.
Теперь еще обращаю внимание на то, что как я уже сказал — по счасливому стечению обстоятельств выражение IF (@cid=cid, @a:=@a+1, (@cid:=cid) AND (@a:=1)) будет равно @a и в том, и в другом случае. Тогда это можно использовать, написав его прямо в WHERE. Напомню, что по условию надо было еще показать картинку, но это уже не вызывает никаких проблем, поскольку мы сначала выполним запрос и потом уже только будем производить счетные оперции с фильтрами. Тогда вот что у нас получится:
Вот это уже будет работать проавильно. Задача решена. Кстати, от себя отмечу — что локальные переменные — это очень сильная штука при умелом их использовании. И еще — данный запрос можно назвать универсальным решением для многих типовых задач подобного рода: 5 последних добавленых или лучших или покупаемых товаров на категорию, 5 лучших статей для каждого пользователя, 10 самых раскупаемых книг каждого жанра и так далее. Потому что абсолютно все равно, какой будет вложенный запрос.
Ну вот вобщем-то и все. Спасибо за внимание. Надеюсь, вам было интерестно.
1 задача.
Тут ничего сложного не было, тем неменее, верных решений оказалось немного. Самая распространенная ошибка — мало кто догадался учитывать вариант, когда категория могла бы быть не опубликована. Попадались с неправильной группировкой или просто грубыми синтаксическими ошибками в коде. Были решения, после просмотре EXPLAIN которых возникали суицидальные мысли. :-)
Но после исправления ошибок, у многих работало правильно, если не считать некоторых «мелких» грехов.
2 задача.
На самом деле она получилась легче, чем я думал — но это больше моя вина, поскольку условие не было достаточно точным, а уточнить никто не догадался. В итоге, это превратилось в подвох, на который никто не обратил внимание. Дело вот в чем — поле ordi отвечает за сортировку, но не было уточнено что оно уникально (правда обратного тоже не было, и это моя вина да). По этому полю действительно идет сортировка, но для каждой принадлежности свои значения, то есть если в одном фотоальбоме идет 1,2,3 то в другом не 4,5,6 а тоже 1,2,3. Из-за этого половина решений одновременно правельны, и одновременно — нет.
Из ошибок — почти нигде нет проверок на флаги вхождений выше. Если не считать этого, то почти у всех работало.
Если же учитывать приведенные мной условия, то это задача достаточно сложная, а именно — запрос получается очень громоздкий. Далее стант ясно почему.
3 задача.
Решений как и следовало ожидать было мало. Сработало относительно как надо только одно — предложеное pharod. Потому что задача действительно очень сложная, и тут уже дело не в громоздкости запросов как во второй, а непосредственно в самом решении.
Теперь решения.
Для начала построим шаблонный запрос, который будет корректно учитывать все приведенные условия и выбирать вообще все. Я так всегда делаю, если одни и те же связанные в таблицах данные надо показывать, но в зависимости от некоторых внешних условий. Сейчас это не важно, я просто хочу пояснить как я решаю подобное по шагам.
Свяжем все три таблицы в едином запросе выставляя условия присоединения с помощью JOIN… ON (...). Получится примерно следующее:
SELECT
*
FROM photo_category as c
JOIN photo_gallery as g ON (g.c_id=c.id AND c.is_published AND g.is_published)
JOIN photo_image as i ON (i.g_id = g.id AND i.is_published)
GROUP BY g.id
HAVING COUNT(i.id) > 0
ORDER BY c.ordi, g.ordi, i.ordi;
Условия присоединения я пишу в ON (...) что бы сразу было понятно что к чему относится и не загромождать WHERE (на самом деле один черт где они написаны). Группировка нужна чтоб фильтровать пустые галереи и категории (для них условие HAVING COUNT(i.id) будет NULL). Сортировка по полям присутствует.
В этом запросе выполнены все исходные условия, хотя это вообще никакое не решение. Мы сделали заготовку. А вот теперь поехали.
Задача первая.
Берем исходный запрос, который уже все учитывает. Добавляем условие выборки по ID категории и еще один JOIN таблицы с учетом флага is_main_foto. Но поскольку данного может и не быть то сделаем его LEFT JOIN:
SELECT
*
FROM photo_category as c
JOIN photo_gallery as g ON (g.c_id=c.id AND c.is_published AND g.is_published)
JOIN photo_image as i ON (i.g_id = g.id AND i.is_published)
LEFT JOIN photo_image as i2 ON (i2.g_id = g.id AND i2.is_main_foto)
WHERE c.id = 1
GROUP BY g.id
HAVING COUNT(i.id) > 0
ORDER BY c.ordi, g.ordi, i.ordi;
Теперь если значение im2.id IS NULL то в i.id будет лежать совершенно любая произвольная фота. Кстати, нормальная СУБД в этом случае должна ругаться потому как не будет ясно, какое значение брать в i. Теперь для полного счастья напишем ответ, используя IF:
SELECT
c.id as cid,
c.title as ctitle,
g.id as gid,
g.title as gtitle,
IF (i2.id IS NULL, i.id, i2.id) as image_id
FROM photo_category as c
JOIN photo_gallery as g ON (g.c_id=c.id AND c.is_published AND g.is_published)
JOIN photo_image as i ON (i.g_id = g.id AND i.is_published)
LEFT JOIN photo_image as i2 ON (i2.g_id = g.id AND i2.is_main_foto)
WHERE c.id = 1
GROUP BY g.id
HAVING COUNT(i.id) > 0
ORDER BY c.ordi, g.ordi, i.ordi;
Для тех кто не знает как работает IF поясню — IF (<условие>, <значение выражения если истина>, <значение выражения если ложь>). Тренарный оператор практически.
Все, задача решена.
Задача вторая.
Решение не очень сложное: надо получить ORDI текущего элемента, далее сделать MAX для удовлетворяющих условием выборки при условии меньше полученного ordi и тем самым получить предидущую фотку, затем аналогично — следующую. Если не вдаваться в подробности, то все достаточно просто, но если вдаваться получаются достаточно сложные запросы.
Сначала получим ordi (поле сортировки) текущего элемента — это очень просто:
SELECT ordi FROM photo_image WHERE id = 1
Далее, будем учитывать что искать надо при условии вхождения только в текущую галерею, то есть надо определить id галереи (по условию он может быть получен из вне, но для полноты картины вычислим его сами):
SELECT g_id FROM photo_image WHERE id = 1
Теперь построим запрос на получение предидущего ORDI (мы ищем только опубликованные):
SELECT MAX(ordi) from photo_image
WHERE is_published AND g_id = (SELECT g_id FROM photo_image WHERE id = 1)
AND ordi < (SELECT ordi FROM photo_image WHERE id = 1)
Аналогично для следующего:
SELECT MIN(ordi) from photo_image
WHERE is_published AND g_id = (SELECT g_id FROM photo_image WHERE id = 1)
AND ordi > (SELECT ordi FROM photo_image WHERE id = 1)
Теперь мы знаем ORDI следующего и предидущего, нужно получить ID соответствующих элементов, опять же при условии что мы находимся только в текущей галерее:
SELECT id, title
FROM photo_image
WHERE
g_id = (SELECT g_id FROM photo_image WHERE id = 1)
AND
(
ordi =
(
SELECT MAX(ordi) from photo_image
WHERE is_published AND g_id = (SELECT g_id FROM photo_image WHERE id = 1)
AND ordi < (SELECT ordi FROM photo_image WHERE id = 1)
)
OR
ordi =
(
SELECT MIN(ordi) from photo_image
WHERE is_published AND g_id = (SELECT g_id FROM photo_image WHERE id = 1)
AND ordi > (SELECT ordi FROM photo_image WHERE id = 1)
)
);
Вроде бы все, только вот проверок условий у нас все еще нет. А так же не ясно, как отделить следующее от предидущего если у нас в выборке будет один результат. Для решения этого добавим в выборку нашу фотографию (для которой вычисляем) и еще воспользуемся нашим «шаблонным» запросом, и в итоге получим:
SELECT ordi, id, title
FROM photo_image
WHERE
EXISTS
(
SELECT
i.id
FROM photo_category as c
JOIN photo_gallery as g ON (g.c_id=c.id AND c.is_published AND g.is_published)
JOIN photo_image as i ON (i.g_id = g.id AND i.is_published)
WHERE i.id = 1
)
AND
g_id = (SELECT g_id FROM photo_image WHERE id = 1)
AND
(
ordi =
(
SELECT MAX(ordi) from photo_image
WHERE is_published AND g_id = (SELECT g_id FROM photo_image WHERE id = 1)
AND ordi < (SELECT ordi FROM photo_image WHERE id = 1)
)
OR
ordi =
(
SELECT MIN(ordi) from photo_image
WHERE is_published AND g_id = (SELECT g_id FROM photo_image WHERE id = 1)
AND ordi > (SELECT ordi FROM photo_image WHERE id = 1)
)
OR
id = 1
);
Условие
EXISTS
(
SELECT
i.id
FROM photo_category as c
JOIN photo_gallery as g ON (g.c_id=c.id AND c.is_published AND g.is_published)
JOIN photo_image as i ON (i.g_id = g.id AND i.is_published)
WHERE i.id = 1
)
уберет из выборки все, если текущая фота, галерея или категория не опубликованы. В выражении HAVING и группировки тут никакого смысла нет, так как если в галерее нет фоток то выбока все равно будет пустой. Результат будет три, два или один ряд. Соответственно искомой фотографией будет та, которая имеет заданный ID а предидущая-следующая определяется исходя из ordi — меньше оно или больше того который с данным ID.
Вроде бы тут очень много подзапросов и работать это будет медленно. На самом деле нет — MySQL кеширует повторяющиеся заросы (можно для пущей уверенности сделать SQL_CACHE), поэтому каждый из подзапросов будет выполнен всего 1 раз. При условии существования индексов (ну куда же без них-то) это будет работать достаточно быстро.
Задача решена.
Примечание: я говорил, что не знаю как решить данную задачу одним запросом. Это так, поскольку в реальности мне еще нужно было получать большую кучу параметров для каждой фотки, и если это делать одни запросом он становится просто огромным и реально тормозит. Здесь — это теже 2 запроса (на следующую и предидущую) правда с общим фильтром.
Задача третья.
Засада в том, что фактически нужно сделать LIMIT для определенной группы. Этого не сделаешь обычным лимитом, поскольку он лимитирует результат всего запроса и только его. Объединять UNION-ом запросы для каждой категории с использованием LIMIT… — ну решение конечно, но если категорий очень много? Не катит. Следующее что приходит в голову — это фильтровать с помощью WHERE. Но тогда нужен какой-то признак по чему будет фильтрация, то есть нужно в условии WHERE отличать ряды которые по счету больше нашего N. А как его получить? Только задав свой порядковый номер ряду альбомов для каждой из категорий. Как его сделать? Только при помощи локальных переменных. Я чуть меннее чем полностью уверен, что данная задача и ей подобные не решаются как-либо иначе.
Если мы возьмем исходный запрос и чуть его модифицируем, вот так:
SELECT
@a:=@a+1, *
FROM photo_category as c
JOIN photo_gallery as g ON (g.c_id=c.id AND c.is_published AND g.is_published)
JOIN photo_image as i ON (i.g_id = g.id AND i.is_published)
GROUP BY g.id
HAVING COUNT(i.id) > 0
ORDER BY c.ordi, g.ordi, i.ordi;
То получим пронумерованные ряды. Для каждого ряда значение @a будет увеличиваться на единицу. Отлично. Но нам надо посчитать все определенной группы, значит при условии что c_id меняется, нужно сбрасывать @a. Введем еще одну переменную для этого, и условие сброса (заодно вместо звездочки добавим нужные нам поля):
SELECT
@a:=@a+1,
IF (@cid=cid, @a:=@a+1, (@cid:=cid) AND (@a:=1)),
c.id as cid, c.title as ctitle,
g.id as gid, g.title as gtitle,
FROM photo_category as c
JOIN photo_gallery as g ON (g.c_id=c.id AND c.is_published AND g.is_published)
JOIN photo_image as i ON (i.g_id = g.id AND i.is_published)
GROUP BY g.id
HAVING COUNT(i.id) > 0
ORDER BY c.ordi, g.ordi, i.ordi;
Что это значит. Если значение переменной @cid равно текущему значению id категории, то переменная @а увеличивается на 1, после чего результат равен @a. В противном случае значение @cid становится равным текущему id категории, @a становится равно 1 и результат выражения становится = 1 (то есть тому же самому @a, хотя на самом деле это просто удачное совпадение). Теперь казалось бы можно просто отфильтровать условием WHERE @a < N но… это не будет работать.
И вот почему. Данная схема будет выполняться правильно только при условии что ряды уже отсортированы. Но сортировка выполняется уже после того, как проработает запрос, а не во время. Получается, что нужно сначала отсортировать, и только потом уже нумировать.
Теперь еще обращаю внимание на то, что как я уже сказал — по счасливому стечению обстоятельств выражение IF (@cid=cid, @a:=@a+1, (@cid:=cid) AND (@a:=1)) будет равно @a и в том, и в другом случае. Тогда это можно использовать, написав его прямо в WHERE. Напомню, что по условию надо было еще показать картинку, но это уже не вызывает никаких проблем, поскольку мы сначала выполним запрос и потом уже только будем производить счетные оперции с фильтрами. Тогда вот что у нас получится:
SELECT q.* FROM
(
SELECT -- запрос из первой задачи
c.id as cid, c.title as ctitle,
g.id as gid, g.title as gtitle,
IF (i2.title IS NULL, i.title, i2.title)
FROM photo_category as c
LEFT JOIN photo_gallery as g ON (g.c_id=c.id AND c.is_published AND g.is_published)
LEFT JOIN photo_image as i ON (i.g_id = g.id AND i.is_published)
LEFT JOIN photo_image as i2 ON (i2.g_id = g.id AND i2.is_main_foto)
GROUP BY g.id
HAVING COUNT(i.id) > 0
ORDER BY c.ordi, g.ordi DESC
) as q
WHERE IF (@cid=q.cid, @a:=@a+1, (@cid:=q.cid) AND (@a:=1)) <= N;
Вот это уже будет работать проавильно. Задача решена. Кстати, от себя отмечу — что локальные переменные — это очень сильная штука при умелом их использовании. И еще — данный запрос можно назвать универсальным решением для многих типовых задач подобного рода: 5 последних добавленых или лучших или покупаемых товаров на категорию, 5 лучших статей для каждого пользователя, 10 самых раскупаемых книг каждого жанра и так далее. Потому что абсолютно все равно, какой будет вложенный запрос.
Ну вот вобщем-то и все. Спасибо за внимание. Надеюсь, вам было интерестно.
No comments:
Post a Comment