Точный поиск в MySQL

243

03 мая 2018, 07:51

Имеется следующая таблица БД

--
-- Структура таблицы `board`
--
DROP TABLE IF EXISTS `board`;
CREATE TABLE IF NOT EXISTS `board` (
`id` int(11) auto_increment,
`user_id` int(11) NOT NULL,
`cat_id` varchar(250) NOT NULL,
`name` varchar(250) NOT NULL,
`translate` varchar(250) NOT NULL,
`text` varchar(15000) NOT NULL,
`email` varchar(64) NOT NULL,
`phone` varchar(20) NOT NULL,
`person` varchar(250) NOT NULL,
`region_id` int(11) NOT NULL,
`city_id` int(11) NOT NULL,
`address` varchar(500) NOT NULL,
`price` varchar(11) NOT NULL,
`time` int(11) NOT NULL,
`timeupdate` int(11) NOT NULL,
`timesrok` int(11) NOT NULL,
`status` enum ('1','2') NOT NULL default '2',
`activation` enum ('1','2') NOT NULL default '1',
`torg` enum ('0','1') NOT NULL default '0',
`free` enum ('0','1') NOT NULL default '0',
`vip` enum ('0','1') NOT NULL default '0',
`viptime` int(11) NOT NULL,
`select` enum('0','1') NOT NULL DEFAULT '0',
`selecttime` int(11) NOT NULL,
`confirm` enum('0','1') NOT NULL DEFAULT '0',
`sendemail` enum('0','1') NOT NULL DEFAULT '0',
`type` enum('1','2') NOT NULL default '1',
`del` enum('0','1') NOT NULL DEFAULT '0',
`file` enum('0','1') NOT NULL DEFAULT '0',
`views` int(11) NOT NULL default '0',
`url` varchar(2000) NOT NULL,
`lat` varchar(11) NOT NULL,
`lng` varchar(11) NOT NULL,
PRIMARY KEY  (`id`),
KEY `user_id` (`user_id`),
FULLTEXT `cat_id` (`cat_id`),
KEY `region_id` (`region_id`),
KEY `city_id` (`city_id`),
FULLTEXT `s1` (`name`, `text`, `price`),
KEY `status` (`status`),
KEY `activation` (`activation`),
KEY `del` (`del`),
KEY `file` (`file`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

В cat_id через запятую пишутся ID категории (возможно это не лучший подход). Нужно подсчитать кол-во объявлений в каждой категории

Так

SELECT `category`.*, (SELECT COUNT(1) FROM `board` WHERE `board`.`cat_id` LIKE CONCAT('%', `category`.`id`, '%') AND `status`='1' AND `activation`='1' AND `del`='0' И ТУТ МОЖЕТ БЫТЬ ЕЩЕ ДОП. ФИЛЬТРОВКА) AS `count` FROM `category` WHERE `refid`='1' ORDER BY `realid` ASC

считает не совсем верно.

Также имеется еще отдельная таблица в которую заносится id пользователя, id категории и id объявления.

--
-- Структура таблицы `board_category`
--
DROP TABLE IF EXISTS `board_category`;
CREATE TABLE IF NOT EXISTS `board_category` (
`user_id` int(11) NOT NULL,
`cat_id` int(11) NOT NULL,
`board_id` int(11) NOT NULL,
KEY `user_id` (`user_id`),
KEY `cat_id` (`cat_id`),
KEY `board_id` (`board_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

UPD: с джоинами не очень дружу, сделал так

SELECT t.*, (SELECT COUNT(*) FROM `board_category` WHERE `board_category`.`cat_id` = t.`id`) AS `count` FROM `category` t LEFT JOIN board c ON c.cat_id = t.id WHERE `refid`='1' ORDER BY `realid` ASC

Как в данный запрос можно добавить дополнительную фильтровку, например по полю status?

Answer 1

SELECT `cat_id`, COUNT(*) FROM `board_category` GROUP BY `cat_id`

Если нужна дополнительная фильтрация, можно сделать JOIN на таблицу объявлений и отфильтровать как хочется.

Answer 2

Решение с сайта https://toster.ru/q/527198

SELECT `category`.*,
  (SELECT COUNT(*)
   FROM `board_category`
   WHERE `board_category`.`cat_id` = `category`.`id`
     AND `board_id` IN
       (SELECT `id`
        FROM `board`
        WHERE `status`='1'
          AND `activation`='1'
          AND `del`='0')) AS `count`
FROM `category`
WHERE `refid`='1'
ORDER BY `realid` ASC