Filtered and paginated list
Hey! I'm building an ERP / invoicing system and thinking of using convex for this. Previous versions of this system used traditional SQL (for different client), so I'm trying to transfer my knowledge and understand the limitations of convex and what's the reason behind them. Maybe I'm overthinking this and it should be fine anyway with this amount of the data, but I want to at least understand the tradeoff I'm making even if it's fine at the moment.
Here's my use case in a nutshell: list of invoices (currently ~50k rows a year) with date, status and sum. (sum is currently calculated in a background job on every item change) Users can search for invoices based on date and status and want to see the number of invoices and the sum of them. (How much did we sell this week.) Maybe the answer is to not support showing the total on this view and we need a different page?
- you can filter for multiple statuses at the time - WHERE status IN (DRAFT, SAVED)
- filter for date like: between 2025-06-01 and 2025-06-14
- we need pagination (don't want to render potentially thousands of results)
- we want to show the number of results
- we want to aggregate the sum field and show the total value on first load even if we only load the first page
Issues and thought process:
- there's no IN operation in convex, we need to use
.filter to match multiple statuses instead of index (https://discord.com/channels/1019350475847499849/1332057416514273432)
- there's no count or aggregation, we need to query all results (collect()) and do the math? when does this get bad? tried to seed convex with some data and do some benchmarks and it looks fine
- Aggregate component only works on general things, doesn't work on dynamic queries
- since date filter is always in the query, it should be fine, but you can easily change the from date to go back and I don't want the app to die because of that
- should we have a separate query to get count and sum and the main query to get paginated results?2 Replies
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!
To get the total count it sounds like you need all of the results. You can either send all results to the client, keep results on the server but still fetch them, or skip the counting entirely.
Concretely, you can call loadMore from the client until you've fetched all pages, only render the first page, and count them to get the total. Or you could do a "manual pagination" loop from a server-side action.
If you want equivalents of the sql IN operator, you could use filter or you could use the streams interface to do the efficient thing https://stack.convex.dev/merging-streams-of-convex-data . This example and more are described here https://stack.convex.dev/translate-sql-into-convex-queries#union
Merging Streams of Convex data
New convex-helpers are available now for fetching streams of documents, merging them together, filtering them them out, and paginating the results. Wi...
Translate SQL into Convex Queries
Here’s a cheatsheet with examples of conversions between SQL queries and Convex queries. This article is geared towards developers (and LLMs) who have...