Изменить параметры запроса поиска.

[Сергей85]

Есть поиск по статьям в CMS Joomla (находит статьи с не указанными разделами).

SELECT * FROM joomla_content WHERE metakey NOT LIKE '%раздел%';

Ввиду того, что не знаю SQL - прошу переделать запрос под 2 отдельные задачи:
- найти статьи, в которых указаны только теги вида "раздел";
- найти статьи, в которых тегов вообще нет.

Спасибо.

[Akina]

Цитата Сергей85 @ 20.08.20, 17:02

- найти статьи, в которых указаны только теги вида "раздел";

SELECT * FROM joomla_content WHERE metakey LIKE '%раздел%';
-- или
SELECT * FROM joomla_content WHERE metakey = 'раздел';

Какой из запросов использовать - зависит от того, как именно выглядит содержимое поля тегов.

Цитата Сергей85 @ 20.08.20, 17:02

- найти статьи, в которых тегов вообще нет.

SELECT * FROM joomla_content WHERE metakey = '';
-- или
SELECT * FROM joomla_content WHERE metakey IS NULL;

Какой из запросов использовать - зависит от того, что пишется в поле тегов при их отсутствии.

[Сергей85]

SELECT * FROM joomla_content WHERE metakey LIKE '%раздел%' - находит статьи. Но в них есть иные теги, помимо %раздел%.

SELECT * FROM joomla_content WHERE metakey = ''; - работает.

[MIF]

Запости несколько записей и скажи, какие из них надо выбрать запросом.

[Сергей85]

Цитата MIF @ 21.08.20, 21:12

Запости несколько записей и скажи, какие из них надо выбрать запросом.

Статья 1. Теги: раздел-1, статья, мое.
Статья 2. Теги: раздел-1.
Статья 3. Теги: статья, мое.
Статья 4. Теги: статья2, мое2.
Статья 5. Теги: раздел-2,раздел-что-угодно.

Запросом нужно поймать статьи 2 и 5: теги только вида "раздел".

[MIF]

Имо,здесь регекс нужен.
Какой тип базы данных?

[Сергей85]

Цитата MIF @ 23.08.20, 15:18

mysql

[MIF]

В выборке есть записи с пробелом и без пробела после запятой. В реальной таблице есть оба паттерна?

[Akina]

Цитата Сергей85 @ 23.08.20, 15:23

mysql

А версия? в данной задаче это важно. Например, для версии 8.0.4+ можно разобрать CSV-поле тегов на отдельные теги, получив нормализованное представление, на котором реляционное деление выполняется элементарно.

Цитата MIF @ 21.08.20, 21:12

Запости несколько записей и скажи, какие из них надо выбрать запросом.

Оформи в теге таблицы, чтобы было чётко видно, где начинается и где заканчивается значение для каждого отдельного поля.

Сообщение отредактировано: Akina - 24.08.20, 04:48

[Сергей85]

Цитата MIF @ 23.08.20, 20:41

В реальной таблице пробелов рядом с запятой нет.

Добавлено 24.08.20, 14:03

Цитата Akina @ 24.08.20, 04:45

версия MySQL: 5.6

Добавлено 24.08.20, 14:05

Цитата Akina @ 24.08.20, 04:45

Цитата MIF @ 21.08.20, 21:12

Запости несколько записей и скажи, какие из них надо выбрать запросом.

Оформи в теге таблицы, чтобы было чётко видно, где начинается и где заканчивается значение для каждого отдельного поля.

Не понял. Теги все хранятся в 1 поле "metakey".

[Akina]

Цитата Сергей85 @ 24.08.20, 14:02

Не понял. Теги все хранятся в 1 поле "metakey".

Не понял - не надо. Но если просят - наверное, не просто так, а?
Просто сделай. А потом, если хочется, удивляйся, любопытствуй или переспрашивай.

Вот, например, твоя строка:

Цитата Сергей85 @ 23.08.20, 04:44

Статья 1. Теги: раздел-1, статья, мое.

Что у тебя в поле metakey?
"Статья 1. Теги: раздел-1, статья, мое."?
"Теги: раздел-1, статья, мое."?
"раздел-1, статья, мое."?

[Сергей85]

Цитата Akina @ 24.08.20, 19:22

"раздел-1, статья, мое."

"раздел-1, статья, мое". Поле тегов, называется metakey.

[MIF]

Дык есть пробелы или их нет?

[Akina]

Возможное решение для версии 5.6.

Поле metakey содержит несколько тегов, разделённых запятыми. При этом предполагается, что сами теги запятых не содержат (иначе нет возможности определить, что делает запятая - разделяет два тега или является компонентом одного тега). Также предполагается, что максимально возможное количество тегов в одной записи - ограничено (даже если формально такого ограничения нет, есть физическое ограничение на размер поля и на размер тега, так что реально такое максимально возможное количество существует, пусть и может быть достаточно большим).

На указанной версии, которая не поддерживает СТЕ, невозможно динамически генерировать наборы записей в запросе. В то же время задача требует парсинга отдельных тегов по номеру (или итеративного парсинга в хранимой процедуре), для чего необходима таблица чисел от 1 до того самого максимально возможного количества тегов. В принципе решаемо набором подзапросов, но суммарный запрос может оказаться достаточно тяжёлым. Рекомендую один раз создать отдельную таблицу с числами от 1 до максимально возможного количества тегов на поле, или до достаточно большого значения, и затем её использовать. Например, с числами от 1 до 1000, в БД mysql - это снизит нагрузку при выполнении запроса:

CREATE TABLE mysql.numbers (id INT PRIMARY KEY)
SELECT 1 + n1.num + n2.num * 10 + n3.num + 100
FROM (SELECT 0 num UNION SELECT 1 UNION ... UNION SELECT 9) n1
JOIN (SELECT 0 num UNION SELECT 1 UNION ... UNION SELECT 9) n2
JOIN (SELECT 0 num UNION SELECT 1 UNION ... UNION SELECT 9) n3;

Теперь задача упрощается. Если максимальное количество тегов не превышает 1000, то запрос

SELECT j.article_id,
       1 + LENGTH(metakey) - LENGTH(REPLACE(metakey, ',', '')) total
       SUM(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(j.metakey, ','), ',', n.id), ',', -1) LIKE 'раздел%') matched
FROM joomla_content j
JOIN mysql.numbers n
GROUP BY j.article_id
HAVING ... ;

позволяет получить требуемые данные. Расчётное поле matched равно количеству тегов, которые соответствуют шаблону, total - соответственно равно общему количеству тегов. Например, если нужны статьи, которые не содержат других тегов - то для таких статей должно выполняться условие total = matched, и именно его следует поместить в HAVING.

Если всё же пробелы возможны, то следует использовать

SUM(TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(j.metakey, ','), ',', n.id), ',', -1)) LIKE 'раздел%') matched

Но запрос всё равно будет тяжёлым. И, что самое плохое, без шансов использовать индексы.

Если максимальное количество тегов на поле меньше - следует уменьшить количество чисел в таблице mysql.numbers, а если оно в пределах 10-20, то просто генерировать набор чисел непосредственно в запросе.

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

Сообщение отредактировано: Akina - 26.08.20, 06:37

[MIF]

У меня получился вот такой запрос.
Только это MS SQL, может для mysql надо чтото подправить.
И латиница, мне запросы с кириллицей не интересны.
Тест таблица и запрос:

DECLARE @T TABLE(metakey varchar(max))
 
INSERT INTO @T(metakey) VALUES('razdel-128.')
INSERT INTO @T(metakey) VALUES('me,razdel-12.')
INSERT INTO @T(metakey) VALUES('razdel-128,razdel-me,razdel-16.')
INSERT INTO @T(metakey) VALUES('razdel-128,razdel-merazdel-16.')
INSERT INTO @T(metakey) VALUES('razdel-128,razdel-me,anotherrazdel,razdel-16.')
 
SELECT *
FROM @T
WHERE metakey like 'razdel-%'
AND LEN (metakey) - LEN(REPLACE(metakey, ',', ''))=  (LEN(metakey) -  LEN(REPLACE(metakey,',razdel-','')))/8

output:

razdel-128.
razdel-128,razdel-me,razdel-16.
razdel-128,razdel-merazdel-16.