How To Group For Multiple Fields In Sequelize

Question

I need one help related to the sequelize and I am using postgres. I want to group records in sequelize. Also the form and user value comes after populate or using include method of sequelize.

I have applied this code but it didn't work:-

{
  group: ['formId', 'userId', 'responseFrom'],
 include: [ { model: forms, as: 'form' }, { model: users, as: 'user' } ]
}

Here the name of table is formAnswers.

[{
            "id": 21,
            "formId": 1,
            "userId": 123,
            "formQuestionId": 2,
            "answer": "8,5,6",
            "responseFrom": "WAITING",
            "createdAt": "2020-01-14T02:31:19.173Z",
             "form": {
                "id": 1,
                "name": "Choose Group",
            },
            "user": {
                "id": 123,
                "fullName": "Test User",              
                "username": "test123",
            }
        },
        {
            "id": 22,
            "formId": 1,
            "userId": 123,
            "formQuestionId": 1,
            "answer": "3",
            "responseFrom": "WAITING",
            "createdAt": "2020-01-14T02:31:19.178Z",
            "form": {
                "id": 1,
                "name": "Choose Group",
            },
            "user": {
                 "id": 123,
                "fullName": "Test User",              
                "username": "test123",
            }
}]

This is the sample record, there will be multiple records for each user. I want to group the records by using formId and userId. Also you can consider responseFrom in group by. I have tried with group in sequelize but its not working.

I need only single record which have formId and userId same. If we are using the above data so the expected output will be:-

[{
            "id": 22,
            "formId": 1,
            "userId": 123,
            "formQuestionId": 1,
            "responseFrom": "WAITING",
            "createdAt": "2020-01-14T02:31:19.178Z",
            "form": {
                "id": 1,
                "name": "Choose Group",
            },
            "user": {
                 "id": 123,
                "fullName": "Test User",              
                "username": "test123",
            }
 }]

I need to apply pagination for this as well so please keep it in mind.

@gru It will give me this error:- column \"formAnswers.id\" must appear in the GROUP BY clause or be used in an aggregate function so if I use id in group by then I don't have expected output. — Devashish Suthar, Feb 24 '22 at 08:16
Does this help? https://stackoverflow.com/a/70747310/2956135 — Emma, Feb 24 '22 at 17:10

score 0 · Answer 1 · answered Feb 25 '22 at 19:02

Possible solution: (not tested)

formAnswers.findAll({
  attributes: [
    sequelize.literal(`DISTINCT ON("form_question_responses"."formId","form_question_responses"."userId","form_question_responses"."responseFrom") 1`),
    'id',
    'formId',
    'userId',
    'responseFrom',
  ],
  include: [
    { model: forms, as: 'form', attributes: ['id', 'name'] },
    { model: users, as: 'user', attributes: ['id', 'fullName', 'username'] }
  ],
  // automatically order by as used DISTINCT ON - DISTINCT ON strictly orders based on columns provided
  // order: [
  //   ['formId', 'ASC'],
  //   ['userId', 'ASC'],
  // ],
  offset: undefined, // for no offset
  limit: 50,
})

How To Group For Multiple Fields In Sequelize

1 Answers1