Using RAW SQL in WHERE clause

[moseleyi]

Is it possible to write a whole raw SQL in WHERE clause?

[northeda]

Sure, here's a pretty minimal example of how to do it:

Assuming you have imported a model and you have access to the global Sequelize instance:

model.findAll({ where: Sequelize.literal('your special where clause here') }).then(result => { 
    // SELECT * FROM model WHERE your special where clause here
    ...
});

StackOverflow might be a better forum for this discussion.

[jedwards1211]

This is sort of a documentation issue, because it's not mentioned in the documentation of the where option to findAll, findOne, etc... http://docs.sequelizejs.com/class/lib/model.js~Model.html#static-method-findAll

Nor does the documentation for Sequelize.literal indicate that it can be used directly as a where clause.

[sushantdhiman]

One way is

Model.findAll({
   where: {
       [Op.and]: [
           Sequelize.literal('RAW SQL STATEMENT WHICH WONT BE ESCAPED!!!')
       ]
   }
})

You can find some querying examples in http://docs.sequelizejs.com/manual/tutorial/querying.html

[jedwards1211]

@sushantdhiman there is only one example of using Sequelize.literal there, and it is buried in the ordering section where one will easily miss it:

  // Will order by max age descending
  order: sequelize.literal('max(age) DESC')

[shanegibney]

What is the correct syntax for the line starting Level_id: sequelize.literal(".....

var express = require("express")
var router = express.Router()
const Task = require("../model/Task")
.....
//Update task
    router.put("/task/:id", (req, res) => {
      if (!req.body) {
        res.status(400)
        res.json({
          error: "Bad Data....!"
        })
      } else {
        Task.update({
            Level_id: Sequelize.literal("SELECT level_id FROM level_tbl WHERE Level = 'Ordinary'"),
            Year: req.body.Year,
            Question: req.body.Question,
            Answer: req.body.Answer
          }, {
            where: {
              id: req.params.id
            }
          })
          .then(() => {
            res.send("Task Updated")
          })
          .error(err => res.send(err))
      }
    })

Removing that line, everything works fine.

I get the error from Express.js,

ReferenceError: Sequelize is not defined

Task.js does require Sequelize and it also creates the model.

Task.js

const Sequelize = require("sequelize")
const db = require("../database/db.js")

module.exports = db.sequelize.define(
  "physics_tbls", {
    id: {
      type: Sequelize.INTEGER,
      primaryKey: true,
      autoIncrement: true
    },
    Level: {
      type: Sequelize.STRING
    },
    Level_id: {
      type: Sequelize.INTEGER
    },
    Year: {
      type: Sequelize.INTEGER
    },
    .........
  }, {
    timestamps: false
  }
)

I am using a MySQL DB.

Any help would be greatly appreciated,

Thanks

[jedwards1211]

@shanegibney

• Sequelize undefined in your express route file, not in Task.js. You need const Sequelize = require('sequelize') in the file that defines your express route.
• You probably also need parentheses around the subquery like

  Level_id: Sequelize.literal("(SELECT level_id FROM level_tbl WHERE Level = 'Ordinary')")
  //                           ^                                                       ^

• Also, are you sure the primary key of level_tbl is level_id? I'm guessing the primary key is probably just id and you meant SELECT id FROM level_tbl...

[shanegibney]

@jedwards1211 That works thank you. I Both needed to require sequelize again and the brackets exactly as you suggested. It would be easier to use id instead of level_id as you say. Thanks again, much appreciated.

[Jplus2]

Does Sequelize.literal support for replacements? How to do this in where clause? I get an error if I follow replacements syntax for sequelize.query

[ephys]

You can use replacements & bind parameters in literals:

User.findAll({
  where: literal('id = :id'),
  replacements: {
    id: 5,
  },
});

It can be used as part of a regular query using and and or:

User.findAll({
  where: or(
    { id: 6 },
    literal('id = :id'),
  ),
  replacements: {
    id: 5,
  },
});

In Sequelize 7 you can also use the sql template string tag:

const id = 5;

User.findAll({
  where: sql`id = ${id}`,
});