CODEWITHSUNDEEP
mysql
RoiEX
RoiEX

Reputation: 1235

Multiple variable assignments with single column 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 columns; 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 column 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 expected, but you can probably imagine where this is going if we have more than 3 terms!

Upvotes: 1

Views: 251

Answers (1)

tzunghaor
tzunghaor

Reputation: 1035

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.

Upvotes: 2

Related Questions