Prisma - GroupBy relation (e.g "Most used Hashtags")-CodePudding

I would like to show the "Most used Hashtags” in my app.

It's just a simple many-to-many relation:

 model Post {
  id              Int     @id @default(autoincrement())
  title           String  @db.VarChar(255)
  description     String  @db.VarChar(255) 
  hashtags        PostHashtag[] 
}

model PostHashtag { 
  post             Post     @relation(fields: [postId], references: [id])
  postId           Int 
  hashtag          Hashtag @relation(fields: [hashtagId], references: [id])
  hashtagId        Int 

  @@id([postId, hashtagId]) 
  @@unique([postId, hashtagId])
} 
 
model Hashtag {
  id             Int     @id @default(autoincrement())
  name           String @db.VarChar(24)  
  posts          PostHashtag[]
}

I cannot really find a way to groupBy the "name" field of the "Hashtag" in the PostHashtag table.

My goal is a query which returns me the top 15 used hashtags. And unfortunately the docs won't help me with this.

CodePudding user response：

This will return the top 15 used hashtags by ordering with posts counts, with the counts included in the result:

const topHashtags = await prisma.hashtag.findMany({
    take: 15,
    orderBy: {
        posts: { _count: 'desc' },
    },
    include: {
        _count: {
            select: { posts: true },
        },
    },
});

CodePudding user response：

Was easier than I thought:

      return await prisma.hashtag.groupBy({ 
        by: ['name'], 
        _count:{
          name: true
        },
        orderBy: {
          _count: {
            name: 'desc',
          } 
    }
  })