Нужна помощь с SQL для ранжирования результатов поиска

Я пытаюсь создать крошечную поисковую систему для упражнений, используя mysql.

Каждое упражнение может иметь произвольное количество тегов поиска.

Вот моя структура данных:

TABLE exercises
  ID
  title

TABLE searchtags
  ID
  title

TABLE exerciseSearchtags
  exerciseID -> exercises.ID
  searchtagID -> searchtags.ID

...где trainingSearchtags — таблица соединения «многие ко многим», выражающая отношения между упражнениями и тегами поиска.

Поисковая система принимает неизвестное количество ключевых слов, введенных пользователем.

Я хотел бы ранжировать результаты поиска на основе количества совпадений ключевого слова / тега поиска.

Вот 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

Это почти работает. Однако результаты ранжируются не в том порядке, в котором я ожидал.

Мое лучшее предположение о том, почему это происходит, заключается в том, что показатель релевантности рассчитывается ДО того, как строки будут сгруппированы по ИД упражнения. Таким образом, если левое соединение приводит к тому, что определенное упражнение появляется в наборе результатов 10 раз, а другое упражнение появляется 4 раза, то первое упражнение может получить более высокий показатель релевантности, даже если оно может не иметь больше совпадений ключевого слова / поискового тега.

Есть ли у кого-нибудь какие-либо предложения/советы о том, как я могу предотвратить это/исправить это?

Заранее спасибо за вашу помощь.


mysql php sql ranking search-engine
person Travis    schedule 02.11.2010    source источник

Ответы (3)


arrow_upward
1
arrow_downward

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

Решение состоит в том, чтобы использовать вложенный выбор вместо оператора 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
person Travis    schedule 02.11.2010

arrow_upward
0
arrow_downward

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

person Xint0    schedule 02.11.2010

arrow_upward
0
arrow_downward

Я сделал что-то подобное только для MSSQL, а не для mySQL... так что это может быть вообще не актуально, но стоит попробовать :)

Мне пришлось поместить CASE как часть предложения ORDER BY, чтобы заставить его правильно подобрать его, например:

ORDER BY
    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 DESC

Также оставив их в SELECT, чтобы я мог вывести релевантность на странице (по запросу)

В любом случае, удачи с ним!

person White Dragon    schedule 02.11.2010
comment
спасибо за предложение. Я попытался жестко закодировать правила CASE в операторе ORDER BY, но, к сожалению, это не изменило порядок результатов. Так что я предполагаю, что это также вычисляется до того, как строки сгруппированы в mysql. - person Travis; 02.11.2010
comment
Ах хорошо; позор, я проверил свой код на всякий случай, если я сделал что-то еще, чтобы заставить его работать, но это было все. Удачи! :) - person White Dragon; 02.11.2010