Я пытаюсь построить крошечный поисковый движок, используя mysql.
Каждое упражнение может иметь произвольное количество поисковых тегов.
Вот моя структура данных:
TABLE exercises ID title TABLE searchtags ID title TABLE exerciseSearchtags exerciseID -> exercises.ID searchtagID -> searchtags.ID … где exerciseSearchtags – это много-много соединений, выражающих взаимосвязь между упражнениями и поисковыми тегами.
Поисковая система принимает неизвестное количество введенных пользователем ключевых слов.
Я хотел бы ранжировать результаты поиска в зависимости от количества совпадений ключевого слова / поиска.
Вот sql, который я сейчас использую для выбора упражнений. И правила CASE, и правила WHERE динамически генерируются, по одному для каждого ключевого слова. Так, например, если пользователь вводит 3 ключевых слова, будет 3 правила CASE и 3 правила WHERE.
SELECT exercises.ID AS ID, exercises.title AS title, ( (CASE WHEN searchtags.title LIKE CONCAT('%',?,'%') THEN 1 ELSE 0 END)+ (CASE WHEN searchtags.title LIKE CONCAT('%',?,'%') THEN 1 ELSE 0 END)+ ...etc... (CASE WHEN searchtags.title LIKE CONCAT('%',?,'%') THEN 1 ELSE 0 END) ) AS relevance FROM exercises LEFT JOIN exerciseSearchtags ON exerciseSearchtags.exerciseID = exercises.ID LEFT JOIN searchtags ON searchtags.ID = exerciseSearchtags.searchtagID WHERE searchtags.title LIKE CONCAT('%',?,'%') OR searchtags.title LIKE CONCAT('%',?,'%') OR ...etc... searchtags.title LIKE CONCAT('%',?,'%') GROUP BY exercises.ID ORDER BY relevance DESC
Это почти работает. Однако результаты не оцениваются в том порядке, который я ожидал бы.
Мое лучшее предположение о том, почему это происходит, заключается в том, что оценка релевантности вычисляется ДО тех пор, пока строки не сгруппированы с помощью exercise.ID. Поэтому, если левое соединение вызывает определенное упражнение 10 раз в результирующем наборе, а другое упражнение появляется 4 раза, то первое упражнение может получить более высокий балл релевантности, даже если у него может не быть больше матчей / поисковых матчей.
Есть ли у кого-нибудь какие-либо предложения / советы о том, как я могу предотвратить это?
Заранее спасибо за вашу помощь.
Я нашел рабочее решение для вышеупомянутой проблемы, и размещаю его здесь, в случае, если кто-то другой сталкивается с подобной проблемой.
Решение состоит в том, чтобы использовать подвыбор, а не оператор case. Вот приведенный выше код кода, исправленный. (Я не знаю, является ли это лучшим или наиболее эффективным решением, но оно устранило проблему для меня, время и, похоже, быстро возвращает результаты поиска.)
SELECT exercises.ID AS ID, exercises.title AS title, ( ( SELECT COUNT(1) FROM searchtags LEFT JOIN exerciseSearchtags ON exerciseSearchtags.searchtagID = searchtags.ID WHERE searchtags.title LIKE CONCAT('%',?,'%') AND exerciseSearchtags.exerciseID = exercises.ID )+ ( SELECT COUNT(1) FROM searchtags LEFT JOIN exerciseSearchtags ON exerciseSearchtags.searchtagID = searchtags.ID WHERE searchtags.title LIKE CONCAT('%',?,'%') AND exerciseSearchtags.exerciseID = exercises.ID )+ ...etc... ( SELECT COUNT(1) FROM searchtags LEFT JOIN exerciseSearchtags ON exerciseSearchtags.searchtagID = searchtags.ID WHERE searchtags.title LIKE CONCAT('%',?,'%') AND exerciseSearchtags.exerciseID = exercises.ID ) ) AS relevance FROM exercises LEFT JOIN exerciseSearchtags ON exerciseSearchtags.exerciseID = exercises.ID LEFT JOIN searchtags ON searchtags.ID = exerciseSearchtags.searchtagID WHERE searchtags.title LIKE CONCAT('%',?,'%') OR searchtags.title LIKE CONCAT('%',?,'%') OR ...etc... searchtags.title LIKE CONCAT('%',?,'%') GROUP BY exercises.ID ORDER BY relevance DESC
Разделите и победите. Вместо того, чтобы пытаться сделать все в одном утверждении, попробуйте разложить проблему на более мелкие части. Например, сначала создайте временную таблицу со всеми упражнениями, которые содержат хотя бы один из тегов поиска. Затем сделайте второй проход, чтобы ранжировать каждое упражнение в таблице temp. Наконец, выберите результат, упорядоченный по ранжированию.
Я только что сделал что-то подобное для MSSQL, а не mySQL … так что это может быть не актуально вообще, но его стоит сделать 🙂
Мне пришлось поставить CASE как часть предложения ORDER BY, чтобы заставить его правильно подобрать, например:
СОРТИРОВАТЬ ПО
CASE WHEN searchtags.title LIKE CONCAT ('%',?, '%') THEN 1 ELSE 0 END +
CASE WHEN searchtags.title LIKE CONCAT ('%',?, '%') THEN 1 ELSE 0 END +
...и т.д...
CASE WHEN searchtags.title LIKE CONCAT ('%',?, '%') THEN 1 ELSE 0 END DESC
В то же время оставляя их в SELECT, чтобы я мог выводить релевантность на странице (по запросу)
В любом случае, удачи в этом!