CODEWITHSUNDEEP
sequelize.js
Pikachu-go
Pikachu-go

Reputation: 3088

Sequelize sum between two columns in model

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;

Upvotes: 7

Views: 23501

Answers (3)

Jairon Alves Lima
Jairon Alves Lima

Reputation: 323

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'],
})

Upvotes: 1

DavidA
DavidA

Reputation: 628

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']
})

Upvotes: 7

Pikachu-go
Pikachu-go

Reputation: 3088

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.

Upvotes: 8

Related Questions