#Dynamic query builder in convex

1 messages · Page 1 of 1 (latest)

scarlet arch
#

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();
  },
});

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"]),

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)

and all the args need to be marked with !

long tundraBOT
#

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](https://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!
stuck shore
#

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

#

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

stuck shore
#

Maybe claude can do it? Chatgpt is refusing to follow the pattern

#

I also see your thread in #1228095053885476985 . If the pattern is so hard to figure out, we should make it easier with helper functions

scarlet arch
#

Thanks @stuck shore, 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.

stuck shore
#

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

scarlet arch
#

You could @stuck shore, it would be appreciate it.