I have this standings array
{ pos: 1, competitor_id: 583, rating: 2049, tier: 1 },
{ pos: 2, competitor_id: 862, rating: 1818, tier: 1 },
{ pos: 3, competitor_id: 67, rating: 1762, tier: 1 },
{ pos: 4, competitor_id: 758, rating: 1750, tier: 1 },
{ pos: 5, competitor_id: 713, rating: 1735, tier: 1 },
{ pos: 6, competitor_id: 868, rating: 1730, tier: 1 },
{ pos: 7, competitor_id: 946, rating: 1706, tier: 2 },
{ pos: 8, competitor_id: 893, rating: 1705, tier: 2 },
{ pos: 9, competitor_id: 731, rating: 1695, tier: 2 },
{ pos: 10, competitor_id: 869, rating: 1692, tier: 2 },
{ pos: 11, competitor_id: 998, rating: 1672, tier: 2 },
{ pos: 12, competitor_id: 968, rating: 1661, tier: 2 },
{ pos: 13, competitor_id: 460, rating: 1656, tier: 2 },
{ pos: 14, competitor_id: 879, rating: 1648, tier: 2 },
{ pos: 15, competitor_id: 969, rating: 1631, tier: 2 },
{ pos: 16, competitor_id: 925, rating: 1629, tier: 2 },
{ pos: 17, competitor_id: 999, rating: 1626, tier: 2 },
{ pos: 18, competitor_id: 991, rating: 1619, tier: 2 },
{ pos: 19, competitor_id: 956, rating: 1618, tier: 2 },
{ pos: 20, competitor_id: 711, rating: 1617, tier: 2 },
{ pos: 21, competitor_id: 964, rating: 1616, tier: 2 },
I want to increment tier by one every time we move 6 rows down.
As you can see above it switches to tier 2 properly but fails to increment to 3 at pos:13 and to tier 4 at pos:19
I tried
const tiersize = 6;
// setting the counters
let tiercounter = 1;
let limit = tiersize;
// running the loop
for (const row of standings) {
await connectionPromise
.query({
sql: 'update table set tier = ? where competitor_id = ?'
}, [tiercounter, row.competitor_id], );
if (row.pos == limit) {
tiercounter += 1;
limit += tiersize;
}
}
What am I doing wrong?
You're updating the database, but you're not updating the array as well. Add row.tier = tiercounter; to the loop.
const tiersize = 6;
// setting the counters
let tiercounter = 1;
let limit = tiersize;
// running the loop
for (const row of standings) {
await connectionPromise
.query({
sql: 'update table set tier = ? where competitor_id = ?'
}, [tiercounter, row.competitor_id], );
row.tier = tiercounter;
if (row.pos == limit) {
tiercounter += 1;
limit += tiersize;
}
}
But rather than incrementing tiercounter as you go, you can simply calculate it from pos.
tiercounter = Math.floor((row.pos-1) / tiersize) + 1;
The updated code would then be:
const tiersize = 6;
// running the loop
for (const row of standings) {
let tiercounter = Math.floor((row.pos - 1) / tiersize) + 1;
await connectionPromise
.query({
sql: 'update table set tier = ? where competitor_id = ?'
}, [tiercounter, row.competitor_id], );
row.tier = tiercounter;
}
You were checking row.pos number with limit and the limit you were updating everytime. Which doesn't make sense with the condition. update the if condition as below.
for (const row of standings) {
await connectionPromise
.query({
sql: 'update table set tier = ? where competitor_id = ?'
}, [tiercounter, row.competitor_id], );
if (row.pos % tiersize == 0) {
tiercounter += 1;
limit += tiersize;
}
}