[SOLVED] Relevance calculation with Match ... Against in JOIN queries (MySQL)

Relevance calculation with Match ... Against in JOIN queries (MySQL)

I am trying to get relevant search results, using the following JOIN query:

SELECT 
    `products`.`id`,
    `brands`.`name` AS `brand`,
    `products`.`name` AS `productname`,
    MATCH (`brands`.`name`) AGAINST ('somebrand someproduct' ) AS brandname_relevance
FROM
    `brands`
        INNER JOIN
    `products` ON `products`.`brand_id` = `brands`.`id`
WHERE
    MATCH (`products`.`name`) AGAINST ('somebrand someproduct' ) AS /* AS is highlighted as 'not valid input at this point' */ product_relevance
ORDER BY (brandname_relevance + product_relevance) DESC

However as it is noted in the code 'AS' is highlighted as invalid in this context by MySQL Workbench. The query fails.

Engine in both tables is InnoDB, however MySQL server Version is above 5.6 and FULLTEXT index is enabled for both brands.name and products.name. Kindly explain what is the problem here.

Solution

That is in the where clause - move it up with the other fields in your select.

SELECT 
    `products`.`id`,
    `brands`.`name` AS `brand`,
    `products`.`name` AS `productname`,
    MATCH (`brands`.`name`) AGAINST ('somebrand someproduct' ) AS brandname_relevance,
    MATCH (`products`.`name`) AGAINST ('somebrand someproduct' ) AS product_relevance
FROM
    `brands`
        INNER JOIN
    `products` ON `products`.`brand_id` = `brands`.`id`
ORDER BY (brandname_relevance + product_relevance) DESC