Menu
I have a sql query:
SELECT field1, field2,
CASE
WHEN field1=1 THEN 'a'
ELSE 'b'
END
AS field3
FROM test
and I want to implement it with sequelizejs ,
const params = {
attributes: //DO SELECT CASE,
};
yield Model.findAll(params);
Can anyone help me? Thank you.
269
Sequelize instance comes with the query() method which you can use to run a raw query. The syntax of the method is as shown below: const [results, metadata] = await sequelize. query( "Your query here", { options } );
According to the doc : If you do not provide other arguments than the SQL, raw will be assumed to the true, and sequelize will not try to do any formatting to the results of the query.
To set the Sequelize findAll sort order in Node. js, we can set the order property. const getStaticCompanies = () => { return Company. findAll({ where: { //... }, order: [ ['id', 'DESC'], ['name', 'ASC'], ], attributes: ['id', 'logo_version', 'logo_content_type', 'name', 'updated_at'] }); };
You need to import the object from Sequelize to use it in your code: const { Sequelize, Op } = require("sequelize"); When you call the query methods from your model, you can use the Op object properties to create SQL queries with operators.
For the people that are still looking for this answer
Model.findAll({
attributes: [[models.sequelize.literal('CASE WHEN "field1" = true THEN 55 ELSE 23 END'), 'field3']]
}
or
Model.findAll({
attributes: { include: [[models.sequelize.literal('CASE WHEN "field1" = true THEN 55 ELSE 23 END'), 'field3']]}
}
So in my example when field1 is true it will return 55 else 23. This will generate a query like
SELECT CASE WHEN "field1" THEN 55 ELSE 23 END AS "field3" FROM "models"
For more information you can look in the documentation http://docs.sequelizejs.com/en/latest/docs/querying/#attributes
111
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With