Get Price highest to lowest with pagination query

[jovial stratus]

I am currently designing a filter which can take in " sorted by " and apply it to a convex query, there are 4 options,

Sorted by Newest (newest to oldest)
Oldest (oldest to newest)
Price (highest to lowest)
Price (lowest to highest)

There are also additional filters such as categoryId of the products and min / max prices

The paginated query looks like this:

export const getPostList = query({
  args: {
    paginationOpts: paginationOptsValidator,
    categoryId: v.optional(v.id("categories")),
    title: v.optional(v.string()),
    sortedBy: v.string(),
    min: v.optional(v.number()),
    max: v.optional(v.number()),
  },
  handler: async (ctx, args) => {
    var res = ctx.db.query("post");

    if (args.categoryId) {
      var res = res.filter((q) => q.eq(q.field("categoryId"), args.categoryId));
    }

    if (args.title) {
      var res = res.filter((q) => q.eq(q.field("title"), args.title));
    }

    if (args.min) {
      var res = res.filter((q) => q.gte(q.field("pricing"), args.min!));
    }

    if (args.max) {
      var res = res.filter((q) => q.lte(q.field("pricing"), args.max!));
    }

    if (args.sortedBy === "oldest") {
      return await res.order("asc").paginate(args.paginationOpts);
    } else if (args.sortedBy === "desc") {
      const x = await res.paginate(args.paginationOpts);
      const ret = x.page.sort((a, b) => b.pricing - a.pricing);
      return x;
    } else if (args.sortedBy === "asc") {
      const x = await res.paginate(args.paginationOpts);
      const ret = x.page.sort((a, b) => a.pricing - b.pricing);
      return x;
    } else {
      return await res.order("desc").paginate(args.paginationOpts);
    }
  },
});```

"desc" meaning from Price highest to lowest 
"asc" meaning from Price lowest to highest

However this does not provide me with the intended result as it only sorts after the pagination. I have tried using withIndex("by_pricing") but it gives an error saying .withIndex is not a function. Please help! Thanks

[polar scroll]

withIndex needs to go before the filters

[jovial stratus]

with this updated code, i no longer get it sorted by price, instead it sorts by creationTime

export const getPostList = query({
  args: {
    paginationOpts: paginationOptsValidator,
    categoryId: v.optional(v.id("categories")),
    title: v.optional(v.string()),
    sortedBy: v.string(),
    min: v.optional(v.number()),
    max: v.optional(v.number()),
  },
  handler: async (ctx, args) => {
    var res = ctx.db.query("post");

    if (args.sortedBy === "asc" || args.sortedBy === "desc") {
      res.withIndex("by_price");
    }

    if (args.categoryId) {
      var res = res.filter((q) => q.eq(q.field("categoryId"), args.categoryId));
    }

    if (args.title) {
      var res = res.filter((q) => q.eq(q.field("title"), args.title));
    }

    if (args.min) {
      var res = res.filter((q) => q.gte(q.field("pricing"), args.min!));
    }

    if (args.max) {
      var res = res.filter((q) => q.lte(q.field("pricing"), args.max!));
    }

    if (args.sortedBy === "oldest") {
      return await res.order("asc").paginate(args.paginationOpts);
    } else if (args.sortedBy === "desc") {
      const x = await res.order("desc").paginate(args.paginationOpts);
      return x;
    } else if (args.sortedBy === "asc") {
      const x = await res.order("asc").paginate(args.paginationOpts);
      return x;
    } else {
      return await res.order("desc").paginate(args.paginationOpts);
    }
  },
});

[polar scroll]

change res.withIndex("by_price"); into res = res.withIndex("by_price");

[jovial stratus]

It gives an error under res saying

Type 'Query<{ document: { _id: Id<"post">; _creationTime: number; title: string; src: string; description: string; location: string; pricing: number; categoryId: Id<"categories">; userId: Id<"users">; likes: number; interactions: number; }; fieldPaths: ExtractFieldPaths<...> | "_id"; indexes: { ...; }; searchIndexes: {}; ...' is missing the following properties from type 'QueryInitializer<{ document: { _id: Id<"post">; _creationTime: number; title: string; src: string; description: string; location: string; pricing: number; categoryId: Id<"categories">; userId: Id<"users">; likes: number; interactions: number; }; fieldPaths: ExtractFieldPaths<...> | "_id"; indexes: { ...; }; searchIn...': fullTableScan, withIndex, withSearchIndexts(2739)

[polar scroll]

ok yeah the types aren't right. i would do

var res = ctx.db.query("post").withIndex((args.sortedBy === "asc" || args.sortedBy === "desc") ? "by_price" : "by_creation_time");

[jovial stratus]

got it, thank you so much ❤️

[polar scroll]

sweet! btw if you're interested in writing very complex filters, you can check out https://stack.convex.dev/complex-filters-in-convex

[jovial stratus]

I also require title to be part of the filter, problem is i am unable to use withSearchindex to do the fuzzy search on all documents with similar titles, currently i am using

    if (args.title) {
      var res = res.filter((q) => q.eq(q.field("title"), args.title));
    }```

However, this only works if the title is EXACTLY the name parameter i search for, how do i work around this?

[mossy ginkgo]

why do you can't use withSearchIndex?

[frail valley]

e.g. searching all posts by title, then manually sorting those by price? presumably the title results wouldn't be so many that they'd need to paginate on price?

[jovial stratus]

im trying to combine all the filters into one function

i cant use searchIndex together with withIndex

[jovial stratus]

for example if the user types Property, i want to find every post with property inside the title along in the paginated filter query above

[mossy ginkgo]

Seems like it's coming soon