AlejandroMMXM•4w ago

.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.

1 Reply

Convex Bot•4w ago

Thanks for posting in <#1088161997662724167>. Reminder: If you have a Convex Pro account, use the Convex Dashboard to file support tickets. - Provide context: What are you trying to achieve, what is the end-user interaction, what are you seeing? (full error message, command output, etc.) - Use search.convex.dev to search Docs, Stack, and Discord all at once. - Additionally, you can post your questions in the Convex Community's <#1228095053885476985> channel to receive a response from AI. - Avoid tagging staff unless specifically instructed. Thank you!

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

Did you find this page helpful?