I want to set default value while Fetching data from DB using Sequelize ORM. For example I have some SQL query like
SELECT a.affiliate_id, a.active AS current_state,
IF(MAX(cn.contract_id) IS NULL ,0, IF(DATEDIFF(NOW(),MAX(cn.contract_date)) > 60,0,1)) AS affiliate_active
FROM zn_pf_affiliate a
LEFT JOIN zn_clients c ON a.affiliate_id = c.refferal_id AND c.refferal_type = 'affiliate'
LEFT JOIN zn_internal_inquiries i ON c.clientid = i.clientID
LEFT JOIN zn_pf_contracts cn ON i.inquiry_id = cn.source_object_id AND cn.source_object_type = 'lead' AND cn.stage=3 AND cn.status=3
WHERE a.agency_id = 159297
GROUP BY a.affiliate_id
HAVING current_state <> affiliate_active;
I want to convert this with Sequelize ORM but the main problem is IF(MAX(cn.contract_id) IS NULL ,0, IF(DATEDIFF(NOW(),MAX(cn.contract_date)) > 60,0,1)) AS affiliate_active How I write this line in Sequelize ORM. Thanks
To use some SQL pieces in Sequelize query you need to use Sequelize.literal like this:
const items = Model.findAll({
attributes: [
...// other attributes,
[Sequelize.literal('(IF(MAX(cn.contract_id) IS NULL ,0, IF(DATEDIFF(NOW(),MAX(cn.contract_date)) > 60,0,1)))', 'affiliate_active']
],
... // other options
})