Dynamic query builder in convex

I'm trying to do a dynamic query based on conditions

import { v } from "convex/values";
import { BookingStatus } from "./lib/bookings";
import { query } from "./_generated/server";

export const getBookings = query({
  args: {
    studentId: v.optional(v.id("users")),
    tutorId: v.optional(v.id("users")),
    status: v.optional(
      v.union(
        v.literal(BookingStatus.AWAITING_TUTOR_CONFIRMATION),
        v.literal(BookingStatus.AWAITING_STUDENT_CONFIRMATION),
        v.literal(BookingStatus.AWAITING_PAYMENT),
        v.literal(BookingStatus.PAYMENT_FAILED),
        v.literal(BookingStatus.SCHEDULED),
        v.literal(BookingStatus.CANCELED),
        v.literal(BookingStatus.COMPLETED)
      )
    ),
    startDate: v.optional(v.number()), // Unix timestamp in milliseconds
    endDate: v.optional(v.number()), // Unix timestamp in milliseconds
  },
  handler: async (ctx, args) => {
    let query = ctx.db.query("bookings");

    if (args.studentId) {
      query = query.withIndex("by_student", (q) =>
        q.eq("student", args.studentId!)
      );
    }

    if (args.tutorId) {
      query = query.withIndex("by_tutor", (q) => q.eq("tutor", args.tutorId));
    }

    if (args.status) {
      query = query.withIndex("by_status", (q) => q.eq("status", args.status));
    }

    if (args.startDate !== undefined) {
      query = query.filter((q) => q.gte(q.field("startTime"), args.startDate));
    }

    if (args.endDate !== undefined) {
      query = query.filter((q) => q.lte(q.field("startTime"), args.endDate));
    }

    return await query.collect();
  },
});

import { v } from "convex/values";
import { BookingStatus } from "./lib/bookings";
import { query } from "./_generated/server";

export const getBookings = query({
  args: {
    studentId: v.optional(v.id("users")),
    tutorId: v.optional(v.id("users")),
    status: v.optional(
      v.union(
        v.literal(BookingStatus.AWAITING_TUTOR_CONFIRMATION),
        v.literal(BookingStatus.AWAITING_STUDENT_CONFIRMATION),
        v.literal(BookingStatus.AWAITING_PAYMENT),
        v.literal(BookingStatus.PAYMENT_FAILED),
        v.literal(BookingStatus.SCHEDULED),
        v.literal(BookingStatus.CANCELED),
        v.literal(BookingStatus.COMPLETED)
      )
    ),
    startDate: v.optional(v.number()), // Unix timestamp in milliseconds
    endDate: v.optional(v.number()), // Unix timestamp in milliseconds
  },
  handler: async (ctx, args) => {
    let query = ctx.db.query("bookings");

    if (args.studentId) {
      query = query.withIndex("by_student", (q) =>
        q.eq("student", args.studentId!)
      );
    }

    if (args.tutorId) {
      query = query.withIndex("by_tutor", (q) => q.eq("tutor", args.tutorId));
    }

    if (args.status) {
      query = query.withIndex("by_status", (q) => q.eq("status", args.status));
    }

    if (args.startDate !== undefined) {
      query = query.filter((q) => q.gte(q.field("startTime"), args.startDate));
    }

    if (args.endDate !== undefined) {
      query = query.filter((q) => q.lte(q.field("startTime"), args.endDate));
    }

    return await query.collect();
  },
});

schema.ts

  bookings: defineTable({
    student: v.id("users"), // Reference to the student (user with role STUDENT)
    tutor: v.id("users"), // Reference to the tutor (user with role TUTOR)
    service: v.id("services"), // Reference to the service being booked
    type: v.union(
      v.literal(BookingType.FREE_MEETING),
      v.literal(BookingType.LESSON)
    ), // Booking type
    status: v.union(
      v.literal(BookingStatus.AWAITING_TUTOR_CONFIRMATION),
      v.literal(BookingStatus.AWAITING_STUDENT_CONFIRMATION),
      v.literal(BookingStatus.AWAITING_PAYMENT),
      v.literal(BookingStatus.PAYMENT_FAILED),
      v.literal(BookingStatus.SCHEDULED),
      v.literal(BookingStatus.CANCELED),
      v.literal(BookingStatus.COMPLETED)
    ), // Booking status
    startTime: v.number(), // Start time of the booking (timestamp)
    endTime: v.number(), // End time of the booking (timestamp)
    createdAt: v.number(), // Creation time of the booking (timestamp)
    updatedAt: v.number(), // Last updated time of the booking (timestamp)
  })
    .index("by_student", ["student", "startTime"])
    .index("by_tutor", ["tutor", "startTime"])
    .index("by_status", ["status"]),

  bookings: defineTable({
    student: v.id("users"), // Reference to the student (user with role STUDENT)
    tutor: v.id("users"), // Reference to the tutor (user with role TUTOR)
    service: v.id("services"), // Reference to the service being booked
    type: v.union(
      v.literal(BookingType.FREE_MEETING),
      v.literal(BookingType.LESSON)
    ), // Booking type
    status: v.union(
      v.literal(BookingStatus.AWAITING_TUTOR_CONFIRMATION),
      v.literal(BookingStatus.AWAITING_STUDENT_CONFIRMATION),
      v.literal(BookingStatus.AWAITING_PAYMENT),
      v.literal(BookingStatus.PAYMENT_FAILED),
      v.literal(BookingStatus.SCHEDULED),
      v.literal(BookingStatus.CANCELED),
      v.literal(BookingStatus.COMPLETED)
    ), // Booking status
    startTime: v.number(), // Start time of the booking (timestamp)
    endTime: v.number(), // End time of the booking (timestamp)
    createdAt: v.number(), // Creation time of the booking (timestamp)
    updatedAt: v.number(), // Last updated time of the booking (timestamp)
  })
    .index("by_student", ["student", "startTime"])
    .index("by_tutor", ["tutor", "startTime"])
    .index("by_status", ["status"]),

I get a huge error, sorry for the spam

Type 'Query<{ document: { _id: Id<"bookings">; _creationTime: number; type: BookingType; tutor: Id<"users">; student: Id<"users">; service: Id<"services">; status: BookingStatus; startTime: number; endTime: number; createdAt: number; updatedAt: number; }; fieldPaths: ExtractFieldPaths<...> | "_id"; indexes: { ...; }; sear...' is missing the following properties from type 'QueryInitializer<{ document: { _id: Id<"bookings">; _creationTime: number; type: BookingType; tutor: Id<"users">; student: Id<"users">; service: Id<"services">; status: BookingStatus; startTime: number; endTime: number; createdAt: number; updatedAt: number; }; fieldPaths: ExtractFieldPaths<...> | "_id"; indexes: { ....': fullTableScan, withIndex, withSearchIndexts(2739)

Type 'Query<{ document: { _id: Id<"bookings">; _creationTime: number; type: BookingType; tutor: Id<"users">; student: Id<"users">; service: Id<"services">; status: BookingStatus; startTime: number; endTime: number; createdAt: number; updatedAt: number; }; fieldPaths: ExtractFieldPaths<...> | "_id"; indexes: { ...; }; sear...' is missing the following properties from type 'QueryInitializer<{ document: { _id: Id<"bookings">; _creationTime: number; type: BookingType; tutor: Id<"users">; student: Id<"users">; service: Id<"services">; status: BookingStatus; startTime: number; endTime: number; createdAt: number; updatedAt: number; }; fieldPaths: ExtractFieldPaths<...> | "_id"; indexes: { ....': fullTableScan, withIndex, withSearchIndexts(2739)

and all the args need to be marked with !

8 Replies

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!

lee•4w ago

What happens if you ask chatgpt to rewrite your code with the pattern i posted in the other thread? It gave me a good result when i tried it Can't paste because it's too long

lee•4w ago

lee•4w ago

The important part is you're splitting up the query construction into stages, so that you can prove to the type system that you're only using a single index. Okay actually chatgpt can't figure it out Maybe claude can do it? Chatgpt is refusing to follow the pattern I also see your thread in #ask-ai . If the pattern is so hard to figure out, we should make it easier with helper functions

edprotonOP•4w ago

Thanks @lee, I tried gpt too and the pattern was refused. Do you have some posts to advice me this? It's a common thing in Read.

lee•4w ago

I would try to do it by hand. The pattern should work I don't think we have posts that discuss the solution, although we could write one

edprotonOP•3w ago

You could @lee, it would be appreciate it.

lee•3w ago

I wrote this up. It's not polished or reviewed, but might help https://github.com/ldanilek/query-cookbook/blob/main/DYNAMIC_QUERY.md

GitHub

query-cookbook/DYNAMIC_QUERY.md at main · ldanilek/query-cookbook

Contribute to ldanilek/query-cookbook development by creating an account on GitHub.

Dynamic query builder in convex

Did you find this page helpful?