I want to get the number of users that have a lower number of XP points than the member who used the command, this way I can get his rank.
However I don't know much in javascript and sql queries, and I'm hard stuck with this, where it simply returns [object Object] instead of a number.
My sql table
const table = sql.prepare("SELECT count(*) FROM sqlite_master WHERE type='table' AND name = 'scores';").get();
if (!table['count(*)']) {
// If the table isn't there, create it and setup the database correctly.
sql.prepare("CREATE TABLE scores (id TEXT PRIMARY KEY, user TEXT, guild TEXT, points INTEGER, level INTEGER, money INTEGER);").run();
// Ensure that the "id" row is always unique and indexed.
sql.prepare("CREATE UNIQUE INDEX idx_scores_id ON scores (id);").run();
sql.pragma("synchronous = 1");
sql.pragma("journal_mode = wal");
}
// And then we have two prepared statements to get and set the score data.
client.getScore = sql.prepare("SELECT * FROM scores WHERE user = ? AND guild = ?");
client.setScore = sql.prepare("INSERT OR REPLACE INTO scores (id, user, guild, points, level, money) VALUES (@id, @user, @guild, @points, @level, @money);");
});
My attempt
if (message.content.startsWith(prefix + "cl stats")) {
const curxp = score.points;
client.rank = sql.prepare("SELECT count(*) FROM scores WHERE points >= @curxp AND guild = @guild").get();
console.log(client.rank);
await message.reply(`${client.rank}`);
}
Found a solution. Probably not the best but it works.
client.getRank = sql.prepare("SELECT count(*) FROM scores WHERE points >= ? AND guild = ?");
function getRank(){
const curXP = score.points;
let rankOBJ = client.getRank.get(curXP, message.guild.id)
console.log(rankOBJ);
let rankStr = JSON.stringify(rankOBJ);
let rank = rankStr.match(/\d/g);
return rank;
};