Sequelize sum between two columns in model

Question

How can I create a query like this with sequelize ?

SELECT name, region, SUM(((COALESCE(base_income, 0) + COALESCE(user_taxes, 0))) AS total_sal FROM user GROUP BY name, region;

You may directly fire query, please have a look doc http://docs.sequelizejs.com/en/latest/docs/raw-queries/ — Arif Khan, Oct 09 '16 at 18:50
@ArifKhan Yep I can use raw query but that would be last resort when using a ORM. What would be the right way using Sequelize methods ? — Pikachu-go, Oct 09 '16 at 19:41

score 8 · Answer 1 · answered Oct 10 '16 at 11:52

8

OK, so following is the solution for above RAW sql query.

  user.findAll({
  attributes: ['name', 'region', [sequelize.fn('SUM', (sequelize.fn('COALESCE', (sequelize.col('base_income')), 0), sequelize.literal('+'), sequelize.fn('COALESCE', (sequelize.col('user_taxes')), 0))), 'total_sal']],
  group: ['name', 'name']})

We have to use sequelize.literal inorder to place any operator between query. Hope this helps.

answered Oct 10 '16 at 11:52

Pikachu-go

3,058
4
20
27

1

Not sure why, but this appears to return the sum of the last-provided column-- not the sum of both columns. – defraggled Nov 02 '20 at 14:31
@defraggled did you ever figure out why that happens? – boileau Jun 29 '21 at 19:23
@boileau No sorry. I just checked my app code to find what implementation I landed on, but it seems I never ended up needing to do this anyway. – defraggled Jul 01 '21 at 10:26
1

Yeah, it seems sequelize doesn't generate the correct SQL for that syntax. I ended up putting the whole query in sequelize.literal – boileau Jul 01 '21 at 11:58

score 7 · Answer 2 · edited Jan 28 '18 at 13:31

7

Although it is an old post, I ran to this now twice. So I'd like to share my solution:

user.findAll({ 
  attributes: [ 'name', 'region',
    [ sequelize.literal(
        'COALESCE(base_income, 0) + COALESCE(user_taxes, 0)'
      ), 'total_sal'
    ]
  ],
  group: ['name', 'name']
})

edited Jan 28 '18 at 13:31

iBug

35,554
7
89
134

answered Jan 28 '18 at 13:12

DavidA

608
8
9

score 1 · Answer 3 · answered Jun 21 '20 at 01:59

1

Despite not finding in the documentation the use as attributes. The sequelize.where function can be a good solution, as it allows the use of sequelize.col for case of associations in models.

User.findAll({
  attributes: [
    'name',
    'region',
    [
      Sequelize.fn(
        'SUM',
        Sequelize.where(Sequelize.col('base_income'), '+', Sequelize.col('user_taxes'))
      ),
      'total_sal',
    ],
  ],
  group: ['name', 'region'],
})

answered Jun 21 '20 at 01:59

Jairon Alves Lima

323
2
5

Bom hack. This is good because it autoquotes the columns for us, which is mandatory e.g. in PostgreSQL when you have upper case chars, with literal you'd need double quotes in that case e.g. as in: `sequelize.literal('"baseIncome" + "userTaxes"')`. Still `.where` is so verbose, I'm tempted to just go with literal. What a shame... – Ciro Santilli OurBigBook.com Apr 03 '22 at 20:16

Sequelize sum between two columns in model

3 Answers3

Linked