Strapi GROUP BY and COUNT fields with the same value-CodePudding

Is there any way in strapi to group by entries with the same fields and count its total?

Trying to create a Poll App which has "Response" Collection containing an "Answer" Field (enum: a, b, c, d). Would like to group responses with the same answers. Something like this:

{
     "answer": "a",
     "total": 3
}, {
     "answer": "b",
     "total": 1
}

Is it possible out of the box?

To give more context, here's its sql counterpart:

select *, count(answers) from responses group by answers

CodePudding user response：

there is no known default way for groupby with entity service, however there is count query:

/src/answer/controllers/answer.js

const { createCoreController } = require("@strapi/strapi").factories;

module.exports = createCoreController("api::answer.answer", ({ strapi }) => ({
  async find(ctx) {
    let { query } = ctx;
    let answers = await strapi.db.query("api::answer.answer").findMany({
      ...query,
    });

    answers = await Promise.all(answers.map(async (answer) => ({
      ...answer,
      total: await strapi.db.query("api::answer.answer").count({where: '...'})
    })))

    return answers
  },
}));

or you can use raw query like this:

let { rows } = await strapi.db.connection.raw(
  `select id from posts where published_at IS NOT null order by random() limit ${count};
    `);

let { rows } = await strapi.db.connection.raw(
  `select *, count(answers) from responses group by answers;`);