[SOLVED] MySQL MATCH AGAINST on Two Tables Not Sorting by Relevance

MySQL MATCH AGAINST on Two Tables Not Sorting by Relevance

I have a stored procedure using MATCH AGAINST that was originally returning data sorted by relevance, which is what I wanted. However, when I added a LEFT JOIN and a second MATCH AGAINST, it is now returning data from both, but no longer sorting by relevance. It is now automatically sorting by the ID.

Returning from one table sorted by relevance:

BEGIN
    SELECT
        dictionary.id,
        dictionary.`term`,
        dictionary.`definition`
    FROM itDictionary dictionary
    WHERE MATCH(dictionary.term, dictionary.definition)
    AGAINST(_query IN NATURAL LANGUAGE MODE);
END

Returning from two tables but somehow sorting by ID:

BEGIN
    SELECT
        dictionary.id,
        dictionary.`term`,
        dictionary.`definition`,
        links.id,
        links.`name`,
        links.`url`
    FROM itDictionary dictionary
        LEFT JOIN itRelatedLinks links ON links.term_id = dictionary.id
    WHERE MATCH(dictionary.term, dictionary.definition)
    AGAINST(_query IN NATURAL LANGUAGE MODE)
        OR MATCH(links.name)
    AGAINST(_query IN NATURAL LANGUAGE MODE);
END

Am I close?

Solution

You can do something like this

SELECT
    dictionary.id,
    dictionary.`term`,
    dictionary.`definition`,
    links.id,
    links.`name`,
    links.`url`
FROM itDictionary dictionary
    LEFT JOIN itRelatedLinks links ON links.term_id = dictionary.id
WHERE MATCH(dictionary.term, dictionary.definition)
AGAINST(_query IN NATURAL LANGUAGE MODE)
    OR MATCH(links.name)
AGAINST(_query IN NATURAL LANGUAGE MODE)
ORDER BY MATCH(dictionary.term, dictionary.definition)
AGAINST(_query IN NATURAL LANGUAGE MODE)
    + MATCH(links.name)
AGAINST(_query IN NATURAL LANGUAGE MODE);

It would add the score of both and have so a combined score to sort by

Of xourse you can add a factor to each score to prioritise one