AlejandroMMXM Posts - Convex Community

AlejandroMMXM

•Created by AlejandroMMXM on 5/9/2025 in #support-community

.distinct() sorting by _creationTime, but want it to sort by custom field

I have an issue with using distinct in this code. This code works correctly returning the messages sorted by message timestamp field (not by creationTime)

 chat_messages: ChatMessages.table
    .index("by_device_and_timestamp", ["device_id", "timestamp"])
    .index("by_device_and_contact_and_timestamp", [
      "device_id",
      "contact_id",
      "timestamp",
    ]),

const messages = stream(ctx.db, schema)
      .query("chat_messages")
      .withIndex("by_device_and_timestamp", (q) =>
        q.eq("device_id", args.deviceId)
      )
      .order("desc").paginate(args.paginationOpts)

 chat_messages: ChatMessages.table
    .index("by_device_and_timestamp", ["device_id", "timestamp"])
    .index("by_device_and_contact_and_timestamp", [
      "device_id",
      "contact_id",
      "timestamp",
    ]),

const messages = stream(ctx.db, schema)
      .query("chat_messages")
      .withIndex("by_device_and_timestamp", (q) =>
        q.eq("device_id", args.deviceId)
      )
      .order("desc").paginate(args.paginationOpts)

But i need to return only one message per contact, so i add distinct(["contact_id"]), and change withIndex() to use "by_device_and_contact_and_timestamp"

    const messages = stream(ctx.db, schema)
      .query("chat_messages")
      .withIndex("by_device_and_contact_and_timestamp", (q) =>
        q.eq("device_id", args.deviceId)
      )
      .order("desc")
      .distinct(["contact_id"]).paginate(args.paginationOpts);

    const messages = stream(ctx.db, schema)
      .query("chat_messages")
      .withIndex("by_device_and_contact_and_timestamp", (q) =>
        q.eq("device_id", args.deviceId)
      )
      .order("desc")
      .distinct(["contact_id"]).paginate(args.paginationOpts);

The problem is that now it's going to sort results by _creationTime, and i need it to always sort by message.timestamp.

2 replies