0

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();
  • 1
    Can you show us your JDatabase query please? – Lodder Feb 4 '18 at 15:02
  • Of course, I did an update to my question. Thank you – Midhat Feb 4 '18 at 17:25
  • @Lodder, any advice? – Midhat Feb 8 '18 at 15:26
  • 1
    In your set clause, replace $db->quoteName('#__rank') with $db->quoteName('rank'). You only use #__ when defining a table name (which is a replacement for the prefix), not a column name – Lodder Feb 8 '18 at 15:28
  • Thank you @Lodder. I tried, but I always have a error with @rownumber. Will it work if I make an php variable $rownumber instead of @rownumber? (1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''rownumber' = '0' , rank = 'rownumber' = 'rownumber' + '1' ORDER BY score DES') – Midhat Feb 8 '18 at 17:02
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:

enter image description here

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:

enter image description here

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/

  • 1
    This is indeed a great answer!! – Mohd Abdul Mujib Dec 20 '18 at 14:30
  • @Midhat Have I sufficiently solved your issue? Let's resolve this page. – mickmackusa Mar 11 at 23:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.