Discord.js get rank position of user

Question

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}`);

    }

I want it to store the number of row matching the selected conditions (aka the number of user with less xp than the client) — Theoph, Oct 06 '21 at 22:50

score 0 · Answer 1 · answered Oct 06 '21 at 23:47

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;

    };

Discord.js get rank position of user

1 Answers1