0
\$\begingroup\$

I need to assign a number to each userId. I think this is too much of a query for updating. Is it possible to reduce the number of queries, or is it okay?

def newRanking(groupId: String) = {
    DB.withConnection { implicit con =>
      var rank: List[Int] = util.Random.shuffle(List(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50))
      var i = 0
      val sql = SQL("SELECT userId FROM `groupInformation` WHERE groupId={groupId} limit 50").on("groupId" -> groupId)
      sql().map { row =>
        SQL("update groupInformation set rank={rank} where groupId={groupId} and userId={userId}").on("groupId" -> groupId, "rank" -> rank(i), "username" -> row[String]("userId")).executeUpdate()
        i += 1
      }.toList
    }
  }
\$\endgroup\$
  • 1
    \$\begingroup\$ You already asked essentially the same question about putting SQL queries in a loop here: codereview.stackexchange.com/questions/54783/… \$\endgroup\$ – Gangstead Jun 25 '14 at 14:31
  • \$\begingroup\$ @Gangstead you can see they are not same \$\endgroup\$ – Govind Singh Nagarkoti Jun 25 '14 at 14:33
  • \$\begingroup\$ The queries are different, but the root misunderstanding of SQL is the same. Get the SQL working in one query in a SQL client, then the Scala will be straight forward. \$\endgroup\$ – Gangstead Jun 25 '14 at 14:37
3
\$\begingroup\$

As I'm sure you've noticed, executing any SQL query in a loop is a bad idea. It would be much better to formulate a statement that accomplishes the entire batch of changes at once.

To achieve that goal, you'll need to combine several ideas:

  • Shuffling using ORDER BY RAND(). Admittedly, the documentation admits:

    RAND() is not meant to be a perfect random generator. It is a fast way to generate random numbers on demand that is portable between platforms for the same MySQL version."

    So, you'll have to decide whether MySQL's idea of randomness is as good as Scala's.

  • A row number generator. Unfortunately, MySQL has no row_number() or rank() function, like some other databases, so you'll need to use a hack.
  • UPDATE using multiple table references.

A statement that you could use is:

UPDATE `groupInformation`
    INNER JOIN (
            SELECT *
                FROM `groupInformation`
                ORDER BY RAND() LIMIT 50
        ) AS shuffled
            ON `groupInformation`.userId = shuffled.userId
    INNER JOIN (
            SELECT @row := 0
        ) AS row_var_decl
    SET `groupInformation`.rank = @row := @row + 1
    WHERE `groupInformation`.groupId = {groupId};

Here's an SQL Fiddle as a demonstration.


As you've noted, the behaviour of the query above is different from the original when there are fewer than 50 matching rows in groupInformation.

\$\endgroup\$

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.