CODEWITHSUNDEEP
node.jspostgresqlsequelize.js
user5671335
user5671335

Reputation: 85

Node sequelize group

Postgresql database. Table:

Name  | equalId

name1 | 1
name2 | 2
name3 | 1
name4 | 2
name5 | 1

I want to get an array of objects sorted by equalId. Example:

[
  {equalId: 1, names: ['name1', 'name3', 'name5']},
  {equalId: 2, names: ['name2', 'name4']},
]

How do I do this with sequelize?

I tried:

await db.name.findAll({
  where: { equalId: { $not: null } },
  attributes: ['equalId', 'name'],
  order: ['name'],
  group: ['equalId', 'name'],
  raw: true,
  logging: true,
});

But the result is not correct.

Upvotes: 0

Views: 1471

Answers (1)

doublesharp
doublesharp

Reputation: 27667

You will need to sort and group the results after they have been retrieved from the database.

db.name.findAll({
  where: {equalId: {$not: null}},
  attributes: ['equalId', 'name'],
  order: ['equalId'],
  raw: true,
  logging: true
})
.then((items) => {
  // get an object keyed by equalId with a value of an array of names
  const names = items.reduce((names, item) => {
    if (!names[item.equalId]) {
      names[item.equalId] = [item.name];
    } else {
      names[item.equalId].push(item.name);
    }
    return names;
  }, {});
  // get an array of results from the object
  const result = Object.keys(names).map((equalId) => {
    return {
      equalId: equalId,
      names: names[key],
    };
  })
  // resolve the result array
  return Promise.resolve(result);
});

Upvotes: 2

Related Questions