search

canvasxyz / canvas

Programmable runtime for peer-to-peer applications
https://canvas.xyz
73 stars 5 forks source link

add WhereCondition filters to count() in ModelDB #338

Open joeltg opened 3 days ago

joeltg commented 3 days ago

Right now, we have the following method signature in AbstractModelDB

abstract count(modelName: string): Promise<number>

It would be very useful (for ourselves) to be able to add filters to this, i.e. to count the number of rows satisfying a WhereCondition (the other query properties don't apply to count()).

abstract count(modelName: string, query?: { where?: WhereCondition }): Promise<number>

To do this, we need to handle this in all four ModelDB implementations: sqlite, sqlite-wasm, pg, and idb. The first three should be straightforward since they have their own query planners. The last (IndexedDB) is harder because we do our own "query planning" based on the available indexes.

To do this, we use the internal ModelAPI methods getIndex and queryIndex.

  1. If no where expression is provided, use the regular existing code (this.db.count(api.storeName) in ModelDB.ts)
  2. If a where expression is provided, and one of the properties of the where expression has an index in this.model.indexes, get the IDBStore for that index using getIndex, and pass it to queryIndex to get an AsyncIterable<ModelValue>. Before iterating, get a filter for the yielded values using const filter = getFilter(this.model, query.where), and count the ones that pass the filter.
  3. If no property of the where expression have an index, iterate over all values (using the primary property index) and count the values that pass the filter.
rjwebb commented 1 day ago

If a where expression is provided, and one of the properties of the where expression has an index in this.model.indexes, get the IDBStore for that index using getIndex, and pass it to queryIndex to get an AsyncIterable. Before iterating, get a filter for the yielded values using const filter = getFilter(this.model, query.where), and count the ones that pass the filter.

If there are multiple indexes that could be used, we could call .count on all of them and then iterate over the one that has the fewest items. We could use this optimisation for query as well...

joeltg commented 1 day ago

oooooo that would be amazing actually