Prisma•2mo ago

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

23 Replies

KHRMOP•2mo ago

note I don't want to have the sortedEvents function, yes it works when I want all the events but if I want pagination then it doesn't work the way that is expected

Yetzederixx•2mo ago

I've had issues sorting deep in the tree. I think your best bet honestly is to use raw sql and/or a view since you can then do pagination without these worries

KHRMOP•2mo ago

view sounds interesting I think I'd like to explore any pointers on how to get started on that

Yetzederixx•2mo ago

A view is basically a saved query that looks like a table. There are two kinds. View and Materialized view. They have pros/cons that you'll have to look up. You will have to hand write the sql for either solution

Yetzederixx•2mo ago

That said, if you are going to be writing your sql anyway... this route may be easier honestly https://www.prisma.io/docs/orm/prisma-client/using-raw-sql

Write Your Own SQL in Prisma Client | Prisma Documentation

Learn how to use raw SQL queries in Prisma Client.

Yetzederixx•2mo ago

Please don't use the "unsafe" versions of raw query and such unless you really, really have to AND know how to sanitize the incoming data. https://xkcd.com/327/

xkcd: Exploits of a Mom

Her daughter is named Help I'm trapped in a driver's license factory.

KHRMOP•2mo ago

thanks I'll check it out for learning purposes But it might be easier in my case to add a field to my events table called earliestDate

Yetzederixx•2mo ago

So you have a many to many between events and the dates they occur since I believe an event could be like a tour for a musician?

KHRMOP•2mo ago

it's more like I want to practice the real purpose of all 3 tables was to reuse dates but honestly I don't think it's worth it Should just have 2 or even 1 table for this type of thing and just aggregate on the dates there's only 365 dates in a year so this way only 365 rows would be the max for the dates table But I don't know if that has any real benefit Well give or take the years, events would usually be within a year or so in practice and then they'd be deleted as needed

Yetzederixx•2mo ago

It's a useful model, there are a lot of uses for M->M relationships, but yeah. Make sure you aren't using a square peg for a round hole That use of date probably isn't well useful here, but in the context of say a touring event where the event has the same name (eg, Taylor Swift's Era Tour) that has dates in NYC, boston, etc this becomes useful even then... you can just use a sorted list in a jsonb column of the event, or a single table for event dates linked by event id, it reduces the complexity here and makes what you want to do a lot easier Something like this perhaps

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
  
  venue_name String
  address String
  moar_fields ...

  @@map("event_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
  
  venue_name String
  address String
  moar_fields ...

  @@map("event_dates")
}

Normalization is great and all, along with code/structure reuse, but it's also a trap that's easy to fall into

KHRMOP•2mo ago

yeah I might refactor to something like that, will look more into that jsonb thing as well It'll also make the queries overall simpler Thanks for the tips felt nice to talk about code with someone been a lonely journey haha

Yetzederixx•2mo ago

like literally everything else in this field, jsonb has it's own drawbacks, but you can index the column on subfield. My only gripe with jsonb is if you update/change data after the first write (often). It can, and will, generate significant replication lag as you scale your database since the entire column is written to the write ahead log. So, two caveats to using them, 1: they should be as static as possible, and 2: be as small as possible Frankly I hate working from home, so being able to chat with other engineers is nice 🙂 So with options 1 and 2, make sure you do at least one of them Gotta say thanks for the convo, I literally just had to implement the typed SQL thing hah

KHRMOP•2mo ago

I just reduced it from 3 tables to two tables xD but prisma still doesn't let you sort by relations just count for some reason

KHRMOP•2mo ago

KHRMOP•2mo ago

newer models

model TempEvent {
  eventId   String   @id @default(cuid()) @map("event_id")
  title       String

  eventDates TempEventDate[]

  @@map("temp_events")
}

model TempEventDate {
  dateId    Int      @id @default(autoincrement()) @map("date_id")

  date DateTime

  event   TempEvent @relation(fields: [eventId], references: [eventId])
  eventId String    @map("event_id")

  @@map("temp_event_dates")
}

model TempEvent {
  eventId   String   @id @default(cuid()) @map("event_id")
  title       String

  eventDates TempEventDate[]

  @@map("temp_events")
}

model TempEventDate {
  dateId    Int      @id @default(autoincrement()) @map("date_id")

  date DateTime

  event   TempEvent @relation(fields: [eventId], references: [eventId])
  eventId String    @map("event_id")

  @@map("temp_event_dates")
}

Yetzederixx•2mo ago

the order by inside eventDates isn't working?

KHRMOP•2mo ago

Yeah it doesn't allow it I tried the typed SQL was pretty damn cool I might made a video on it But with sqlite it's gonna be hard to aggregate eegate the dates without turning them into strings

Yetzederixx•2mo ago

ick sqlite heh, they don't have date truncation functions like postgres/mysql so you can agg on them? Also, sometimes it doesn't hurt to reverse the order of your join eg going TempEventDate -> TempEvent and then repack the structures in code

KHRMOP•2mo ago

I do have access to postgres but here I am trying to expand xD wanted to try out Turso couldnt figure out a good solution so I opted for having an earliestDate flag

 const initialEvents = await db.event.findMany({
    include: {
      eventDates: {
        orderBy: { date: "asc" },
      },
    },
    orderBy: { earliestDate: "asc" },
    take: TAKE_EVENTS_LIMIT,
  });

 const initialEvents = await db.event.findMany({
    include: {
      eventDates: {
        orderBy: { date: "asc" },
      },
    },
    orderBy: { earliestDate: "asc" },
    take: TAKE_EVENTS_LIMIT,
  });

I just have to now make sure that whenever an eventDate is delete I keep it in sync i.e. make sure earliestDate is updated for the event if needed

Yetzederixx•2mo ago

You can add an event trigger on the table, wlel in postgres at least

KHRMOP•2mo ago

true sometimes wonder if that would be more ideal than doing it at the server level

Yetzederixx•2mo ago

I had a boss once threaten us all with "if you ever write a sql function I'll fire you". I've since done them, since I'm the boss now, but I approach them very wearily heh

KHRMOP•2mo ago

I read some where recently you can write mysql functions in JavaScript now so thats pretty cool

Gaming

Programming

Sorting by Relations