<Alterion.Dev>
<Alterion.Dev>2mo ago

✅ – Can anyone help with better-sqlite3's `aggregate` method to read more than one row?

Can anyone help with better-sqlite3's aggregate method to read more than one row?
Solution:
I'm using better-sqlite3 and I want to use its aggregate method but I need to have access to 2 rows, not just 1, in the aggregate step function. This is the code that works ```js filter(predicate) { this.#db.aggregate('filter', { start: [], step: (accumulator, currentValue) => {...
Jump to solution
3 Replies
Solution
<Alterion.Dev>
<Alterion.Dev>2mo ago
I'm using better-sqlite3 and I want to use its aggregate method but I need to have access to 2 rows, not just 1, in the aggregate step function. This is the code that works
filter(predicate) {
this.#db.aggregate('filter', {
start: [],
step: (accumulator, currentValue) => {
const parsed = JSON.parse(currentValue);
if (predicate(parsed)) {
accumulator.push(parsed);
}
return accumulator;
},
result: (accumulator) => JSON.stringify(accumulator),
});
const results = this.#db
.prepare(`SELECT filter(value) FROM mytable`)
.pluck()
.get();
return JSON.parse(results);
}
filter(predicate) {
this.#db.aggregate('filter', {
start: [],
step: (accumulator, currentValue) => {
const parsed = JSON.parse(currentValue);
if (predicate(parsed)) {
accumulator.push(parsed);
}
return accumulator;
},
result: (accumulator) => JSON.stringify(accumulator),
});
const results = this.#db
.prepare(`SELECT filter(value) FROM mytable`)
.pluck()
.get();
return JSON.parse(results);
}
But I need too add a second row, key. I don't know how. I tried to do .prepare('SELECT filter(key, value) FROM mytable') but that tells me wrong number of arguments to function filter(). I tried SELECT filter(key), filter(value) from mytable and that runs the function twice, not once, with the key then the value and that's obviously not what I want. Any idea on how I can get both values at once in the step ?
<Alterion.Dev>
<Alterion.Dev>2mo ago
Got an answer from the better-sqlite3 author, so I'll stick it here for posterity, just in case anyone else ever searches archives! This is for another method but it's simpler, so easier to look at:
reduce(predicate, initialValue) {
this.#db.aggregate('reduce', {
start: initialValue,
step: (accumulator, currentValue, key) =>
predicate(accumulator, this.#parse(currentValue), key),
});
return this.#db
.prepare(`SELECT reduce(value, key) FROM ${this.#name}`)
.pluck()
.get();
}
reduce(predicate, initialValue) {
this.#db.aggregate('reduce', {
start: initialValue,
step: (accumulator, currentValue, key) =>
predicate(accumulator, this.#parse(currentValue), key),
});
return this.#db
.prepare(`SELECT reduce(value, key) FROM ${this.#name}`)
.pluck()
.get();
}
There we go 🙂 Hope it helps someone else in the future.
reactibot
reactibot2mo ago
This question has an answer! Thank you for helping 😄 If you have a followup question, you may want to reply to this thread so other members know they're related. https://discord.com/channels/102860784329052160/565213527673929729/1226362915372929074