Sort paginated items

I have this schema

export const participantsSchema = defineTable({
  sessionId: v.id("session"),
  searchable: v.optional(v.string()),
  userProfileId: v.optional(v.id("userProfiles")),
  invitedBy: v.optional(v.id("userProfiles")),
  role: sessionRoleEnumValidator
})
  .index("by_session_id", ["sessionId"])
  .index("by_session_role", ["sessionId", "role"])
  .index("by_user_profile_id", ["userProfileId"])
  .index("by_user_profile_role", ["userProfileId", "role"])
  .index("by_invite", ["invitedBy", "userProfileId"])
  .index("by_invite_role", ["invitedBy", "userProfileId", "role"])
  .index("by_session_and_user", ["sessionId", "userProfileId"])
  .index("by_searchable_field", ["searchable"])
  .searchIndex("by_searchable", {
    searchField: "searchable",
    filterFields: ["userProfileId", "sessionId", "role"],
  });

export const participantsSchema = defineTable({
  sessionId: v.id("session"),
  searchable: v.optional(v.string()),
  userProfileId: v.optional(v.id("userProfiles")),
  invitedBy: v.optional(v.id("userProfiles")),
  role: sessionRoleEnumValidator
})
  .index("by_session_id", ["sessionId"])
  .index("by_session_role", ["sessionId", "role"])
  .index("by_user_profile_id", ["userProfileId"])
  .index("by_user_profile_role", ["userProfileId", "role"])
  .index("by_invite", ["invitedBy", "userProfileId"])
  .index("by_invite_role", ["invitedBy", "userProfileId", "role"])
  .index("by_session_and_user", ["sessionId", "userProfileId"])
  .index("by_searchable_field", ["searchable"])
  .searchIndex("by_searchable", {
    searchField: "searchable",
    filterFields: ["userProfileId", "sessionId", "role"],
  });

I want to query like this but give the users ability to sort in ascending / descending order based on the searchable index.

const data = await ctx.db
        .query("participants")
        .withIndex("by_user_profile_role", (q) =>
          q.eq("userProfileId", userProfileId).eq("role", defaultRole)
        )
        .order("desc")
        .paginate(paginationOpts);

const data = await ctx.db
        .query("participants")
        .withIndex("by_user_profile_role", (q) =>
          q.eq("userProfileId", userProfileId).eq("role", defaultRole)
        )
        .order("desc")
        .paginate(paginationOpts);

When I replace:

.withIndex("by_user_profile_role", (q) =>
          q.eq("userProfileId", userProfileId).eq("role", defaultRole)
        )

.withIndex("by_user_profile_role", (q) =>
          q.eq("userProfileId", userProfileId).eq("role", defaultRole)
        )

with:

.withIndex("by_searchable")

.withIndex("by_searchable")

sort works as expected but I can't use multiple index, I need to use the existing index while allowing users to sort based on the searchable field

1 Reply

ampp•8mo ago

I ran into this too: Search queries always return results in relevance order based on how well the document matches the search query. Different ordering of results are not supported. https://docs.convex.dev/text-search#ordering

Full Text Search | Convex Developer Hub

Run search queries over your Convex documents

Sort paginated items

Did you find this page helpful?