CODEWITHSUNDEEP
mysqlaliasmatchkeyword
Niko Efimov
Niko Efimov

Reputation: 2213

MySQL MATCH with multiple keywords

I'm building a feature that looks-up similar items to what user is inputting in the form. The user fills in two fields: type and manufacturer

I want to search for records in my database that match their corresponding values and return the best matches first.

I have a query that assigns a score to each field:

SELECT id, manuf, model, type, MATCH(manuf) AGAINST('BMW') AS `score_manuf`, MATCH(type) AGAINST('Car') AS `score_type` FROM `items`

This actually works and produces the scores.

Now I want to calculate the total score, that is the sum of two scores, to be later used to sort the results:

SELECT id, manuf, model, type, MATCH(manuf) AGAINST('BMW') AS `score_manuf`, MATCH(type) AGAINST('Car') AS `score_type`, (score_manuf + score_type) as 'score' FROM `items`

When I run this query, I get an error saying that "score_manuf" is an unknown column.

Can I not run arithmetic on alias fields? Or perhaps there is another way to accomplish what I'm trying to do?

Upvotes: 2

Views: 416

Answers (1)

Jose Rui Santos
Jose Rui Santos

Reputation: 15319

SELECT id, manuf, model, type,
       @m := MATCH(manuf) AGAINST('BMW') AS score_manuf,
       @t := MATCH(type) AGAINST('Car') AS score_type,
       @m + @t as score
  FROM items;

This is the way you define user-defined variables

Upvotes: 3

Related Questions