rochel3•7mo ago

Get Price highest to lowest with pagination query

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);
    }
  },
});

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

14 Replies

lee•7mo ago

withIndex needs to go before the filters

rochel3OP•7mo ago

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);
    }
  },
});

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);
    }
  },
});

lee•7mo ago

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

rochel3OP•7mo ago

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)

lee•7mo ago

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");

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

rochel3OP•7mo ago

got it, thank you so much ❤️

lee•7mo ago

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

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.

rochel3OP•7mo ago

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));
    }

    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?

FleetAdmiralJakob 🗕 🗗 🗙•7mo ago

why do you can't use withSearchIndex?

ian•7mo ago

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?

rochel3OP•7mo ago

im trying to combine all the filters into one function i cant use searchIndex together with withIndex 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

FleetAdmiralJakob 🗕 🗗 🗙•7mo ago

FleetAdmiralJakob 🗕 🗗 🗙•7mo ago

Seems like it's coming soon

lee•7mo ago

searchIndex can only return items sorted by relevance, so it doesn't sound like what you want. I would use the withIndex and implement fuzzy search in a custom filter (https://stack.convex.dev/complex-filters-in-convex)

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.

Get Price highest to lowest with pagination query

Did you find this page helpful?