[Question] Elegant way to get total count and limited collection

[Deliaz]

Hi, I have a question, is there any elegant method to get a total count of items and limited collection? I use lazy load and show 25 items per page/scroll (using limit+offset), but also want to know what is the total size of a table.

Right now I am doing:

const collection = this.table
	.orderBy(orderBy)
	.filter(filterFav)
	.filter(filterSearch)
	.reverse();

const countPromise = collection
	.count();

const itemsPromise = collection
	.offset(page * ITEMS_PER_PAGE)
	.limit(ITEMS_PER_PAGE)
	.toArray();

return Promise.all([itemsPromise, countPromise]);

But feels like it can be more optimized. Any suggestions?

[dfahlander]

There isn't today, but I have started writing a paging middleware that I want to introduce in a later version of Dexie. But as of now, one has to be innovative here.

One problem with the query you posted is that it will do two full index scans where only one would be needed. Also, since it applies filter() and offset() on the collection, Dexie cannot use IDBIndex.getAll() but needs to query rows one by one (due to limitations in IndexedDB API). This can become particuarly slow in Angular applications due to Zone.js intercepting all indexedDB callbacks (don't know if that is your case though. But also for standard environments, getAll() is way faster than IDBCursor iterations).

What you could do to optimize this query, would be to:

1. Skip the count() call and do it manually. Skipping count() will change from 2 index scans to a single index scan.
2. Skip offset() and filter() calls and set a hard limit of, say 10000 (a limit that should protect from memory exhaust). If the unfiltered result is below 10000, just apply the filters on the resulting array instead. Else, let dexie filter the rows after 10000 and apply them as well.

These two steps would make the query extremely much faster if the index contains fewer than 10000 records, but also double speed than current, if it contains more than that.

const HARD_LIMIT = 10000;

class YourService {
  ...
  async loadPage(filterFav, filterSearch, page, bReverse) {
    const allItems = await this.table
      .orderBy(orderBy)
      .limit(HARD_LIMIT)
      .toArray(); // Will utilize IDBIndex.getAll()
    let filtered = allItems
      .filter(filterFav)
      .filter(filterSearch); // Array.filter()
    if (bReverse) filtered = filtered.reverse(); // Array.reverse()
    if (allItems.length === HARD_LIMIT) {
      // We didn't get all data in first try.
      // Need to continue filtering one by one:
      const rest = await this.table
        .orderBy(orderBy)
        .offset(HARD_LIMIT)
        .filter(filterFav)
        .filter(filterSearch)
        .toArray();

      if (bReverse) {
        filtered = rest.reverse().concat(filtered);
      } else {
        filtered = filtered.concat(rest);
      }
    }
    const start = page * ITEMS_PER_PAGE;
    const end = start + ITEMS_PER_PAGE;
    const result = filtered.slice(start, end);
    return [
      result,
      filtered.length
    ];
  }
}

This solution is NOT more elegant though, but more optimized.

A more elegant solution is on the way, but cannot promise when the paging support will be introduced. It is a tricky thing, but yet possible using IDBCursor.continuePrimaryKey() to load pages in chunks using getAll() wherever possible. This is what the new middleware will do under the hood.

[Deliaz]

@dfahlander Thank you for the better approach. I will test this idea in my project, seems it suits me well.

[oleersoy]

I've also built a count() feature into Slice. You could drop your entities into a store or slice and call count() on it. This is what the implementation looks like:

  /**
   * Returns the number of entries contained.
   * @param p The predicate to apply in order to filter the count
   */
  count(p?: Predicate<E>): Observable<number> {
    if (p) {
      return this.notifyEntryCount.pipe(
        map((e: E[]) => e.reduce((total, e) => total + (p(e) ? 1 : 0), 0))
      );
    }
    return this.notifyEntryCount.pipe(map((entries: E[]) => entries.length));
  }

I'm using Slice as the real time application store (Instead of something like Akita, NGRX, Redux, etc.) and using Dexie as the persistance manager for all entities in the store.

[jayarjo]

@dfahlander is paging middleware anywhere near completion? Not attempting to apply any force here, just asking :)

[dfahlander]

The code for it was dry-coded in the branch dexie-core where built the middleware architecture DBCore that is now part of 3.x-alpha. But in that branch I also added a paging layer above the DBCore layer. This is not yet incorporated into Dexie. If someone would be keen to know how the paging-engine works without having to use IDBCursor but keep using the more optimal getAllKeys() and getAll(), the code for it can be found here. Notice the layer L3-keyrange-paging that should implement paging.

I haven't got as much time this year for doing those larger works on dexie as I've been into an intensive period of consultancy. But my heart is still on this project and for a next generation of it. I don't know for sure if the paging-support will make it into 3.0 or be part of the next major version, or if someone wants to deep dive and generate a PR based on the code in here.

[mikhail-yahorau]

Hi @dfahlander! Is there any update on paging middleware? Is there any source other than you posted above to check the status/progress? Best regards!

[dfahlander]

Unfortunately, paging support is not implemented, but instead of a new paging API, the idea is that the cache will assist in optimizing simple offset().limit() requests for paging. It's in the road map for 5.0 without any date set https://dexie.org/roadmap/dexie5.0#improved-paging