I've got items that I want to order based on the timestamp (descending) and a score (descending).

orderBy = [
 {
   timestamp: 'desc',
 },
 {
   score: {value: 'desc'},
 },
]

When I set timestamp first, it just orders by timestamp it seems. And when I set score first, it shows the oldest with the highest score. But I want the newest ones with the highest scores. How do I do that?

@Timo 👋 Could you share your schema?

Basically, it's like this:

model Item {
  id  String @id @default(cuid()) @db.VarChar(80)
  timestamp  DateTime @default(now())
  score Score?
}

model Score {
  id  String @id @default(cuid()) @db.VarChar(80)
  value  Float  @default(0)
}

Just tested out on sample data and it works fine. Could you re-check the data you get from the

orderBy

and check for the timestamps?

I'm confused by this. It is very unlikely that there will be the same timestamps in records (given the timestamp precision), so wouldn't it almost always sort by timestamp?

Yes it would always sort by timestamp and then the score in this example

I'm getting the following order

timestamp: "2021-11-21T20:21:53.635Z"
value: 0

timestamp: "2021-11-20T15:56:46.861Z"
value: 0

timestamp: "2021-11-17T10:34:34.696Z"
value: 500

timestamp: "2021-11-12T17:44:04.251Z"
value: 50000

timestamp: "2021-11-08T09:18:05.407Z"
value: 0

Yes it's correct. See my earlier message. Because your timestamps are sorted at first. If you would have like 3 timestamps which are the same, only then would the score be sorted within those timestamps. Like this (look at the timestamps):

timestamp: "2021-11-21T20:21:59.635Z" 
value: 300

timestamp: "2021-11-21T20:21:53.635Z" <-- same timestamp
value: 5000

timestamp: "2021-11-21T20:21:53.635Z" <-- same timestamp
value: 4000

timestamp: "2021-11-21T20:21:53.635Z" <-- same timestamp
value: 500

But because it is very unlikely to have the same timestamps (the items would have to be added at the exact same time), your query is just sorting by timestamps. What's your use case exactly? What do you want to view in your app?

Thanks for the reply and explanation! I want to sort posts based on a score (which is calculated from different metrics - likes, comments, shares, etc.) while taking the posting time in consideration, otherwise it's always showing very old posts. What I did for now is to only take posts that are not older than 7 days, but it feels like a hacky way. You can actually see it in action, though the score isn't visible on the ui. https://inji.com/discover

I see what you mean. Yeah that prisma query is not gonna magically combine itself to make a weighted sort request 😉 I don't think there's anything right now in prisma that will make you do that. You would need to either do some raw sql query (https://stackoverflow.com/questions/8368089/how-can-you-weight-date-freshness-in-a-mysql-search) or modify returned data on the resolver/controller/frontend. Something like:

// for the sake of dealing with dates
import dayjs from 'dayjs'

// Don't sort them yet
const items = await.prisma.item.findMany();

function modifyScore(currentItem) {
  // if is from today multiply by some modifier etc
  if (dayjs(currentItem.timestamp).isToday()) {
    return currentItem.score * 2
  }
  if (dayjs(currentItem.timestamp).isYesterday()) {
    return currentItem.score * 1.6
  }
  const aWeekAgo = dayjs().subtract(7, days);
  if (dayjs(currentItem.timestamp).isAfter(aWeekAgo)) {
    return currentItem.score * 1.2
  }
  if (dayjs(currentItem.timestamp).isBefore(aWeekAgo)) {
    return currentItem.score * 0.8
  }
}

// loop trhough items and modify score
const itemsMapped = items.map(item => ({...item, score: modifyScore(item)}));

// now sort 
const itemsSorted = itemsMapped.sort((a,b) => a.score - b.score);

I typed it out just to give you an idea, I didn't test it, there might be errors. But that might not be scalable as you need to load all items at initial fetch. So there probably would be no use for that infinite scrolling of yours. Another veeery wild idea would be to run a node js cron job everyday https://jobscheduler.net/#/ On that job modify all items score everday by some number like

-100

(either fixed number or you provide an algorithm for that number or a modifer). You could make a new field in datamodel if you don't want to modify the original score, like:

weightedScore  Int

The older the item the more subtractions it got from each day. Then you could query them gradually with infinite scroll. But that is a wild idea. Altought not that bad if you think about it 😂 Hope that helps, good luck. 🤕

Thank you! I'll check it out!