thedevstockgirl•14mo ago

How to make a query for a compound index like this?

I'd like to be able to have an index with the postId, but then also have an index on likesCount. How can I write the query for this? .index("by_post_id_and_likes_count", ["postId", "likesCount"])

14 Replies

erquhart•14mo ago

Your code is correct, is it not working? Oh you said how to write the query

Calonz•14mo ago

From my experience what you can do is: const post = await ctx.db.query(“posts”).withIndex(“by_post_id_and_likes_count”, (q) => q.eq(“postId”, postId).eq(“likesCount”, likedId). Do you think @erquhart this is the way to do it?

erquhart•14mo ago

Here's the basic doc for that: https://docs.convex.dev/database/indexes/#querying-documents-using-indexes

Indexes | Convex Developer Hub

Indexes are a data structure that allow you to speed up your

erquhart•14mo ago

For sure that's accurate, but I'm guessing OP wants to sort on likes count rather than eq() The link above goes to example code for using gte(), lt(), etc for sorting on an index.

thedevstockgirlOP•14mo ago

Ok. Thank you. And you are correct. I want to sort on likes count and also have it use the postId index I did look at that doc, but it does not answer the question though. I specifically want to sort by the likeCount, but don't want that on the whole comments, but rather only on comments on a specific post

erquhart•14mo ago

Is the table a posts table or a table of comments for posts Assuming this is a comments table, the postId in the index will limit the query to only comments for that post. The likesCount field in the index can then be used to sort.

thedevstockgirlOP•14mo ago

@erquhart , yes, it is on the comments table. Can you write out how you would use a compound index here, with the likesCount used to sort it. It is a paginated query. @Calonz , this is for sorting, consequently, you don't have a likeId

erquhart•14mo ago

Example:

import { PaginationOptions, paginationOptsValidator } from 'convex/server'
import { query } from './_generated/server'
import { v } from 'convex/values'

const pagePostCommentsByMostLiked = query({
  args: {
    postId: v.id('post'),
    paginationOpts: paginationOptsValidator,
  },
  handler: async (ctx, args) => {
    return ctx.db
      .query('posts')
      .withIndex('by_postId_likesCount', (q) => q.eq('postId', args.postId))
      .order('desc')
      .paginate(args.paginationOpts)
  },
})

import { PaginationOptions, paginationOptsValidator } from 'convex/server'
import { query } from './_generated/server'
import { v } from 'convex/values'

const pagePostCommentsByMostLiked = query({
  args: {
    postId: v.id('post'),
    paginationOpts: paginationOptsValidator,
  },
  handler: async (ctx, args) => {
    return ctx.db
      .query('posts')
      .withIndex('by_postId_likesCount', (q) => q.eq('postId', args.postId))
      .order('desc')
      .paginate(args.paginationOpts)
  },
})

Query results are ordered by their index by default, across the index fields from left to right, ascending. In this case, your index is first sorted by postId, and then where the post id is equal, it's sorted by likesCount. Since we're using q.eq() to get only posts with a specific id, only the likesCount impacts sorting. Note that we don't have to mention likesCount in the query index function (the function we're passing to withIndex()) we're using here for it to impact sorting, it automatically impacts sorting because it is included in the index definition in the schema. We could have an index with 10 fields and only .eq() on one of them in a query (would have to be the first one), and all 10 would still impact sorting. If we did not add .order('desc'), it would return the items with the lowest likesCount first. You want highest count first (I think), so we set it to sort descending. I know that's still kind of a lot, feel free to ask for clarification on any of these points.

thedevstockgirlOP•14mo ago

Thanks @erquhart . I think my query was not clear. This query is for the postComments table, not the post. Say you are already on a post. You want to query the postComments table for all comments belonging to that post. But then, a user has an option to sort those comments by most recent or oldest, or most liked or least liked. So you want to query the postComments table, where postId is the current post you are looking at, and you want the first comment to be the comment with the most likes., hence the index of by_post_id_and_likes_count on the postComments table. The example you shared show a query on the post table. And it is also not really doing anything with the likes count.

Michal Srb•14mo ago

switch(ordering) {
  case "like_count": 
    return ctx.db.query('comments')
  .withIndex('by_postId_likesCount', (q) => q.eq('postId', args.postId))
      .order('desc');
  case "time":
    return ctx.db.query('comments')
  .withIndex('by_postId', (q) => q.eq('postId', args.postId))
      .order('desc');
}

switch(ordering) {
  case "like_count": 
    return ctx.db.query('comments')
  .withIndex('by_postId_likesCount', (q) => q.eq('postId', args.postId))
      .order('desc');
  case "time":
    return ctx.db.query('comments')
  .withIndex('by_postId', (q) => q.eq('postId', args.postId))
      .order('desc');
}

When sorting by like_count, you need an index that includes it (by_postId_likesCount). When sorting by creation time, you don't need a special index because _creationTime is always the last indexed field (hence by_postId). Does that make sense? You'll always get the results sorted by the first index field you don't use in withIndex call. For index("foo", ["a", "b", "c", "d", "e"]) withIndex("foo", q=>q.eq("a", a).eq("b", b)) sorts by the "c" field

thedevstockgirlOP•14mo ago

That is exactly what I was looking for. Thank you so much. I almost gave up. There was no way I would know this without your last explanation. Especially the piece about the sorting happening with the first index you don't include. Thanks again.

erquhart•14mo ago

@Michal Srb isn’t sorting across all fields and not just the first one you don’t use? (Excluding those used for equality matching)

Michal Srb•14mo ago

It's sorting by the first field that isn't used for equality, then on conflicts by the next field, etc. doc 1: {x: "a", y: "a", z: "a"} doc 2: {x: "a", y: "a", z: "b"} if I do withIndex("foo", q=>q.eq("x", x)).order("desc") I'll get doc 2, then doc 1, because the results are sorted by y, and then on conflict by z.

erquhart•14mo ago

Okay, just making sure I wasn't missing something 👍

How to make a query for a compound index like this?

Did you find this page helpful?