RJ•11mo ago

Filter by documents with fields which are equal to a set (>= 2) of values using an index?

Say I have a table that looks like this:

books: defineTable({
  author: v.string(),
  content: v.string(),
}).index("by_author", ["author"])

books: defineTable({
  author: v.string(),
  content: v.string(),
}).index("by_author", ["author"])

and I'd like to retrieve a collection of documents belonging to the authors "Stendhal" and "Alexandre Dumas". If I use .filter, I understand that this is achievable in one query:

db.query("books")
  .filter((q) =>
    q.or(
      q.eq(q.field("author"), "Stendhal"),
      q.eq(q.field("author"), "Alexandre Dumas"),
    )
  )
  .collect()

db.query("books")
  .filter((q) =>
    q.or(
      q.eq(q.field("author"), "Stendhal"),
      q.eq(q.field("author"), "Alexandre Dumas"),
    )
  )
  .collect()

but is it possible to achieve in one query using an index?

db.query("books")
  .withIndex(
    "by_author",
    (q) => q.eq("author", "Stendhal")
    // * No `q.or` available
    // * Further chaining with
    //   q.eq(...).eq(...) doesn't work,
    //   according to the types
    // * Neither may additional indexes be used
  )

db.query("books")
  .withIndex(
    "by_author",
    (q) => q.eq("author", "Stendhal")
    // * No `q.or` available
    // * Further chaining with
    //   q.eq(...).eq(...) doesn't work,
    //   according to the types
    // * Neither may additional indexes be used
  )

3 Replies

lee•11mo ago

nope this isn't possible, because an indexed query must read a contiguous range of the index. think of it like: if the documents were sorted by the index, you can start reading at any point and read the documents in order from there. so you can read all documents with author == "Stendhal" and all documents with "Alexandre" <= author <= "Stendhal" but you can't read just those authors because they're not contiguous. If you want to read just those authors, you can do two separate index queries, and fetch them in parallel with a Promise.all for speed. concretely, that would look like

const [books1, books2] = await Promise.all([
  db.query("books")
  .withIndex(
    "by_author",
    (q) => q.eq("author", "Stendhal")).collect(),
  db.query("books")
  .withIndex(
    "by_author",
    (q) => q.eq("author", "Alexandre")).collect()
]);

const [books1, books2] = await Promise.all([
  db.query("books")
  .withIndex(
    "by_author",
    (q) => q.eq("author", "Stendhal")).collect(),
  db.query("books")
  .withIndex(
    "by_author",
    (q) => q.eq("author", "Alexandre")).collect()
]);

RJOP•11mo ago

Gotcha, thank you @lee! It's not important to me at the moment, but I can imagine wanting to both 1) fetch my results using separate indexed queries (as you outline above), and 2) paginate the sum of these results. Do you have any thoughts on how/whether that would be possible?

lee•11mo ago

paginating the union is something we've played with. you can do it with two separate usePaginatedQuery hooks from the client, but if you want to do it with a single query it's not possible today. multiple paginate calls in a query is something we can potentially support in the future, but no timeline yet