Меню
Что нового?

Ведь HAVING после GROUP BY.

WP

^_^
Ведь HAVING после GROUP BY.

Задачка. Форум. Поиск. Есть две таблицы posts и topics (их поля и параметры описаны ниже).
Нужно выбрать топики (тип, название, описание, последнее сообщение), один или несколько постов составляющих которые (составляющих эти топики) релевантны предложенному запросу. При этом в таблице результатов каждый топик не должен встречаться более одного раза (это могло бы произойти если запросу релевантны несколько постов одной темы).
В форме поиска есть кропка "Показывать Сообщения", если она отмечена, то внутри таблицы с результатами, нужно показать пост данного топика который релевантен запросу, подсветив поисковые слова. А также если он, этот пост, не является первым в теме (if topicstart = 0), то прицеплять при наведении span с изображением первого поста. Прошу прощенья за подробный рассказ.
Казалось бы, всё элементарно:
PHP: 
   $result = $sql->query('SELECT SQL_CALC_FOUND_ROWS'
    .' `'.SQL_TBLPREFIX.'forum_topics`.*'
    .', MAX(MATCH (`'.SQL_TBLPREFIX.'forum_posts`.`subject`, `'.SQL_TBLPREFIX.'forum_posts`.`body`) AGAINST (\''.sqlescape($str_words).'\' IN BOOLEAN MODE)) AS `relevance`'
    .', `'.SQL_TBLPREFIX.'forum_topics`.*, `'.SQL_TBLPREFIX.'forum_posts`.`id` AS `lastpost`'
    .', `'.SQL_TBLPREFIX.'forum_posts`.`ctime` AS `lastpost_time`'
    .($show_posts?', `'.SQL_TBLPREFIX.'forum_posts`.`body`'
    .', `'.SQL_TBLPREFIX.'forum_posts`.`body_smiles`'
    .', `'.SQL_TBLPREFIX.'forum_posts`.`topicstart`':'')
    .', `'.SQL_TBLPREFIX.'users`.`username` AS `lastposter`'
    .', `'.SQL_TBLPREFIX.'groups`.`group_tag` AS `lastposter_group_tag`'
    .', `t1`.`username` AS `topicstarter`'
    .', `t2`.`group_tag` AS `topicstarter_group_tag`'
    .' FROM `'.SQL_TBLPREFIX.'forum_posts`'
    .' LEFT JOIN `'.SQL_TBLPREFIX.'forum_topics` ON `'.SQL_TBLPREFIX.'forum_posts`.`topicid` = `'.SQL_TBLPREFIX.'forum_topics`.`id`'
    .' LEFT JOIN `'.SQL_TBLPREFIX.'forum_posts` AS `t3` ON `'.SQL_TBLPREFIX.'forum_topics`.`lastpost` = `t3`.`id`'
    .' LEFT JOIN `'.SQL_TBLPREFIX.'users` ON `'.SQL_TBLPREFIX.'forum_posts`.`aid` = `'.SQL_TBLPREFIX.'users`.`id`'
    .' LEFT JOIN `'.SQL_TBLPREFIX.'users` AS `t1` ON `'.SQL_TBLPREFIX.'forum_topics`.`aid` = `t1`.`id`'
    .' LEFT JOIN `'.SQL_TBLPREFIX.'groups` ON `'.SQL_TBLPREFIX.'users`.`groupid` = `'.SQL_TBLPREFIX.'groups`.`gid`'
    .' LEFT JOIN `'.SQL_TBLPREFIX.'groups` AS `t2` ON `t1`.`groupid` = `t2`.`gid`'
    .' GROUP BY `'.SQL_TBLPREFIX.'forum_topics`.`id`'
    .' HAVING `relevance` > 0 ORDER BY `relevance` DESC'
    .' LIMIT '.($perpage*$pagenum).','.$perpage);
По поисковому слову "привет", с отмеченой галочкой "Показывать Сообщения" запрос выглядит так:
[sql]
SELECT SQL_CALC_FOUND_ROWS `xE_forum_topics`.*, MAX(MATCH (`xE_forum_posts`.`subject`, `xE_forum_posts`.`body`) AGAINST ('привет' IN BOOLEAN MODE)) AS `relevance`, `xE_forum_topics`.*, `xE_forum_posts`.`id` AS `lastpost`, `xE_forum_posts`.`ctime` AS `lastpost_time`, `xE_forum_posts`.`body`, `xE_forum_posts`.`body_smiles`, `xE_forum_posts`.`topicstart`, `xE_users`.`username` AS `lastposter`, `xE_groups`.`group_tag` AS `lastposter_group_tag`, `t1`.`username` AS `topicstarter`, `t2`.`group_tag` AS `topicstarter_group_tag` FROM `xE_forum_posts` LEFT JOIN `xE_forum_topics` ON `xE_forum_posts`.`topicid` = `xE_forum_topics`.`id` LEFT JOIN `xE_forum_posts` AS `t3` ON `xE_forum_topics`.`lastpost` = `t3`.`id` LEFT JOIN `xE_users` ON `xE_forum_posts`.`aid` = `xE_users`.`id` LEFT JOIN `xE_users` AS `t1` ON `xE_forum_topics`.`aid` = `t1`.`id` LEFT JOIN `xE_groups` ON `xE_users`.`groupid` = `xE_groups`.`gid` LEFT JOIN `xE_groups` AS `t2` ON `t1`.`groupid` = `t2`.`gid` GROUP BY `xE_forum_topics`.`id` HAVING `relevance` > 0 ORDER BY `relevance` DESC LIMIT 0,30
[/sql]
Но GROUP BY выполняется вперед HAVING поэтому поиск, как и следовало ожидать, производится лишь по первому посту темы. А WHERE использовать не можем, т.к. поле `relevance` не хранится в таблице.
Я давно не спал, поэтому плохо соображаю, не подскажите как быть? Не хотелось бы использовать временную таблицу.

Поля таблиц:

CREATE TABLE `xE_forum_topics` (
`id` int(10) unsigned NOT NULL auto_increment,
`section_id` int(11) NOT NULL default '0',
`subject` varchar(100) NOT NULL default '',
`description` varchar(100) NOT NULL default '',
`views` int(11) unsigned NOT NULL default '0',
`ctime` int(11) NOT NULL default '0',
`countposts` int(11) unsigned NOT NULL default '0',
`lastpost` int(11) NOT NULL default '0',
`aid` int(11) NOT NULL default '0',
`guest_username` varchar(100) NOT NULL default '',
`closed` tinyint(1) NOT NULL default '0',
`sticky` tinyint(1) NOT NULL default '0',
`announcement` tinyint(1) NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `section_id` (`section_id`),
FULLTEXT KEY `description` (`description`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251 COMMENT='Forum Topics'

xE_forum_posts | CREATE TABLE `xE_forum_posts` (
`id` int(10) unsigned NOT NULL auto_increment,
`topicid` int(11) NOT NULL default '0',
`topicstart` tinyint(1) NOT NULL default '0',
`replypost` int(10) unsigned NOT NULL default '0',
`aid` int(11) NOT NULL default '0',
`guest_username` varchar(100) NOT NULL default '',
`ip` int(10) unsigned NOT NULL default '0',
`subject` varchar(100) character set utf8 NOT NULL default '',
`body` longtext character set utf8 NOT NULL,
`body_smiles` tinyint(1) NOT NULL default '0',
`ctime` int(11) NOT NULL default '0',
`atime` int(11) NOT NULL default '0',
`timesedit` int(11) NOT NULL default '0',
`showsignature` tinyint(1) NOT NULL default '0',
PRIMARY KEY (`id`),
FULLTEXT KEY `fulltext` (`subject`,`body`)
ENGINE=MyISAM DEFAULT CHARSET=cp1251 COMMENT='Forum Posts' |
 

Clubber

Новичок
Если я правильно понял, тебе нужно следующее. На пальцах:

SELECT sum(match_against) as relevance
WHERE match_against > 0
GROUP BY
HAVING relevance > 0

-~{}~ 01.08.06 22:46:

sum - опционально
 

WP

^_^
Clubber
Там проблема с начислением получается... В общем лучше утром сделаю за 5 минут чем сейчас думать буду. Спасибо.
 

Clubber

Новичок
Если ты про sum vs. max, то это не было главной идеей :) А where match_against > 0, имхо, там необходим.

-~{}~ 01.08.06 23:14:

Но GROUP BY выполняется вперед HAVING поэтому поиск, как и следовало ожидать, производится лишь по первому посту темы.
Нажмите для раскрытия...
Кстати, может ты имел в виду "производится вывод первого поста для каждого топика"?

А то поиск то как раз должен быть по всем. Сначала mysql считает match_against для всех постов. Во время группировки, из них выбирается максимум + инфу о некоем посте. Затем фильтрует хэвингом.
 
Войдите или зарегистрируйтесь для ответа.
Сверху