时间戳

首页 > 解决方案 > SQL 排行榜问题 nodejs discord.js

javascript - SQL 排行榜问题 nodejs discord.js

问题描述

我创建了一个简单的不和谐机器人，其中包含消息侦听器等，它在某些事件上将数据添加到 MySQL 数据库中。

我的代码添加 +1 点以对进入频道的消息进行评分（完全工作）：

//If message is sent add points to scores table

    con.query(`SELECT * FROM scores WHERE user="${message.author.id}" AND guild= "${message.guild.id}"`, (err, rows) => {
        if (err) throw err;

        let sql;
        if (rows.length < 1) {
            sql = `INSERT INTO scores (user, guild, points) VALUES ('${message.author.id}', '${message.guild.id}', '1')`, (err, rows) => {
                if (err) throw err;
            };

        } else {
            let score = rows[0].scores;
            sql = `UPDATE scores SET points = points +1 WHERE user="${message.author.id}" AND guild= "${message.guild.id}"`;
        };

        con.query(sql);

    });

一旦排行榜命令进入频道；我想获取分数表中的数据，将其分类到前 10 名并以不和谐的方式打印（通过使用 message.channel.send 或 message.channel.send(embed)。

下面的代码是我到目前为止所拥有的：

const Discord = require("discord.js");

module.exports.run = async (bot, message, args, con) => {
    
const top10 = `SELECT user, points, lstmsg FROM scores WHERE guild = '${message.guild.id}' ORDER BY cast(points as SIGNED) ASC LIMIT 10`;
//user, points, lstmsg from scores WHERE guild= '${message.guild.id}' ORDER BY points DESC LIMIT 10`;

con.query(top10, function(err, rows, fields) {
  if (err) throw err;

  //    const embed = new Discord.RichEmbed()
  //   .setTitle("Leaderboard")
  //   .setAuthor(bot.user.username,)
  //   .setDescription("Inactive..... Boooooo!")
  //   .addField(rows[1])
  //   .setColor(0x00AE86)


return message.channel.send(rows);

});


}
module.exports.help = {
    name: "top10",
    usage: "``prefix`` top10",
    description: "top 10 points",
}

但它不和谐地发送以下内容：

[object Object]
[object Object]
[object Object]
[object Object]
[object Object]
[object Object]
[object Object]
[object Object]
[object Object]
[object Object]

有任何想法吗？提前致谢

使用 JSON.Stringify 会返回如下结果：

[{"user":"123456789876587659","points":"2","lstmsg":"2019-03-04T22:40:40.000Z"},{"user":"378976806730203147","points":"80","lstmsg":"2019-03-05T10:10:18.000Z"},{"user":"123456789101112131","points":"93","lstmsg":"2019-03-04T22:40:29.000Z"},{"user":"378976806730203147","points":"168","lstmsg":"2019-03-05T10:10:18.000Z"},{"user":"456567676767867677","points":"289","lstmsg":"2019-03-04T22:41:01.000Z"}]

我希望数据显示为：

User:
Points:
Last Message:

对于数据库中的每个条目（限制为 10 个）。

我还尝试了以下资源：SQLite-Based Points system

代码：

const Discord = require("discord.js");

module.exports.run = async (bot, message, args, con) => {
  {
    var top10query = "SELECT * FROM scores WHERE guild = ? ORDER BY points DESC LIMIT 10"
    const top10 = con.query(top10query)

    // Now shake it and show it! (as a nice embed, too!)
    const embed = new Discord.RichEmbed()
      .setTitle("Leaderboard")
      .setAuthor(bot.user.username, bot.user.avatarURL)
      .setDescription("Our top 10 points leaders!")
      .setColor(0x00AE86);

    for (const data of top10) {
      embed.addField(bot.users.get(data.user).tag, `${data.points} points (last message ${data.lstmsg})`);
    }
    return message.channel.send({ embed });
  }





}
module.exports.help = {
  name: "inactive",
  usage: "``prefix`` inactive",
  description: "Bottom 30 inactive & last message date for current weeks",
}

但我收到一个错误（很可能是因为该示例适用于 SQLlite 并适用于 MySQL）：

(node:2880) UnhandledPromiseRejectionWarning: TypeError: top10 is not iterable

标签： javascriptmysqlnode.jsdiscord.jsleaderboard

解决方案

我建议，如果还没有的话，那user是guild分数表的复合主键（或唯一键）。这可以在 SQL 中通过以下方式完成：

ALTER TABLE scores DROP PRIMARY KEY, ADD PRIMARY KEY (user, guide)

对于您的 SQL 语句，INSERT ON DUPLICATE KEY UPDATE是一个常见的查询。它依赖于上面的唯一键或主键。

INSERT INTO scores (user, guild, points) VALUES ('${message.author.id}', '${message.guild.id}', '1')
ON DUPLICATE KEY UPDATE points=points+1

另请参阅防止 Node.js 中的 SQL 注入

推荐阅读