joren_vh
joren_vh
PostsComments
DTDrizzle Team
Created by joren_vh on 9/16/2024 in #help
Aggregating the count of a relationship
Hi there, In my data model I have "reflections" which are basically "posts" and I have "reactions". One reflection can have many reactions by multiple users. Basically like a facebook post and the reactions would be the emoji reactions you can do. This is my current query to get a paged list of reflections 
 db.query.userReflectionTable.findMany({
      where: and(...filters),
      with: {
        user: true,
        reactions: true,
      },
      orderBy: (reflections, { desc }) => [desc(reflections.createdAt), desc(reflections.id)],
      limit: limit,
      offset: offset,
    }),
 db.query.userReflectionTable.findMany({
      where: and(...filters),
      with: {
        user: true,
        reactions: true,
      },
      orderBy: (reflections, { desc }) => [desc(reflections.createdAt), desc(reflections.id)],
      limit: limit,
      offset: offset,
    }),
Now, instead of a list of all reaction entities, I want to have an aggregated response for the reactions. I basically want a list of something that looks like this 
...
"reactions": [
  {
    "emotion": "LAUGHING"
    "count": 21
  },

  {
    "emotion": "CRYING"
    "count": 17
  },
...
]
...
"reactions": [
  {
    "emotion": "LAUGHING"
    "count": 21
  },

  {
    "emotion": "CRYING"
    "count": 17
  },
...
]
Is it possible to do that in one query? And how do I do that in drizzle without using a raw query? (Using postgress btw) Thanks in advance!
2 replies