KHRM
KHRM
Explore posts from servers
PPrismaDTDrizzle TeamKKinde
PostsComments
PPrisma
Created by KHRM on 10/3/2024 in #help-and-questions
Sorting by Relations
Hello, I am trying to introduce infinite scrolling im my application but I am running into an issue. I am using Prisma ORM with sqlite through Turso here is what I curently have 
  const events = await db.event.findMany({
    include: {
      eventDates: {
        orderBy: {
          date: {
            date: "asc",
          },
        },
        include: { date: true },
      },
    },
    skip,
    take,
    cursor,
  });

  const sortedEvents = events.sort((a, b) => {
    const aDate = a.eventDates[0].date.date.getTime();
    const bDate = b.eventDates[0].date.date.getTime();

    return aDate - bDate;
  });
  const events = await db.event.findMany({
    include: {
      eventDates: {
        orderBy: {
          date: {
            date: "asc",
          },
        },
        include: { date: true },
      },
    },
    skip,
    take,
    cursor,
  });

  const sortedEvents = events.sort((a, b) => {
    const aDate = a.eventDates[0].date.date.getTime();
    const bDate = b.eventDates[0].date.date.getTime();

    return aDate - bDate;
  });
I sort the events after I get them, but if I am only grabbing 5 results at a time, its only going to sort those 5 (which means it may be sorted within itself, but it might not be the 5 earliest events) here is a simplified version of my schema 
model Event {
  eventId   String   @id @default(cuid()) @map("event_id")
  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @default(now()) @updatedAt @map("updated_at")
  title       String
  eventDates EventDate[]
  @@map("events")
}

model EventDate {
  event   Event  @relation(fields: [eventId], references: [eventId])
  eventId String @map("event_id")
  date    Date   @relation(fields: [dateId], references: [dateId])
  dateId  Int    @map("date_id")

  @@unique([eventId, dateId])
  @@map("event_dates")
}

model Date {
  dateId Int      @id @default(autoincrement()) @map("date_id")
  date   DateTime @unique
  eventDates EventDate[]
  @@map("dates")
}
model Event {
  eventId   String   @id @default(cuid()) @map("event_id")
  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @default(now()) @updatedAt @map("updated_at")
  title       String
  eventDates EventDate[]
  @@map("events")
}

model EventDate {
  event   Event  @relation(fields: [eventId], references: [eventId])
  eventId String @map("event_id")
  date    Date   @relation(fields: [dateId], references: [dateId])
  dateId  Int    @map("date_id")

  @@unique([eventId, dateId])
  @@map("event_dates")
}

model Date {
  dateId Int      @id @default(autoincrement()) @map("date_id")
  date   DateTime @unique
  eventDates EventDate[]
  @@map("dates")
}
How can I get all events sorted by their first event date, whcih is sorted by their date example query result 
console.log(events[0])
console.log(events[0])
{
  eventId: 'cm1s1bcxo0029jzxzoivivxr7',
  createdAt: 2024-10-02T15:40:54.972Z,
  updatedAt: 2024-10-02T15:40:54.972Z,
  title: 'AXCN: Mobile Suit Gundam',
  eventDates: [
    {
      eventId: 'cm1s1bcxo0029jzxzoivivxr7',
      dateId: 105,
      date: { dateId: 105, date: 2024-10-02T00:00:00.000Z }
    },
    {
      eventId: 'cm1s1bcxo0029jzxzoivivxr7',
      dateId: 106,
      date: { dateId: 106, date: 2024-10-06T00:00:00.000Z }
    }
  ]
{
  eventId: 'cm1s1bcxo0029jzxzoivivxr7',
  createdAt: 2024-10-02T15:40:54.972Z,
  updatedAt: 2024-10-02T15:40:54.972Z,
  title: 'AXCN: Mobile Suit Gundam',
  eventDates: [
    {
      eventId: 'cm1s1bcxo0029jzxzoivivxr7',
      dateId: 105,
      date: { dateId: 105, date: 2024-10-02T00:00:00.000Z }
    },
    {
      eventId: 'cm1s1bcxo0029jzxzoivivxr7',
      dateId: 106,
      date: { dateId: 106, date: 2024-10-06T00:00:00.000Z }
    }
  ]
note eventDates array may not necessarily be in order but this is covered by 
 include: {
      eventDates: {
        orderBy: {
          date: {
            date: "asc",
          },
        },
        include: { date: true },
      },
    },
 include: {
      eventDates: {
        orderBy: {
          date: {
            date: "asc",
          },
        },
        include: { date: true },
      },
    },
38 replies