Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0301· OpenAIAi2 WildChat-4.8M (AI2)

const [questionnaire, categories] = await prisma.$transaction([…

  1. User

    const [questionnaire, categories] = await prisma.$transaction([ prisma.questionnaire.findFirst({ orderBy: [{ startDate: “desc” }], where: { …filter, approved: true, disabled: false, endDate: { // gte: addDays(new Date(), -5), //previous code gte: subDays(new Date(), -5), // return questionnaires with in the last 5 days of the current date lte: new Date(), }, startDate: { lte: new Date(), }, isDraft: false, }, include: { questionnaireItems: { where: { disabled: false, }, include: { item: { include: { translation: true, itemMeasurements: true, }, }, }, }, questionnaireRegionStatus: { where: { regionCode: { in: regionCodes } }, }, }, }), prisma.division.findMany({ where: { disabled: false }, include: { group: { where: { disabled: false }, include: { class: { where: { disabled: false }, include: { subclass: { where: { disabled: false }, include: { translation: true, }, }, translation: true, }, }, translation: true, }, }, translation: true, }, }), ]); why is the above code not returning questionnaires that are equal to the current date or below the current date

  2. Assistant

    The code is only returning questionnaires that have a start date before or equal to the current date and an end date that's between 5 days ago and the current date. So if a questionnaire has a start date that is after the current date or an end date that's more than 5 days ago, it won't be returned by this query.

    To include questionnaires that have a start date equal to or after the current date, you can remove the startDate condition from the where clause:

    where: {
      ...filter,
      approved: true,
      disabled: false,
      endDate: {
        gte: subDays(new Date(), -5),
        lte: new Date(),
      },
      isDraft: false,
    },
    

    This will only filter by the endDate and other conditions, but not by the startDate.

  3. User

    what if the startDate must be less than or equal to current date

  4. Assistant

    If you want to include only the questionnaires that have a startDate less than or equal to the current date, you can add the condition startDate: { lte: new Date() } to the where clause:

    where: {
      ...filter,
      approved: true,
      disabled: false,
      startDate: { lte: new Date() },
      endDate: {
        gte: subDays(new Date(), -5),
        lte: new Date(),
      },
      isDraft: false,
    },
    

    This will ensure that you return only the questionnaires that are currently ongoing or have already started.

  5. User

    But the above changes still dont fetch a questionnarie with startDate 2023-05-15 01:00:00 and endDate 2023-05-26 24:00:00 when the current date is set to todays date

  6. Assistant

    That's because the query is checking for questionnaires that have an end date between 5 days ago and the current date, and a start date before or equal to the current date. If you want to include the questionnaire with a start date of 2023-05-15 01:00:00 and an end date of 2023-05-26 24:00:00 when the current date is today, you will need to modify the query to include it.

    One possible way to do this is to adjust the range of the end date filter to include the maximum end date that a questionnaire can have, which is 24:00:00 of the last day of the questionnaire. Here's an updated query that includes this adjustment:

    const [questionnaire, categories] = await prisma.$transaction([
      prisma.questionnaire.findFirst({
        orderBy: [{ startDate: "desc" }],
        where: {
          ...filter,
          approved: true,
          disabled: false,
          startDate: { lte: new Date() },
          endDate: {
            gte: subDays(new Date(), -5),
            lte: endOfDay(new Date()),
          },
          isDraft: false,
        },
        include: {
          questionnaireItems: {
            where: {
              disabled: false,
            },
            include: {
              item: {
                include: {
                  translation: true,
                  itemMeasurements: true,
                },
              },
            },
          },
          questionnaireRegionStatus: {
            where: { regionCode: { in: regionCodes } },
          },
        },
      }),
      prisma.division.findMany({
        where: { disabled: false },
        include: {
          group: {
            where: { disabled: false },
            include: {
              class: {
                where: { disabled: false },
                include: {
                  subclass: {
                    where: { disabled: false },
                    include: {
                      translation: true,
                    },
                  },
                  translation: true,
                },
              },
              translation: true,
            },
          },
          translation: true,
        },
      }),
    ]);
    

    With this adjustment, the query will include the questionnaire from your example as long as the current date is on or before 2023-05-26.