https://www.prisma.io/ logo
Join Slack
Powered by
I'm tracking reads of blog posts. Among other thin...
# orm-help
k
I'm tracking reads of blog posts. Among other things, I track the post slug. I want to find the most popular blog posts and order them by their popularity. To do this I need to first count how many rows reference each post slug, and then order them by that count. Ultimately what I'm hoping for is an array of post slugs ordered by popularity. How would I go about doing this with prisma? EDIT: I think I figured it out. I have to sort it myself in JS, but that's fine. If I'm missing anything that would allow me to have postgres do this sorting for me that would be sweet, but I think this will work:
Copy code
const result = await prisma.postRead.groupBy({
    by: ['postSlug'],
    orderBy: { postSlug: 'asc', },
    _count: true,
    take: limit,
  })
EDIT AGAIN: I think this won't work with a limit because we're not ordering by the count, but by the postSlug. Anyone know if it's possible to order by the slug? Otherwise I have to get all the data and then limit it.
d
Hi, so you want to order by a count of a group of a relation? Yeah we have hit this before and I have seen other people hit this issue too. Two options are using an sql query or by sorting the entire dataset on the server which will work but only for smaller data sets. I'm not sure if there is an active issue to +1 but it seems like this is a common case that isn't handled
t
k
Thanks! I'll look into it!
👍 1
@Thomas Ladd that worked perfectly. Thank you!
Copy code
async function getMostPopularPostSlugs({
  limit,
  exclude,
}: {
  limit: number
  exclude: Array<string>
}) {
  const result = await prisma.postRead.groupBy({
    by: ['postSlug'],
    _count: true,
    orderBy: {
      _count: {
        postSlug: 'desc',
      },
    },
    where: {
      postSlug: {notIn: exclude},
    },
    take: limit,
  })

  return result.map(({postSlug}) => postSlug)
}
Here's what it was before:
Copy code
async function getMostPopularPostSlugs({
  limit,
  exclude,
}: {
  limit: number
  exclude: Array<string>
}) {
  const result = await prisma.postRead.groupBy({
    by: ['postSlug'],
    orderBy: {postSlug: 'asc'},
    _count: true,
  })

  const filtered = result.filter(({postSlug}) => !exclude.includes(postSlug))
  const sorted = sortBy(filtered, i => -i._count)

  return sorted.slice(0, limit).map(({postSlug}) => postSlug)
}
This feels so much better!
t
Awesome!
d
Ah, cool
Open in Slack
PreviousNext
Powered by