Prisma•2mo ago

FindMany Where Confusion

I'm trying to do a simple "find many where" query on the following schema: TLDR: a course has a 1 to many relationship with a scheduled event.

model Course {
  id             Int              @id @default(autoincrement())
  subjectCode    String
  courseCode     String           
  title          String
  shortTitle     String
  description    String
  createdAt      DateTime         @default(now())
  updatedAt      DateTime         @updatedAt
  ScheduledEvent ScheduledEvent[]
}

model ScheduledEvent {
  id                                 Int                           @id @default(autoincrement())
  crn                                String                        @unique
  description                        String
  courseId                           Int
  course                             Course                        @relation(fields: [courseId], references: [id])
  createdAt                          DateTime                      @default(now())
  updatedAt                          DateTime                      @updatedAt
  ScheduledEventRegistrations        ScheduledEventRegistrations[]
}

model Course {
  id             Int              @id @default(autoincrement())
  subjectCode    String
  courseCode     String           
  title          String
  shortTitle     String
  description    String
  createdAt      DateTime         @default(now())
  updatedAt      DateTime         @updatedAt
  ScheduledEvent ScheduledEvent[]
}

model ScheduledEvent {
  id                                 Int                           @id @default(autoincrement())
  crn                                String                        @unique
  description                        String
  courseId                           Int
  course                             Course                        @relation(fields: [courseId], references: [id])
  createdAt                          DateTime                      @default(now())
  updatedAt                          DateTime                      @updatedAt
  ScheduledEventRegistrations        ScheduledEventRegistrations[]
}

I have these two queries: Query 1 returns and empty list. Query 2 returns a list with many records. Why is it that I don't get anything back from query 1???

const events = await prisma.scheduledEvent.findMany({
    where: {
      course: {
        title: {
          contains: "Math",
        },
      },
    },
    include: {
      course: true,
    },
  });

  const courses = await prisma.course.findMany({
    where: {
      title: {
        contains: "Math",
      },
    },
  });

const events = await prisma.scheduledEvent.findMany({
    where: {
      course: {
        title: {
          contains: "Math",
        },
      },
    },
    include: {
      course: true,
    },
  });

  const courses = await prisma.course.findMany({
    where: {
      title: {
        contains: "Math",
      },
    },
  });

5 Replies

RaphaelEtim•2mo ago

Hi @Matthew I'm not able to reproduce this as both queries return the Math record. Can you confirm that the Math record exist in scheduledEvent and is connected to a course in your db?

MatthewOP•2mo ago

Hey @RaphaelEtim thanks for the help! Math isn't a record in this case, I'm just hoping to do a do a lookup kind of like "where the title of course (a string) contains the substring of "math"" Here is some example output of the two code blocks

RaphaelEtim•2mo ago

Thanks for clarifying. What does the output of the query below look like?

const events = await prisma.scheduledEvent.findMany();

const events = await prisma.scheduledEvent.findMany();

MatthewOP•2mo ago

I'm able to filter on properties directly on the "scheduledEvent" table, but the issue I seem to get is when I want to filter scheduledEvents by data on their associated course

RaphaelEtim•2mo ago

I just implemented the exact query you specified and it works

 const res = await prisma.scheduledEvent.findMany({
    where: {
      course: {
        title: {
          startsWith: "Math",
        },
      },
    },
    include:{
      course: true,
    }
  });

 const res = await prisma.scheduledEvent.findMany({
    where: {
      course: {
        title: {
          startsWith: "Math",
        },
      },
    },
    include:{
      course: true,
    }
  });

output

[
  {
    id: 1,
    description: 'Math class',
    courseId: 1,
    createdAt: 2024-10-08T15:10:54.775Z,
    updatedAt: 2024-10-08T15:10:54.775Z,
    course: {
      id: 1,
      subjectCode: 'CSE',
      courseCode: null,
      title: 'Math',
      shortTitle: null,
      description: null,
      createdAt: 2024-10-08T15:10:54.775Z,
      updatedAt: 2024-10-08T15:10:54.775Z
    }
  },
  {
    id: 2,
    description: 'Mathematics for all business',
    courseId: 2,
    createdAt: 2024-10-08T16:43:09.738Z,
    updatedAt: 2024-10-08T16:43:09.738Z,
    course: {
      id: 2,
      subjectCode: 'MATH',
      courseCode: null,
      title: 'Mathematics for business',
      shortTitle: null,
      description: null,
      createdAt: 2024-10-08T16:43:09.738Z,
      updatedAt: 2024-10-08T16:43:09.738Z
    }
  }
]

[
  {
    id: 1,
    description: 'Math class',
    courseId: 1,
    createdAt: 2024-10-08T15:10:54.775Z,
    updatedAt: 2024-10-08T15:10:54.775Z,
    course: {
      id: 1,
      subjectCode: 'CSE',
      courseCode: null,
      title: 'Math',
      shortTitle: null,
      description: null,
      createdAt: 2024-10-08T15:10:54.775Z,
      updatedAt: 2024-10-08T15:10:54.775Z
    }
  },
  {
    id: 2,
    description: 'Mathematics for all business',
    courseId: 2,
    createdAt: 2024-10-08T16:43:09.738Z,
    updatedAt: 2024-10-08T16:43:09.738Z,
    course: {
      id: 2,
      subjectCode: 'MATH',
      courseCode: null,
      title: 'Mathematics for business',
      shortTitle: null,
      description: null,
      createdAt: 2024-10-08T16:43:09.738Z,
      updatedAt: 2024-10-08T16:43:09.738Z
    }
  }
]

Gaming

Programming

FindMany Where Confusion