Сортировка и фильтр по вложенному запросу

[slavamakhotkin]

Сортировка и фильтр по вложенному запросу

Здравствуйте.

Есть две таблицы. В первой хранятся страницы сайта (поля id, url, deleted). Во второй странице хранится история посещения страниц (id, page_id, when, ip).

Нужно одним запросом получить список страниц сайта, отсортированный по времени последнего посещения, но исключить из списка страницы, посещенные с определенного айпи (скажем, 127.0.0.1). Как это можно сделать?

Я составил такой запрос:

[SQL]SELECT T.id, T.url, H.when
FROM `pages` T
LEFT JOIN (
SELECT `when`, page_id
FROM `history`
ORDER BY `when` DESC
) H ON H.page_id = T.id
WHERE T.deleted = 0
GROUP BY T.id
ORDER BY H.when DESC
[/SQL]

который вроде сортирует по времени последнего посещения. Но как добавить сюда фильтр по айпи адресу?

Можно, в принципе, сделать второй запрос. То есть, сначала получить список отсортированных страниц, а отфильтровать те, которые еще не посещались этим айпишником, примерно вот так:

[SQL]SELECT page_id
FROM `history`
WHERE page_id IN (айдишники) AND ip = '127.0.0.1' AND when IS NULL
[/SQL]

либо сложить эти оба запроса вместе и нагородить такую городулю:

[SQL]SELECT T.id
FROM (
SELECT T.id, T.url, H.when
FROM `pages` T
LEFT JOIN (
SELECT `when`, page_id
FROM `history`
ORDER BY `when` DESC
) H ON H.page_id = T.id
WHERE T.deleted = 0
GROUP BY T.id
) T
LEFT JOIN `history` H ON H.page_id = T.id AND H.ip = '127.0.0.1'
WHERE H.when IS NULL
ORDER BY T.when ASC
[/SQL]

Городуля эта работает, насколько я могу судить, правильно. Имеет ли она право на существование, или можно эту задачу решить эффективнее и элегантнее?

 

[Gas]

Я бы так точно не стал делать, а попробовал бы так (не запускал):

SELECT *, (SELECT `when` FROM history WHERE page_id = p.id AND ip <> '***' ORDER BY `when` dESC LIMIT 1) AS `when`
FROM pages AS p WHERE p.deleted = 0
ORDER BY `when` DESC

у hostory должен быть составной индекс (page_id,when)
если он не будет использоваться - попробовать с force index

С учётом того, что history будет пухнуть намного быстрее pages, такой вариант должен быть значительно лучше (если c проверяемого ip не много запросов).

 

[slavamakhotkin]

Gas

Мне нужно вообще не выводить страницы, посещенные определенным адресом. К тому же, судя по предыдущим опытам, page_id = p.id не сработает и майскуль ругнется, что не знает, что такое "p" (видимо, области видимости разграничены)

 

[dimagolov]

slavamakhotkin, почему ты не можешь отсеивать лишние IP в первом же подзапросе:
[sql]SELECT T.id, T.url, H.when
FROM `pages` T
LEFT JOIN (
SELECT `when`, page_id
FROM `history`
WHERE ip <> '127.0.0.1'
ORDER BY `when` DESC
) H ON H.page_id = T.id
WHERE T.deleted = 0
GROUP BY T.id
ORDER BY H.when DESC
[/sql]
п.с. кстати, так хранить IP в БД маразм

 

[slavamakhotkin]

Допустим, в таблице хистори две записи:
1 123 127.0.0.1 2010-03-24 00:00:00
2 123 86.45.23.01 2010-03-24 00:10:00

Тогда запрос
[SQL]
SELECT `when` , page_id
FROM `history`
WHERE ip <> '127.0.0.1'
ORDER BY `when` DESC
[/SQL]

Вернет строку с id = 2, и страница с page_id = 123 попадет в итоговую выборку. Мне же нужно, чтобы не попала. То есть если эту страницу посетил 127.0.0.1, то эта страница в результате запроса не должна отображаться

 

[Gas]

К тому же, судя по предыдущим опытам, page_id = p.id не сработает и майскуль ругнется, что не знает, что такое "p" (видимо, области видимости разграничены)

это у тебя были какие-то неудачные опыты, подзапросы нормально работают.

Мне нужно вообще не выводить страницы, посещенные определенным адресом.

А, теперь понятно. Я бы тогда сделал так:
в таблицу pages добавил поле visit_last, которое обновлял при каждом просмотре, на таблицу history навесил составной индекс (page_id, ip). IP конечно хранить нужно не строкой, а int unsigned not null.
В итоге получился бы примерно такой запрос:

SELECT *, (SELECT 1 FROM history WHERE page_id = p.id AND ip = '***' LIMIT 1) AS ip_exists
FROM pages AS p WHERE p.deleted = 0
HAVING ip_exists IS NULL
ORDER BY `visit_last` DESC

В ситуации когда страниц будет мало, а количество просмотров очень сильно расти, группировать history, сравнение "не равно" по большому количеству строк, джойнить derived query без индексов, как-то лчно мне не нравится.

 

[slavamakhotkin]

Спасибо. Так и думал, придется добавить last_visited.
(IP для примера, на самом деле там и так поле int unsigned not null)

 

[Gas]

Хотя здесь идеологически правильней даже использовать not exists вместо (select 1 ...) + having
Но смысл в принципе тот же остаётся.