✅ – 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>•3mo 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>•3mo 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•3mo 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