1 reply

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!

Drizzle Team•2y ago•

1 reply

joren_vh

Aggregating the count of a relationship

 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!

Aggregating the count of a relationship

Similar Threads

Aggregating the count of a relationship

Similar Threads

Similar Threads

Similar Threads