Как сортировать и фильтровать результаты поиска по нескольким полям в SQL
У меня есть база данных SQL с музыкальными песнями. У каждой песни, конечно, есть исполнитель, альбом и жанр. У них также есть общий счетчик популярности, который был получен из внешнего источника. Тем не менее, я хочу дать пользователям возможность голосовать и за песни. В конце концов, результаты поиска должны быть упорядочены по этой популярности, а также точности результатов с исходным запросом.
Текущий запрос, который я использую, выглядит следующим образом:
SELECT *
FROM p2pm_tracks
WHERE
`artist` LIKE '%$searchquestion%' OR
`genres` LIKE '%$searchquestion%' OR
`trackname` LIKE '%$searchquestion%' OR
`album_name` LIKE '%$searchquestion%'
ORDER BY `popularity` DESC
LIMIT $startingpoint, $resultsperpage
Я борюсь со следующим:
- Пользователи что-то ищут. Я смотрю во всех областях: песня заглавие, художник, альбом а также жанр. Однако, как правило, определенный поисковый запрос содержит (части) несколько из этих треков.
Например, пользователь может искать Opening Philip Glass,
В этом случае первое слово название песни, а второе и третье слова являются имя художника.
Другой пример:
Если я разделю запрос по пробелам, найдутся правильные треки. Однако, если другая дорожка, которая соответствует только одному из этих слов, имеет более высокую популярность, она будет возвращена раньше той, которая фактически точно соответствует поисковому запросу.
Я все еще хочу отсортировать результаты таким образом, чтобы вещи, которые совпадают с большими частями запроса, были на самом верху. Как я могу сделать это с помощью SQL?
- У меня есть статическая популярность и я хочу создать новую. Поэтому я хочу использовать среднее значение всех голосов на определенной дорожке (эти голоса хранятся в другой таблице), за исключением случаев, когда голосов еще нет. Как я могу построить запрос SQL, который делает это?
Мое приложение построено на PHP, но я хотел бы сделать как можно больше этого в SQL, предпочтительно в минимально возможном количестве запросов, чтобы уменьшить задержку.
Любая помощь будет оценена.
Решение
Вы можете добавить вес для каждого столбца в результатах поиска.
Вот код:
SELECT *,
CASE WHEN `artist` LIKE '%$searchquestion%' THEN 1 ELSE 0 END AS artist_match,
CASE WHEN `genres` LIKE '%$searchquestion%' THEN 1 ELSE 0 END AS genres_match,
CASE WHEN `trackname` LIKE '%$searchquestion%' THEN 1 ELSE 0 END AS trackname_match,
CASE WHEN `album_name` LIKE '%$searchquestion%' THEN 1 ELSE 0 END AS album_name_match,
FROM p2pm_tracks
WHERE
`artist` LIKE '%$searchquestion%' OR
`genres` LIKE '%$searchquestion%' OR
`trackname` LIKE '%$searchquestion%' OR
`album_name` LIKE '%$searchquestion%'
ORDER BY
`artist_match` DESC,
`genres_match` DESC,
`trackname_match` DESC,
`album_name_match` DESC,
`popularity` DESC,
LIMIT $startingpoint, $resultsperpage
Этот запрос будет собирать результаты, связанные с:
- художник ПЕРВЫЙ,
- ТОГДА жанр,
- ТОГДА название трека,
- ТОГДА название альбома,
- ТОГДА популярность песни
Чтобы оптимизировать этот запрос, вы должны избежать используя «LIKE» и используйте вместо него «FULLTEXT SEARCH».
Оптимизированный код будет:
SELECT *,
CASE WHEN MATCH (artist) AGAINST ('$searchquestion') THEN 1 ELSE 0 END AS artist_match,
CASE WHEN MATCH (genres) AGAINST ('$searchquestion') THEN 1 ELSE 0 END AS genres_match,
CASE WHEN MATCH (trackname) AGAINST ('$searchquestion') THEN 1 ELSE 0 END AS trackname_match,
CASE WHEN MATCH (album_name) AGAINST ('$searchquestion') THEN 1 ELSE 0 END AS album_name_match,
FROM p2pm_tracks
WHERE
MATCH (artist) AGAINST ('$searchquestion') OR
MATCH (genres) AGAINST ('$searchquestion') OR
MATCH (trackname) AGAINST ('$searchquestion') OR
MATCH (album_name) AGAINST ('$searchquestion')
ORDER BY
`artist_match` DESC,
`genres_match` DESC,
`trackname_match` DESC,
`album_name_match` DESC,
`popularity` DESC,
LIMIT $startingpoint, $resultsperpage
И убедитесь, что вы используете движок MyISAM для таблицы MySQL и создали индексы для столбцов, которые вы хотите найти.
Код для вашей таблицы MySQL должен выглядеть следующим образом:
CREATE TABLE p2pm_tracks (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
artist VARCHAR(255) NOT NULL,
trackname VARCHAR(255) NOT NULL,
...
...
FULLTEXT (artist,trackname)
) ENGINE=MyISAM;
Для получения дополнительной информации, проверьте следующее:
— http://dev.mysql.com/doc/refman/5.0/en/fulltext-natural-language.html
— http://dev.mysql.com/doc/refman/5.5/en/fulltext-boolean.html
Если вы ищете что-то более продвинутое, обратите внимание на Solr (на основе Lucene), Sphinx, ElasticSearch (на основе Lucene) и т. Д.
Другие решения
MySQL не очень хорош в поиске текста 🙁
-
Что вы можете попытаться сделать, это взглянуть на функциональность полнотекстового поиска (http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html)
-
С функцией сопоставления вы можете получить актуальность, где вы можете заказать.
ВЫБЕРИТЕ p2pm_tracks. *,
МАТЧ (исполнитель, жанры) ПРОТИВ («несколько слов») как актуальность,
МАТЧ (художник) ПРОТИВ («несколько слов») AS artist_relevance
Пожалуйста, не используйте как. Это очень медленно. Вы можете использовать полнотекстовый поиск в MySQL, но вы не можете определить, какой столбец является более важным.
Лучшее решение — MySQL со сфинксом.
Хм, соответствовать вашему примеру 1. сложно в SQL, я не уверен, есть ли функция.
что вам нужно, это что-то вроде этой функции в php
http://php.net/manual/function.similar-text.php
Или вы выбираете в своем запросе sql только среднее голосование и вычисляете, насколько «хорошими» результаты соответствуют с помощью php и функции аналогичного текста.