Последние темы посетившие поисковые боты [SQL]

[siv1987]

Вот такой интересный sql запрос получился для просмотра последних тем которые посетили поисковые боты из логов посещения ботов.

 

SELECT t.tid, t.title, FROM_UNIXTIME(start_date) start_date, FROM_UNIXTIME(s.entry_date) date_entry, s.bot, s.query_string 
FROM ibf_spider_logs s 
INNER JOIN (
SELECT sid, @prefix:='/forum/topic', @suffix:='.html', 
 IF(@ploc:=LOCATE(@prefix, query_string),
	SUBSTRING(
		query_string,
		@ploc + LENGTH(@prefix),
		IF(@sloc:=LOCATE(@suffix, query_string, @ploc+LENGTH(@prefix)), @sloc-(@ploc+LENGTH(@prefix)), 5)
	),
	0) as tid 
FROM ibf_spider_logs 
ORDER BY sid DESC 
LIMIT 2000) z ON z.sid=s.sid 
INNER JOIN ibf_topics t ON t.tid=z.tid 

Результат:

 

Запрос заточен под конкретный вид чпу темы /forum/topic1.html. Если у вас другой тип чпу, или вовсе его нету, в @prefix:= нужно указать то, что перед id темы, в @suffix:= то, что после.

Например, для стандартного формата чпу вида /topic/1-title.html, нужно указать для префикса @prefix:='/topic/', для суффикса @suffix:='-',

Для ссылок с параметрами (без чпу): @prefix:='showtopic=', @suffix:='&',

Результаты можно отсортировать как по дате посещения бота, так и по дате создания темы, позволяя отследить когда были просканированы новые темы на форуме. Сортировать вообще можно по множеству полей - ид темы, даты создания, автору, просмотров, и пр поля которые доступны в таблице topics.

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