Nested Filtering in Prisma

Get Help and Help Others
1

Hi all.
My structure is
TaskList → Contains Groups → Which contain tasks.

I am trying to return tasks for a given taskList if the task is within a timeframe, any ideas on how to craft this, it is just returning everything.

export const upcomingTasks: QueryResolvers['upcomingTasks'] = ({
  weddingId,
}) => {
  const today = new Date()
  const nextWeek = new Date(today)
  nextWeek.setDate(today.getDate() + 7)

  return db.taskList.findUnique({
    where: {
      weddingId,
    },
    include: {
      taskGroups: {
        include: {
          tasks: {
            where: {
              dueDate: {
                gte: today, // Greater than or equal to today
                lte: nextWeek, // Less than or equal to next week
              },
            },
          },
        },
      },
    },
  })
}
2

Hi @EverydayTinkerer this is definitely more of a Prisma question than a Redwood one.

But, may I ask why you are returning a TaskList when you say you want tasks?

Maybe find tasks where the dates fall into the range and then join in on the TaskList for the given TaskList id instead?

It would help to have a reproducible repo example with some sample seed data to query or test against.

Your query as its stands says get the task list with the id and where any tasks are upcoming.

It isn’t filtering the tasks per se —- the condition is does the task list has upcoming tasks.

And the if you get ten task list and use a relation resolver on TaskList.tasks the. It will return all the tasks for the list.

Hence why you likely want to query tasks up front.

3

I second @dthyresson’s point about it being more of a prisma issue.

But for the sake of thoroughness, you would want to query the tasks (not the tasklist). the include clause is a tool to specify which entities to do a join on. so it’s only necessary is you want the returned entities to reference other entities.

here’s how i would structure the query. note that the include part here may not be needed for your use case.

  const upcomingTasks = await db.task.findMany({
    include: {
      group: {
        include: {
          list: true,
        },
      },
    },
    where: {
      dueDate: {
        gte: today,
        lte: nextWeek,
      },
      group: {
        list: {
          weddingId,
        },
      },
    },
  })
// an example of accessing the referenced entities
const firstTask = upcomingTasks[0]
console.log('task id', firstTask.id)
console.log('group id', firstTask.group.id)
console.log('list id', firstTask.group.list.id)
4

Yup! Thanks @colbywhite for the example.

One point: be cautious of lte and gte on dates and you will overlap on the range.

Typically SQL has a between which is >= and <

And then you can use 00:00 times.

That said. Be wary off timezones and GMT always when doing time filters.

Since you are likely storing as UTC you may need to convert your date time values accordingly

5

Thanks all. This is the first project I have ever done where I am doing the db design and backend work. so I never know if I am connecting tables correctly.

here is my schema for these tables atm. I will have a look at what you have both suggested and have a play around.

model TaskList {
  id         String      @id @default(uuid())
  name       String
  wedding    Wedding?    @relation(fields: [weddingId], references: [id])
  weddingId  String?     @unique
  taskGroups TaskGroup[]
}

model TaskGroup {
  id         String    @id @default(uuid())
  name       String
  taskList   TaskList? @relation(fields: [taskListId], references: [id])
  taskListId String?
  tasks      Task[]
  order      Int       @default(0)
}

model Task {
  id          String     @id @default(uuid())
  title       String
  description String?
  dueDate     DateTime?
  TaskGroup   TaskGroup? @relation(fields: [taskGroupId], references: [id])
  taskGroupId String?
  status      String?    @default("todo")
}
6

Thanks @colbywhite your idea worked straight away. I still have a lot to learn when it comes to using prisma and designing db entries.