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

  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

events

events

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")
}

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

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 }
    }
  ]

{
  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 },
      },
    },

 include: {
      eventDates: {
        orderBy: {
          date: {
            date: "asc",
          },
        },
        include: { date: true },
      },
    },

 include: {
      eventDates: {
        orderBy: {
          date: {
            date: "asc",
          },
        },
        include: { date: true },
      },
    },

Sorting by Relations

Similar Threads

Similar Threads

Similar Threads