maros.studenic•5mo ago

Hey I wanted to ask, is there a way to

Hey I wanted to ask, is there a way to specify by which field you want to sort collection with index ? for example I have list of companies that have year revenue, and want to filter them based on the revenue, but I want to sort them based on creation date. for example

export const getCompanies = query({
    args: {
        minRevenue: v.number(),
        maxRevenue: v.number()
    },
    handler: async (ctx, args) => {
        const companies = await ctx.db.query("companies").withIndex('revenue', (q) => q.and(q.lte(q.field("revenue"), args.maxRevenue), q.gte(q.field("revenue"), args.minRevenue)),order('desc');
        return companies;
    }
});

export const getCompanies = query({
    args: {
        minRevenue: v.number(),
        maxRevenue: v.number()
    },
    handler: async (ctx, args) => {
        const companies = await ctx.db.query("companies").withIndex('revenue', (q) => q.and(q.lte(q.field("revenue"), args.maxRevenue), q.gte(q.field("revenue"), args.minRevenue)),order('desc');
        return companies;
    }
});

Because now I understand that is going to be sorted by revenue, because its first field in index.

12 Replies

Hmza•5mo ago

did you tested and confirmed it is sorting by index and not _creationTime by default ? https://docs.convex.dev/database/reading-data#ordering

Reading Data | Convex Developer Hub

Query and

Hmza•5mo ago

sorry i didn't read it before. yes it is doing by index i suppose you'll have to use javascript to sort. which is also mentioned in the docs.

maros.studenicOP•5mo ago

okay, so its not supported by default on the query. Because parsing by js its probably slower. Yeah I tried, its by first index.

Hmza•5mo ago

best way i can think of is using something like "compound index" this will help https://stack.convex.dev/databases-are-spreadsheets

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...

Hmza•5mo ago

reason i'm attaching this doc because i had somewhat similar question before so i found this. i'm not sure if convex directly supports compounds though.

Hmza•5mo ago

if you're worried about data being too large to sort (hence slower) i'd suggest paginating it then. https://docs.convex.dev/database/pagination

Paginated Queries | Convex Developer Hub

Load paginated queries.

Hmza•5mo ago

hope this helps your cause somewhat!

maros.studenicOP•5mo ago

Thanks for the article. I think that if I also want a pagination by creation date, then it would need a way to sort the data to paginate them.

Hmza•5mo ago

Yeah i said pagination so that you only sort the page that is being pulled

maros.studenicOP•5mo ago

That would break the logic. On the first page there would be company from today till last year. And in the second page there could be also company from today till last year, because it paginates them based on the revenue.

lee•5mo ago

You're right this is difficult: combining a >= filter, ordering on a different field, and pagination. https://stack.convex.dev/databases-are-spreadsheets Some ideas: - store a "revenue band" field which encompasses the revenue >= and <= filters. So the index filter can be a .eq - use the creation time index and post-filter by revenue. To paginate this you can use https://stack.convex.dev/complex-filters-in-convex - for each possible revenue in the range (which you can fetch with https://stack.convex.dev/select-distinct ), or for each possible revenue band if you make small revenue bands as a separate field, paginate by creation time and merge the pages (with a helper like https://stack.convex.dev/pagination ) Sorry for the link dump. This is a pretty difficult question in any database. I think in a sql database a query planner would probably do the post-filter thing, which is fairly easy and will work but can be slow.

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...

Using TypeScript to Write Complex Query Filters

There’s a new Convex helper to perform generic TypeScript filters, with the same performance as built-in Convex filters, and unlimited potential.

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...

Take Control of Pagination

Convex offers robust control over pagination with a powerful function, getPage, enabling complex edge cases. In this article, we go over how to use th...

maros.studenicOP•5mo ago

Oo thanks really nice idea.

Hey I wanted to ask, is there a way to

Did you find this page helpful?