Как отсортировать поля в 1 таблице по значениям во 2?

Question

[Дмитрий Жуков]

Добрый день.
Пытаюсь прикрутить к модулю статей в opencart возможность сортировки по кол-ву комментариев в записи.

Первая таблица этого модуля содержит данные о, собственно, записях,
Вторая таблица содержит комментарии с указанием id записи (из первой табл.), в которой размещается.
Т.е.
1) oc_blog (blog_id, text, title )
2) oc_blog_comment (blog_id, comment)

Нужно отсортировать посты с учетом кол-ва комментов в каждой.
Подскажите правильный sql запрос.

Answer 1

[Сергей]

вариант 1 (с оконными функциями):
select distinct p.blog_id, p.text, count(w.blog_id) over (partition by w.blog_id) from oc_blog p, oc_blog_comment w where w.blog_id = p.blog_id order by 3 desc limit 5;
вариант 2 (с пред. подсчётом):
select p.blog_id, p.text, t.count from oc_blog p, (select blog_id, count(blog_id) from oc_blog_comment group by blog_id ) t where p.blog_id=t.blog_id order by t.count desc limit 5;

Первый вариант на моей выборке раза в 3 медленнее, в нём оконная функция выполняется для каждой строки.
Второй вариант к тому же удобнее в будущем ограничивать, например, статьи не старше месяца, запихнуть во внутренний селект.

ПыСы, лимит исключительно для удобства.

Comments

[Дмитрий Жуков]

Очень признателен! Спасибо за пищу для размышлений )

[Дмитрий Жуков]

Хочу дополнить. Уже существует запрос:

$sql = "SELECT * FROM " . DB_PREFIX . "blog i LEFT JOIN " . DB_PREFIX . "blog_description id ON (i.blog_id = id.blog_id) LEFT JOIN " . DB_PREFIX . "blog_to_store i2s ON (i.blog_id = i2s.blog_id) WHERE id.language_id = '" . (int)$this->config->get('config_language_id') . "' AND i2s.store_id = '" . (int)$this->config->get('config_store_id') . "' AND i.status = '1' AND i.sort_order <> '-1'";

Сортировка по дате, например, осуществляется дополнительным запросом:

$sql .= " ORDER BY i.date_added DESC, i.blog_id DESC LIMIT " . (int)$start . "," . (int)$limit;

[Сергей]

Вопрос не понял, но по вашему первому селекту поправил бы немного.
У вас: "WHERE id.language_id =..."
Лучше бы: "blog_description id ON (i.blog_id = id.blog_id AND id.language_id=...)"
и остальные аналогично. Суть в том, чтобы уменьшить таблицу которая участвует в join, тем самым ускорив процесс. Если делать как у вас, то сначала проходит объединение таблиц (очень накладная операция), а потом отсечение лишнего по where - это намного накладнее.

[Сергей]

Кстати, если хотите сделать пагинацию, то попробуйте CTE. Минус - не используются индексы, но если у вас на странице пара десятков записей из миллиона, то будет существенно быстрее всё равно.
В вашем случае:
with t as (select id from blog order by date_added desc, blog_id desc limit 5, 5 ) select i.* from blog i, t left join blog_description... where i.id=t.id

Тем самым вы существенно сократите первую таблицу, к которой джойнятся остальные.
И второе - НИКОГДА не используйте SELECT *, только перечисление полей, т.к. текстовые столбцы выбираются через временную таблицу и в будущем если вы добавить ещё столбец, селект может заглючить, например, появится 2 одинаковых имени.

Резюме: в таблицу t вы предварительно выбираете только айдишники с ограничением по кол-ву, по ним прикрепляете остальные поля с той же таблице, а потом join других.