Listing a table sorted by a certain column from database: node.js

Question

Alright, so I've been stuck on this for awhile and I am not getting any closer.

  run(msg, { text }) {
    let sql = 'SELECT username, melo, lelo, celo FROM users';
    var count = 0;
    db.query(sql, (err, rows) => {
      var users = [];
      if (text == 'mock') {
        for (var i = 0; i < rows.length; ++i) {
          count++;
          users.push([count + ') '], [rows[i].username + ': '], [rows[i].melo]);
        }
        var arr = users.sort((a, b) => {
          return b[2] - a[2];
        });
        console.log(users);
        // console.log(arr);
        msg.channel.send(arr);
      }
    });
  }

I can get it list. But I can;t get it to sort, well... anything. I've done it several different ways, but to no avail. This is the last method I tried, which only lists everything, like a normal array. However I'd like it to list:

1) name highernumber
2) name lowernumber
3) name evenlowernumber

If it helps, think of like an RPG leader board.

`[ [ '1) ' ], [ 'eNForcer: ' ], [ 100 ], [ '2) ' ], [ 'Bloodmorphed: ' ], [ 101 ] ] ` Just realized the count++ would be inaccurate during that anyways. — William, Feb 05 '18 at 11:02
This `[ [ '1) ' ], [ 'eNForcer: ' ], [ 100 ], [ '2) ' ], [ 'Bloodmorphed: ' ], [ 101 ] ]` seems not clear — yue you, Feb 05 '18 at 11:04
see this: https://stackoverflow.com/questions/16096872/how-to-sort-2-dimensional-array-by-column-value — aswzen, Feb 05 '18 at 11:06
Yup, read that already. I don't want it to sort twice. I only need it sort by `melo`. That is where I got the sorting from as well: `var arr....` etc. Hmm... some of the other answers will work though. And itseems I'm nt pushing the data right either. — William, Feb 05 '18 at 11:27

Akash Dathan · Accepted Answer · 2018-02-05T13:41:55.717

1

First of all, the index should be assigned after the array has been sorted, unless that is the requirement.

I believe you are trying to create an array of arrays and sort the top level array thus the return b[2] - a[2].

This wouldn't work, because

users.push([count + ') '], [rows[i].username + ': '], [rows[i].melo])

does not create a nested array, it just adds multiple elements to the same array. What you need is

users.push([count + ') ', rows[i].username + ': ', rows[i].melo])

But even in this, the count won't be in sorted order because it has been assigned before sorting. The following code will give you the required results.

run(msg, { text }) {
  let sql = 'SELECT username, melo, lelo, celo FROM users';
  var count = 0;
  db.query(sql, (err, rows) => {
    var users = [];
    if (text == 'mock') {
      for (var i = 0; i < rows.length; ++i) {
        users.push([rows[i].username + ': ', Number(rows[i].melo)]);
      }
      var arr = users.sort((a, b) => {
        return b[1] - a[1];
      });

      for (var i = 0; i < arr.length; ++i) {
        count += 1
        arr[i].unshift(count + ') ')
        arr[i] = arr[i].join()
      }
      console.log(users);
      // console.log(arr);
      msg.channel.send(arr);
    }
  });
}

edited Feb 05 '18 at 13:41

answered Feb 05 '18 at 11:55

Akash Dathan

4,348
2
24
45

I get `arr[i].unshift(...).join` is not a function. – William Feb 05 '18 at 13:26
Okay I removed the `.join` and it seems to... work? I just get 2 numbers now... also not even the right numbers. it produces `[3, 3]` – William Feb 05 '18 at 13:33
Can u give the output you are getting ? – Akash Dathan Feb 05 '18 at 13:35
That is the output I'm getting `[3, 3]` the `console.log('arr')` gives 2 3's. `console.log(users)` before the sort array gives `[ [ 'eNForcer: ', 100 ], [ 'Bloodmorphed: ', 101 ] ]` Okay so the problem is the loop adding the numbers to the names. That is where things are messing up. – William Feb 05 '18 at 13:38
`arr[i].join is not a function` – William Feb 05 '18 at 13:47
Nevermind, I made a mistake. But now I have a, well... ugly problem. In discords the message prints out: `1) ,Bloodmorphed: ,101 2) ,eNForcer: ,100`. All those commas, pretty ugly. Oh, I fixed it: just did `.join('');` – William Feb 05 '18 at 13:51
That’s because the default saperator of join is comma. try using ‘join(‘’)’ – Akash Dathan Feb 05 '18 at 13:53
Yup got it right as you answered :P.. Anyways, thanks very much for the help. I was pulling my hair out :O. – William Feb 05 '18 at 13:54
np, happy to help :-) – Akash Dathan Feb 05 '18 at 13:55
For the record, if you want to print it out in a embed: `value: arr.toString().replace(',', '\n');` would print it out like abve, but in an embed instead... I can give the code if youwant, so you can put it as an example in the answer, as well. – William Feb 06 '18 at 11:06

Listing a table sorted by a certain column from database: node.js

1 Answers1