How to SET a user-defined MySQL variable and increment it to produce ranking values with JDatabaseQuery?

Question

I'm trying to write this query:

SET @rownumber = 0;
UPDATE #__hikers SET rank = (@rownumber:=@rownumber+1)
ORDER BY score DESC;

like JDatabaseQuery, but still got an error. Please help

UPDATE: I tried this code 1:

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->set($db->quote(@rownumber = 0));
$query->update($db->quoteName('#__hikers'));
$query->set($db->quoteName('rank') . ' = ' .$db->quote(@rownumber:=@rownumber+1));
$query->order('score DESC');
$db->setQuery($query);
$result = $db->execute();

code 2:

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->set($db->quote(@rownumber).' = '.$db->quote (0));
$query->update($db->quoteName('#__hikers'));
$query->set($db->quoteName('rank') .' = '.$db->quote(@rownumber).' = '. $db->quote(@rownumber).' + '. $db->quote(1));
$query->order('score DESC');
$db->setQuery($query);
$result = $db->execute();

Answer 1

Joomla syntax aside, let's start by explaining your desired action.

You want to apply an incrementing counter to a specific column for all rows in the table. This is a very reasonable and common task.

Your raw query is correct. (SQLFiddle Demo)

The tricky part is getting Joomla to understand that you want the counter to be instantiated prior to the UPDATE query. Even harder is convincing Joomla to execute both operations in a single transaction. Assuming your table prefix is lmnop then you would want the following query.

SET @rownumber = 0;
UPDATE `lmnop_hikers` SET `rank` = (@rownumber := @rownumber + 1)
ORDER BY `score` DESC;

However, when you repeat the Joomla query method set(), Joomla tries to do you a favor and groups those clauses together with a comma (regardless of the order of your code lines).

$db = JFactory::getDBO();
try {
    $query = $db->getQuery(true)
                ->set("@rownumber = 0")
                ->update($db->qn("#__hikers"))
                ->set($db->qn("rank") . " = (@rownumber := @rownumber + 1)")
                ->order($db->qn("score") . " DESC");
    echo $query->dump();    // testing only, don't do this when live/public
    $db->setQuery($query);
    $db->execute();
    echo "<div>" . $db->getAffectedRows() . "</div>";
} catch (Exception $e) {
    echo "<div>" , $e->getMessage() , "</div>";  // don't do this when live/public
}

You will see:

And receive this error: (as you've discovered)

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@rownumber = 0 , `rank` = (@rownumber := @rownumber + 1) ORDER BY `score` DESC' at line 2

So how can we manufacture a query that performs the variable incrementation in a single transaction and successfully update the table? Well, it's not pretty...

I drew some inspiration from: #1221 - Incorrect usage of UPDATE and ORDER BY Keep in mind, the posted ranking technique is "tie-ignorant" (dense rank). Also, a comment claims: This doesn't work in MariaDB because ORDER BY is ignored in subqueries.

My first discovery -- joining on score for "Gapped Ranking": (SQLFiddle Demo)

UPDATE `lmnop_hikers` a 
INNER JOIN 
(
    SELECT *, @i := @i + 1 AS `counter`
    FROM `lmnop_hikers`
    CROSS JOIN (SELECT @i := 0) c
    ORDER BY `score` DESC
) b
ON a.`score` = b.`score`
SET a.`rank` = b.`counter`

Great, it works as desired on the fiddle. But wait, when I tried to run that through my Joomla/PHP or via phpMyAdmin I got skewed/broken results. (I didn't isolate the cause of the issue; I just started chiseling away at a workaround.)

After a while, I used this sledgehammer approach ...if you thought the first query was ugly...

This Raw Query works on my phpMyAdmin:

UPDATE lmnop_hikers a
INNER JOIN (
    SELECT b.*, MIN(counter) AS `min` FROM `lmnop_hikers` AS `b`
    INNER JOIN (
        SELECT *, @i := @i + 1 AS `counter`
        FROM `lmnop_hikers`
        CROSS JOIN (
            SELECT @i := 0
        ) d
        ORDER BY `score` DESC
    ) c ON `b`.`score` = `c`.`score`
    GROUP BY b.id
) b ON a.id = b.id
SET a.rank = b.`min`

Producing the following expected result:

Joomla/PHP Code:

$db = JFactory::getDBO();
try {
    $crossjoin = $db->getQuery(true)
                    ->select("@i := 0");

    $subinnerjoin = $db->getQuery(true)
                       ->select("*, @i := @i + 1 AS " . $db->qn("counter"))
                       ->from($db->qn("#__hikers"))
                       ->join("CROSS", "($crossjoin) d")
                       ->order($db->qn("score") . " DESC");

    $innerjoin =$db->getQuery(true)
                   ->select("b.*, MIN(" . $db->qn("counter") . ") AS " . $db->qn("min"))
                   ->from($db->qn("#__hikers", "b"))
                   ->innerJoin("($subinnerjoin) c ON " . $db->qn("b.score") . " = " . $db->qn("c.score"))
                   ->group($db->qn("b.id"));

    $query = $db->getQuery(true)
                ->update($db->qn("#__hikers", "a"))
                ->innerJoin("($innerjoin) b ON a.id = b.id")
                ->set($db->qn("a.rank") . " = " . $db->qn("b.min"));
    echo $query->dump();    // testing only, don't do this when live/public
    $db->setQuery($query);
    $db->execute();
    echo "<div>" , $db->getAffectedRows() , "</div>";
} catch (Exception $e) {
    echo "<div>" , $e->getMessage() , "</div>";  // don't do this when live/public
}

Printing this to screen:

UPDATE `lmnop_hikers` AS `a`
INNER JOIN (
SELECT b.*, MIN(`counter`) AS `min`
FROM `lmnop_hikers` AS `b`
INNER JOIN (
SELECT *, @i := @i + 1 AS `counter`
FROM `lmnop_hikers`
CROSS JOIN (
SELECT @i := 0) d
ORDER BY `score` DESC) c ON `b`.`score` = `c`.`score`
GROUP BY `b`.`id`) b ON a.id = b.id
SET `a`.`rank` = `b`.`min`

10

If anyone would like to offer any refinements or insights as to why the simpler JOIN query works on SQLFiddle (mysql5.6) but not my localhost (mysql5.7.19), they will be warmly received.

If you want to continue researching about different Ranking techniques, this might give some valuable insights: https://mattmazur.com/2017/03/26/exploring-ranking-techniques-in-mysql/