Ability to create Metrics on top of Indices

[WonderPanda]

Is your feature request related to a problem? Please describe. I'm working on a local app that conceptually has a list of items pertaining to a certain parent project. For simplicity, the schema could be:

{
  project: {
     id: string
  },
  item: {
    id: string,
    projectId: string,
    isComplete: boolean
  }
}

I'd like to be able to have a metric that allows me to calculate percentage calculation for each project based on its related items

Describe the solution you'd like It seems like it would make sense to be able to define a metric definition that targets a specific index instead of the overall table.

Describe alternatives you've considered We can definitely recreate the aggregate completion calculation using other TinyBase primitives but it feels like there's an opportunity for an elegant addition to the core API

Additional context Add any other context or screenshots about the feature request here.

[jamesgpearce]

I think this would be a perfect use case for the queries module. That lets you aggregate and filter more flexibly than the index and metrics modules.

[WonderPanda]

Thanks for the context! Just to be clear, are you suggesting that we could accomplish this today using the Queries module? Or that it would be the logical place for this feature to live if it were to be implemented?

I had contemplated opening up another issue about the possibility of having parameterized queries as well, not sure if that ties into your vision for how this might work. Also, do you have a preference for how you'd like to communicate through GitHub? I could definitely create these as Discussions instead of Issues

[jamesgpearce]

Yes it may do what you need today, but do feel free to open a discussion called 'parameterized queries'. I had thought quite a bit about that but didn't quite get there as expected.

[WonderPanda]

We've refactored to using queries instead of a metrics to accomplish our goals here. It definitely feels more natural and SQL-ish even though its a bit more code I think the intent reads better this way.

Before:

metrics.setMetricDefinition(
        `${COMPLETED_QUESTIONS_METRIC_PREFIX}_${itemId}`,
        "questions",
        "sum",
        (getCell) => {
          return getCell("item_id") === itemId && getCell("response") ? 1 : 0;
        }
      );

After:

queries.setQueryDefinition(
        `${COMPLETED_QUESTIONS_METRIC_PREFIX}_query_${itemId}`,
        "questions",
        ({ group, select, where }) => {
          select("item_id");
          where((getCell) => {
            return (
              getCell("item_id") === itemId && Boolean(getCell("response"))
            );
          });
          group("item_id", "count").as("count");
        }
      );

It does still seem like this forces us to iterate over every single row in the table in either case, so my original thought around allowing us to operate against indices for this kind of thing remains a curiosity. We're happy with the query approach over all though

[segments-tobias]

@WonderPanda How did you end up implementing parametrized queries if I may ask?