RoiEX RoiEX - 9 days ago 7
MySQL Question

Multiple variable assignments with single coloumn as result

I have a Table like this

+----+------+
| ID | Name |
+----+------+
| 0 | Foo |
| 1 | Bar |
+----+------+


Based on the Table rows I want to calculate a ranking based on each row, so that a query like

SELECT *, [calculation] AS ranking FROM table ORDER BY ranking DESC


would return something like

+----+------+---------+
| ID | Name | Ranking |
+----+------+---------+
| 0 | Bar | 3 |
| 1 | Foo | 1 |
+----+------+---------+


My problem is, that I need to perform multiple assignments per row without getting more coloumns; basically something like

@ranking:= 0
IF(Name LIKE $firstTerm, @ranking := @ranking + 1, @ranking := @ranking)
IF(Name LIKE $secondTerm, @ranking := @ranking + 1, @ranking := @ranking)
IF(Name LIKE $thirdTerm, @ranking := @ranking + 1, @ranking := @ranking)


For each row, so that in the end I have either 0, 1, 2 or 3 as one extra coloumn for each entry in my table

EDIT: In theory a dynamically generated query like

SELECT *
FROM table
ORDER BY SUM(
CASE
WHEN
(NAME LIKE $firstTerm AND NAME NOT LIKE $secondTerm AND NAME NOT LIKE $thirdTerm)
OR (NAME NOT LIKE $firstTerm AND NAME LIKE $secondTerm AND NAME NOT LIKE $thirdTerm)
OR (NAME NOT LIKE $firstTerm AND NAME NOT LIKE $secondTerm AND Name LIKE $thirdTerm) THEN 1
WHEN (Name LIKE $firstTerm AND Name LIKE $secondTerm AND Name NOT LIKE $thirdTerm)
OR (Name NOT LIKE $firstTerm AND Name LIKE $secondTerm AND Name LIKE $thirdTerm) THEN 2
WHEN (Name LIKE $firstTerm AND Name LIKE $secondTerm AND Name LIKE $thirdTerm) THEN 3
ELSE 0
) DESC;


Would work as exoected, but you can probably imagine where this is going if we have more than 3 terms!

Answer
SELECT *, 
    IF(Name like $firstTerm, 1, 0) + 
    IF(Name like $secondTerm, 1, 0) as Ranking 
  FROM table ORDER BY Ranking DESC;

However, if the dataset gets big, and the calculation complex, using a search engine (elasticsearch, solr) would be more efficient and more flexible.

Comments