Parameterized Queries

[WonderPanda]

Currently, when building up UIs that are bound to TinyBase we'll often create individual query definitions for individual items to grab the data we need. For example:

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

This ends up being set up in a loop for each itemId in a list. It would be really nice if instead we had some way to set up the query definition once but then invoke it with the itemId that we care about. @jamesgpearce you had mentioned you've done some prior thinking about this so I was wondering if you had any potential APIs in mind. If not, I can definitely propose some from the perspective of a user consuming this functionality

[ghost]

It would be nice if the queries automatically updated whenever the parameters change, as I'm currently doing something like this:

store.addValueListener(
  "activeObjectId",
  updateQueryDefinition
);

function updateQueryDefinition() {
  queries.setQueryDefinition(
    "activeAttributeValueIds",
    "attributeValue",
    ({ select, where }: { select: any; where: any }) => {
      select("attributeUuid");
      const activeObjectId = store.getValue("activeObjectId");
      where("objectId", activeObjectId);
    }
  );
}

[Alex-ray]

Parameterized queries is incredibly common and I haven't been able to create a good pattern around this. For the sake of feedback here is what I'm currently doing.

 const getNodePropertiesByNameQuery = (nodeId: string, name: string) => {
    const queryName = `node:${nodeId}.${name}.properties`;
    const nextQueries = queries.setQueryDefinition(
      queryName,
      "properties",
      ({ select, where }) => {
        select("id");
        select("nodeId");
        select("name");
        select("type");
        select("createdAt");
        select("updatedAt");
        select("order");
        select("string");
        select("number");
        select("boolean");

        where("name", name);
        where("nodeId", nodeId);
      }
    );

    return {
      name: queryName,
      queries: nextQueries,
    };
  };
  
  const getNodeProperty = (nodeId: string, name: string) => {
    const { name: queryName, queries: nextQueries } =
      getNodePropertiesByNameQuery(nodeId, name);

    const resultTable = nextQueries.getResultTable(queryName);

    const id = Object.keys(resultTable)[0];
    return resultTable[id] as Property;
  };

If there is something terribly wrong or if there is a better pattern here I would love to hear it!

[waynesbrain]

I am treating queries like I would treat an SQL VIEW [1] because if you look at the implementation, as soon as you (re-) define a query, it starts fetching rows and similar to an SQL view, you can't pass in new params when calling it.

For an SQL view I would avoid redefining it while users were selecting from it.

Here in #180 in the doc_store.ts code where I create queries =, you can see that I don't use the built-in query methods. Instead I use the index and relationship facilities to query the tables. In that code, if I wanted a base view (to do joins), then I would use a view created with setQueryDefinition.

[1] Also mentioned here

[dylmye]

This is without a doubt the most frustrating aspect of using Tinybase and unfortunately makes me regret investing in this solution.