Drizzle Team•4mo ago

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!

1 Reply

>> Thunder <<•3mo ago

this is easely possible by using sql functions like count and some subqueries send me a dm with some more information on the tables you are querying to and i can help you stub it out

Gaming

Programming

Aggregating the count of a relationship