ГРУППИРОВАТЬ результат ПО определенному ключевому слову в MySQL?

ProgramBox

ГРУППИРОВАТЬ результат ПО определенному ключевому слову в MySQL?

Post author:admin
Запись опубликована:14 мая, 2023
Post category:Вопросы по программированию

#php #mysql #group-by #tagging #sql-like

#php #mysql #группировать по #тегирование #sql-подобный

Вопрос:

У меня есть страница, помеченная несколькими тегами с ключевым словом, которое я ищу, и иногда она не помечена этим ключевым словом, поэтому, когда у нее есть эти теги, она вернет результат, подобный приведенному ниже,

запрос,

 SELECT*
FROM root_pages AS p

LEFT JOIN root_mm_pages_tags AS mm
ON mm.page_id = p.page_id

LEFT JOIN root_tags AS t
ON t.tag_id =  mm.tag_id
AND t.tag_name LIKE '%story%'

WHERE p.page_title LIKE '%article title 8%'
AND p.page_hide != '1'

ORDER BY (t.tag_name 0) ASC

Результат,

 page_id     page_url            tag_name    
17          article title 8     NULL
17          article title 8     NULL
17          article title 8     sys-rsv-story-1

итак, я должен использовать GROUP BY для решения этой проблемы,

 SELECT*
FROM root_pages AS p

LEFT JOIN root_mm_pages_tags AS mm
ON mm.page_id = p.page_id

LEFT JOIN root_tags AS t
ON t.tag_id =  mm.tag_id
AND t.tag_name LIKE '%story%'

WHERE p.page_title LIKE '%article title 8%'
AND p.page_hide != '1'

GROUP BY p.page_id
ORDER BY (t.tag_name 0) ASC

и это возвращает что-то вроде этого,

 page_id     page_url            tag_name    
17          article title 8     NULL

Но мне нужен этот результат, в котором есть ключевое слово, которое я ищу,

 page_id     page_url            tag_name    
17          article title 8     sys-rsv-story-1

Итак, возможно ли сгруппировать результат по ключевому слову? Или другие запросы получше для архивирования этого?

Кроме того, он не должен возвращать результат, если этого ключевого слова там нет, но он все равно возвращает,

 page_id     page_url            tag_name    
    17          article title 8     NULL
    17          article title 8     NULL

Редактировать:

Мое новое решение,

  SELECT*
FROM root_pages AS p

INNER JOIN root_mm_pages_tags AS mm
ON mm.page_id = p.page_id

INNER JOIN root_tags AS t
ON t.tag_id =  mm.tag_id

WHERE p.page_title LIKE '%{group1}%'
AND t.tag_name LIKE '%story%'
AND p.page_hide != '1'

AND EXISTS (
    SELECT page_url
    FROM root_pages AS p

    LEFT JOIN root_mm_pages_tags AS mm
    ON mm.page_id = p.page_id

    LEFT JOIN root_tags AS t
    ON t.tag_id =  mm.tag_id

    WHERE page_url = 'article title 1d'
    AND t.tag_name LIKE '%story%'
    AND p.page_hide != '1'
)

ORDER BY (t.tag_name 0) ASC

Ответ №1:

Старайтесь не использовать condition в LEFT JOIN:

 SELECT *
FROM root_pages AS p

LEFT JOIN root_mm_pages_tags AS mm
ON mm.page_id = p.page_id

LEFT JOIN root_tags AS t
ON t.tag_id =  mm.tag_id

WHERE p.page_title LIKE '%article title 8%'
AND p.page_hide != '1'
AND t.tag_name LIKE '%story%'

GROUP BY p.page_id
ORDER BY (t.tag_name 0) ASC

РЕДАКТИРОВАТЬ: Если вы хотите получить строки, заголовок страницы которых содержит ‘название статьи’, и строки, которые не имеют этого названия, но содержат необходимое ключевое слово, используйте этот запрос (как предложил @user985935):

 SELECT *
FROM root_pages AS p

LEFT JOIN root_mm_pages_tags AS mm
ON mm.page_id = p.page_id

LEFT JOIN root_tags AS t
ON t.tag_id =  mm.tag_id

WHERE (p.page_title LIKE '%article title 8%'
OR t.tag_name LIKE '%story%')
AND p.page_hide != '1'


GROUP BY p.page_id
ORDER BY (t.tag_name 0) ASC

1. Это был мой первоначальный запрос, но мне нужно сделать этот запрос динамическим для строк с этим ключевым словом, а также для строк без этого ключевого слова. имеет ли это смысл? Спасибо.

2. если это так, как насчет того, чтобы использовать или вместо и условие like (p.page_title, НАПРИМЕР ‘%article title 8%’ Или t.tag_name, НАПРИМЕР ‘%story%’) просто измените приведенный выше запрос, тогда вы сможете добиться динамического результата из него. 😉

3. Спасибо за подсказку, Дмитрий! 🙂

Ответ №2:

Ой.

Я думаю, что ваши SQL-запросы довольно странные.

Несколько моментов, на которые следует обратить внимание:

использование bar LIKE '%foo%' очень сложно для движка SQL, он должен последовательно сканировать все строки и искать подстроку ‘foo’ в строке столбца. Использование индекса недоступно. Так что избегайте этого, если можете. Используйте хотя бы bar LIKE 'foo%' , если можете (индекс доступен, если у вас есть start). И в вашем случае у вас могли бы быть страницы с соответствующим заголовком ‘article title 80’, вы уверены, что вам просто не нужен p.page_title = 'article title 8' ?
почему вы делаете 0 в порядке по инструкции? Вы действительно хотите запретить использование индекса?
p.page_hide != '1' стр.page_hide разве это не tinyint? это строка? зачем использовать символы в кодировке UTF8 для хранения 0 или 1?

Но проблема не в этом.

Одна из ваших проблем заключается в том, что использование group by GROUP BY p.page_id на самом деле неверно в SQL, но MySQL скрывает этот факт. Команда group by должна содержать по крайней мере каждый элемент, который не является aggegate в части SELECT (совокупность — это count или sum, или avg и т.д.). Здесь вы группируете по идентификатору и получаете случайную вещь, MySQL думает, что вы знаете, что делаете, и вы уверены, что все остальные поля в select одинаковы, когда идентификатор тот же (что не так, имя_тега отличается).

И если у вас есть несколько тегов, соответствующих вашему ключевому слову (‘story’ здесь), разве вы не хотите, чтобы страница отображалась несколько раз? со всеми тегами?

Итак.

Вы хотите выбрать страницу, где у вас есть тег. Я бы сказал, использовать EXISTS ключевое слово и упростить задачу.

Это может быть что-то вроде этого:

 SELECT * 
 FROM root_pages AS p
WHERE p.page_title = 'article title 8'
 AND p.page_hide != 1
 -- exists will return true as soon as the engine find one matching row
 AND EXISTS (
  SELECT mm.page_id
  FROM root_mm_pages_tags AS mm
    LEFT JOIN root_tags AS t
      ON t.tag_id =  mm.tag_id
  -- here we make a correlation between the subquery and the main query
  WHERE mm.page_id = p.page_id
  AND t.tag_name LIKE '%story%'
)

Но с помощью этого запроса вы получаете только название страницы, а не результат тега. И если вы хотите перечислить все совпадающие теги для страницы, вам нужен другой запрос, довольно близкий к тому, что у вас есть:

 SELECT p.page_id, p.page_name, t.tag_name
 FROM root_pages AS p
   INNER JOIN root_mm_pages_tags AS mm
       ON mm.page_id = p.page_id
     INNER JOIN root_tags AS t
         ON (t.tag_id =  mm.tag_id 
         AND t.tag_name LIKE '%story%')
WHERE p.page_title = 'article title 8'
 AND p.page_hide != 1

С первым INNER JOIN я сохраняю только страницы, у которых есть теги. Со вторым INNER JOIN я только не допускаю, чтобы в строках root_mm_pages был соответствующий тег root_tags . Я думаю, что ваш NULL получен из строк в этих таблицах, связанных с другими несоответствующими тегами (поэтому наличие поля NULL в таблице root_tags приводит к вашему запросу). Поэтому не используйте LEFT JOIN, если вам нужны только результаты сопоставления.

Если вы хотите получить только один результат для каждой таблицы, вам понадобится GROUP BY p.page_id, p.page_name и вам нужно будет добавить агрегатную функцию для оставшегося поля t.tag_name . Вы могли бы использовать GROUP_CONTACT(t.tag_name ORDER BY t.tag_name ASC SEPARATOR ",") для получения списка всех совпадающих тегов для этой таблицы.

Редактировать

Похоже, на самом деле вам нужны страницы с соответствующим заголовком ИЛИ страницы с соответствующим ключевым словом. В этом случае вы должны использовать LEFT JOIN , и у вас будут нулевые значения. Если вам не нужен тег в результате, ключевое слово EXISTS по-прежнему является вашим лучшим другом, просто замените AND EXISTS на OR EXISTS . Это самое быстрое решение.

Если вам нужны совпадающие теги в результате или NULL, когда они не были тегами, у вас есть 2 решения. UNION Смешивание запросов является результатом простого запроса к заголовкам и запроса к тегам с внутренними объединениями или выполнения хорошей группы с помощью GROUP_CONCAT. Если вы не используете GROUP_CONCAT (как в ответе @Dmitry Teplyakov), вы, возможно, получите результаты, в которых заголовок страницы не совпадает, только ключевое слово, но в поле tag_name будет отображаться NULL, поскольку первый tag_row, указанный перед применением GROUP BY к запросу, является НУЛЕВЫМ полем — страница как 3 ключевых слова, совпадающее ключевое слово не является первым в запросе -.

 SELECT 
 p.page_id,
 p.page_name,
 GROUP_CONCAT(t.tag_name ORDER BY t.tag_name ASC SEPARATOR ",")
FROM root_pages AS p
   LEFT JOIN root_mm_pages_tags AS mm
       ON mm.page_id = p.page_id
     LEFT JOIN root_tags AS t
         ON t.tag_id =  mm.tag_id 
WHERE p.page_hide != 1
 AND (p.page_title = 'article title 8'
  OR t.tag_name LIKE '%story%')
GROUP BY p.page_id, p.page_name;

Но здесь мы теряем ваш порядок по tag_name. Упорядочивание по имени тега означает, что вы хотите, чтобы одна и та же страница отображалась в нескольких строках, если она соответствует ключевому слову несколько раз. Или, если имя совпадает, и ключевое слово также … или, может быть, нет. Так что на самом деле решение с ОБЪЕДИНЕНИЕМ запросов, возможно, лучше. Но ключевой момент в том, что вы должны объяснить, что вы хотите в поле tag_name 🙂

1. Большое спасибо за этот ответ, regilero. Я действительно получаю результат, к которому стремлюсь, по вашему предложению — смотрите Мою правку выше. Спасибо за помощь! 🙂

2. @lauthiamkok: смотрите мою правку, я не уверен, что у вас действительно есть то, что вы хотите. Зависит от того, чего вы действительно хотите. Будьте осторожны в SQL с запросами, которые, кажется, дают правильные результаты, создавайте расширенные тестовые примеры.

3. regilero, спасибо за редактирование. Я меняю свой код на use INNER JOIN , чтобы быть в безопасности. И да, мне нужно создать расширенные тестовые примеры. Спасибо! 🙂

4. Но если вы используете только ВНУТРЕННЕЕ ОБЪЕДИНЕНИЕ, то это приведет к удалению страниц с совпадающими заголовками и без ключевых слов, запрос ВНУТРЕННЕГО ОБЪЕДИНЕНИЯ следует использовать в запросе ОБЪЕДИНЕНИЯ с соответствующим запросом заголовка.

5. Может быть, я должен просто использовать LEFT JOIN ! lol протестирован в различных случаях, пока все в порядке… Я думаю, мне нужно больше понять разницу между LEFT JOIN и INNER JOIN , поскольку они всегда меня смущают. Большое вам спасибо за ваш ответ!:-)

Ответ №3:

вот пример запроса, который я упоминаю в комментарии:

 SELECT *
FROM root_pages AS p

LEFT JOIN root_mm_pages_tags AS mm
ON mm.page_id = p.page_id

LEFT JOIN root_tags AS t
ON t.tag_id =  mm.tag_id

WHERE p.page_hide != '1'
AND (t.tag_name LIKE '%story%' OR p.page_title LIKE '%article title 8%')
GROUP BY p.page_id
ORDER BY (t.tag_name 0) ASC