zoomie•3w ago

Can't apply multiple bounds (filters) on an index

For the table I'm filtering, I don't know how many results I will get back. I don't want to use collect (in case it crashes) and if I use take, their is a chance all the data returned will be filtered out and the user won't see anything. I thought I'd add all the filter options into the index, but get an error.

// transaction.ts
    .withIndex("by_project_id_and_block_timestamp_and_value_quote", (q) =>
        q
          .eq("project_id", projectId)
          .gte("block_timestamp", startDate)
          .lte("block_timestamp", endDate)
          .gte("value_quote", minAmount)
      )

// schema.ts
.index("by_project_id_and_block_timestamp_and_value_quote", [
  "project_id",
  "block_timestamp",
   "value_quote",
])

// transaction.ts
    .withIndex("by_project_id_and_block_timestamp_and_value_quote", (q) =>
        q
          .eq("project_id", projectId)
          .gte("block_timestamp", startDate)
          .lte("block_timestamp", endDate)
          .gte("value_quote", minAmount)
      )

// schema.ts
.index("by_project_id_and_block_timestamp_and_value_quote", [
  "project_id",
  "block_timestamp",
   "value_quote",
])

Error: Property 'gte' does not exist on type 'IndexRange'. Is their a way to use an and? It looks like you can only apply one UpperBoundIndexRangeBuilder and LowerBoundIndexRangeBuilder

5 Replies

Convex Bot•3w ago

Thanks for posting in <#1088161997662724167>. Reminder: If you have a Convex Pro account, use the Convex Dashboard to file support tickets. - Provide context: What are you trying to achieve, what is the end-user interaction, what are you seeing? (full error message, command output, etc.) - Use search.convex.dev to search Docs, Stack, and Discord all at once. - Additionally, you can post your questions in the Convex Community's <#1228095053885476985> channel to receive a response from AI. - Avoid tagging staff unless specifically instructed. Thank you!

lee•3w ago

An index range is a contiguous range of documents when the documents are ordered by the index fields. Your query takes a range of block_timestamp and for each block_timestamp it filters by value_quote, which means it's not contiguous. See https://stack.convex.dev/databases-are-spreadsheets for more background. Depending on how much data you have, you could use an index for the block_timestamp range and then post-filter by value_quote

Databases are Spreadsheets

I want to share my mental model of databases: - Databases are just big spreadsheets - An index is just a view of the spreadsheet sorted by one or mor...

zoomieOP•3w ago

Thanks for the reply! Ok, so I need to find another way to filter the data. For now, it will be ok just to pull everything into memory, I am just worried about hitting the 16,384 limit, which seems likely. What I'll do is accumulate data in an action (node.js server) which I read somewhere seems to be the standard approach.

lee•3w ago

Do you intend to paginate the data or return all of the results after filtering?

lee•3w ago

If you want to return all the results, you can construct queries to get exactly what you want with this pattern https://stack.convex.dev/select-distinct

SELECT DISTINCT without SQL

You'll often want to retrieve unique records from your database. While SELECT DISTINCT is the traditional way to do this in SQL databases, we've impro...

Can't apply multiple bounds (filters) on an index

Did you find this page helpful?