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
- there's no count or aggregation, we need to query all results (
- 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?
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 (support-communityfilter index based on multiple possible values?)- 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?
