Как проверить все соединения при создании оценки с помощью MYSQL

Я нахожу это довольно трудным для объяснения, поэтому, пожалуйста, расскажите мне обо мне здесь...

Я использую MYSQL для создания оценки для каждого результата, возвращаемого запросом. Затем результаты упорядочиваются по результату.

Часть, которая, похоже, не работает должным образом, - это когда я пытаюсь добавить оценку для каждого найденного тега, а результат назначен. Так что скажем, что я выполняю поиск тегов "example", "test" и "tag", и один из моих результатов присваивается тегам "example", "test", "someothertag", который должен принести оценку 10 так как есть 2 совпадения.

Что на самом деле происходит, я получаю оценку 5, если есть совпадение, независимо от того, сколько тегов сопоставлено. и 0, если теги не совпадают.

Ниже приведен пример одного из запросов, созданных при поиске.

        SELECT DISTINCT results.*,
                    ( 
                        5*(MATCH(tags.name) AGAINST('"self employed"' IN BOOLEAN MODE)) +
            5*(MATCH(tags.name) AGAINST('"rental income"' IN BOOLEAN MODE)) +
            5*(MATCH(tags.name) AGAINST('"commission income"' IN BOOLEAN MODE)) +
            5*(MATCH(tags.name) AGAINST('"bankruptcy"' IN BOOLEAN MODE)) +
            5*(MATCH(tags.name) AGAINST('"condo approval"' IN BOOLEAN MODE)) +

                        1*usefulness + 
                        10*shares 
                    ) AS score 
        FROM results
        INNER JOIN categories c on results.ID = c.RESULT_ID
        INNER JOIN tags ON results.id = tags.result_id
        WHERE c.name in ('purchase', 'condo', 'va')
        AND ( tags.name = 'self employed' OR tags.name = 'rental income' OR tags.name = 'commission income' OR tags.name = 'bankruptcy' OR tags.name = 'condo approval'  )
        AND ( results.scope = 'all' OR results.scope = 'hi' )
        AND published = 1

        GROUP BY results.ID
        having count(distinct c.c_id) = 3
        ORDER BY score DESC 
        LIMIT 8 OFFSET 0

Ответ 1

Как советует Сэм Дюфел, вам, вероятно, не нужен полнотекстовый поиск, тем более, что вы используете точное сравнение строк в своих предложениях WHERE.

Кроме того, из-за отношения "много-ко-многим" между results и categories (предполагается из предложения HAVING COUNT(c_id) = 3), я думаю, вы никоим образом не можете присоединиться к categories и tags в тот же запрос.

Без предложения GROUP BY для одного заданного result вы получите одну строку для каждого соответствия category. Для каждой пары соответствия (result, category) вы должны получить одну строку для каждого соответствия tag.name. Я не думаю, что есть способ справиться с таким результатом.

Я бы предложил следующее:

Шаг 1: получение results присутствует во всех трех категориях

SELECT results.ID
FROM results
JOIN categories ON results.id = categories.result_id
WHERE categories.name IN ('purchase', 'condo', 'va')
GROUP BY results.ID
HAVING COUNT(DISTINCT c.c_id) = 3

Шаг 2: вычисление оценки любого results совпадения, по меньшей мере, одной строки поиска

SELECT
    DISTINCT results.*, -- DISTINCT is redundant because of the GROUP BY clause
    ( 
        5*(COUNT(tags.result_id)) + -- you actually want to count the number of matches!
        1*usefulness +  -- warning, see below 
        10*shares       -- warning, see below
    ) AS score 
FROM results
INNER JOIN tags ON results.id = tags.result_id
WHERE
    tags.name = 'self employed'
    OR tags.name = 'rental income'
    OR tags.name = 'commission income'
    OR tags.name = 'bankruptcy'
    OR tags.name = 'condo approval'
GROUP BY results.ID

Шаг 3: все вместе

SELECT
    results.*,
    ( 
        5*(COUNT(tags.result_id)) +
        1*usefulness +  -- warning, see below 
        10*shares       -- warning, see below
    ) AS score 
FROM (
        SELECT results.id
        FROM results
        JOIN categories ON results.id = categories.result_id
        WHERE
            categories.name IN ('purchase', 'condo', 'va')
            AND ( results.scope = 'all' OR results.scope = 'hi' )
            AND published = 1
        GROUP BY results.id
        HAVING COUNT(DISTINCT categories.c_id) = 3
) AS results_subset
JOIN results ON results_subset.id = results.id
JOIN tags ON results.id = tags.result_id
WHERE
    tags.name = 'self employed'
    OR tags.name = 'rental income'
    OR tags.name = 'commission income'
    OR tags.name = 'bankruptcy'
    OR tags.name = 'condo approval'
GROUP BY results.ID

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

Слово предупреждения: поля usefulness и shares не являются частью функции GROUP BY, не включенной в функцию агрегации. Это разрешено MySQL, но очень опасно. Если usefulness и shares относятся к таблице, отличной от result (таблица GROUP'ed BY), значения, возвращаемые в вашем запросе, undefined.

Ответ 2

напишите его следующим образом:

   "sum((5*(MATCH(tags.name) AGAINST('"self employed"' IN BOOLEAN MODE))), 
        (5*(MATCH(tags.name) AGAINST('"rental income"' IN BOOLEAN MODE))) ,
        (5*(MATCH(tags.name) AGAINST('"commission income"' IN BOOLEAN MODE))),
        (5*(MATCH(tags.name) AGAINST('"bankruptcy"' IN BOOLEAN MODE))),
        (5*(MATCH(tags.name) AGAINST('"condo approval"' IN BOOLEAN MODE))),
      (1*usefulness), (10*shares)) as score"

Ответ 3

Вам нужно указать значение SUM(), потому что строка ONE соответствует только одному тегу.

В вашем запросе выбрано несколько строк и сгруппировано по идентификатору, поэтому вы получаете результат только для ONE Row, и это всегда будет 5 в вашем случае.

Ответ 4

Я думаю, что ваш запрос слишком сложный. Попробуйте следующее:

SELECT
    results.*,
    5 * count(distinct tags.name) + 1*usefulness + 10*shares AS score 
FROM results
JOIN categories c on results.ID = c.RESULT_ID
    AND c.name in ('purchase', 'condo', 'va')
JOIN tags ON results.id = tags.result_id
    AND tags.name in ('self employed', 'rental income', 'commission income', 'bankruptcy', 'condo approval')
WHERE results.scope in ('all', 'hi')
AND published = 1
GROUP BY 1, 2, 3, 4, 5 -- list as many numbers here as there are columns in "results" 
HAVING count(distinct c.c_id) = 3
ORDER BY score DESC 
LIMIT 8 OFFSET 0

Одна из ключевых проблем, с которой вы столкнулись, - это группировка - чтобы она работала правильно, вам нужно либо указать, либо указать выбранную позицию, все столбцы таблицы results. Вы не указали схему таблицы, поэтому я не мог знать, что писать. Я предположил 5 столбцов, поэтому GROUP BY 1, 2, 3, 4, 5, но вы должны убедиться, что это правильно.

Я прибрал ваш OR, изменив их на IN - это позволит использовать индексы для этих столбцов, если такие индексы существуют ( "OR" не будет использовать индекс).

Я переместил некоторые условия условия WHERE в условия JOIN, где это имело смысл - это должно повысить производительность.