将原始SQL查询与Sequelize ORM和文字一起使用 [英] Using a raw SQL query with Sequelize ORM and literal

问题描述

使用Sequelize ORM，我尝试更新字段level_id，其中该字段在另一个名为level_tbl的表中具有指向字段Level的外键.

Using the Sequelize ORM I am trying to update the field level_id where this field has a foreign key to the field Level in another table called level_tbl.

 select * from level_tbl;
+----------+----------+
| level_id | Level    |
+----------+----------+
|        1 | Higher   |
|        2 | Ordinary |
+----------+----------+

我的更新任务如下所示，并且您可以看到我正在尝试使原始sql查询与Sequelize一起用作文字.

My update task looks like this, and as you can see I am trying to get a raw sql query to work as a literal with Sequelize.

  //Update task
    router.put("/task/:id", (req, res) => {
      if (!req.body) {
        res.status(400)
        res.json({
          error: "Bad Data....!"
        })
      } else {
        Task.update({
            Level: req.body.Level,
            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,
            Topic: req.body.Topic,
            Sub_topic: req.body.Sub_topic,
            Question_type: req.body.Question_type,
            Marks: req.body.Marks,
            Question_number: req.body.Question_number,
            Part: req.body.Part,
            Sub_part: req.body.Sub_part
          }, {
            where: {
              id: req.params.id
            }
          })
          .then(() => {
            res.send("Task Updated")
          })
          .error(err => res.send(err))
      }
    })

此行的正确语法是什么?

What would be the correct syntax for this line?

   Level_id: [sequelize.literal("SELECT level_id FROM level_tbl WHERE Level = 'Ordinary'")],

问题是我已经导入了模型并可以访问全局Sequelize实例.因此，文档中的示例不适用于这种方式，即

The issue is that I already have imported a model and have access to the global Sequelize instance. Therefore example in the documentation don't apply this way, i.e.,

 order: sequelize.literal('max(age) DESC')

来自 https://sequelize.org/master/manual/querying.html

还有

https://github.com/sequelize/sequelize/issues/9410#issuecomment-387141567

定义了模型的My Task.js如下

My Task.js where the model is defined is as follows,

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

我正在使用MEVN堆栈-> MySQL，Express.js，Vue.js和Node.js

I am using a MEVN stack -> MySQL, Express.js, Vue.js and Node.js

任何帮助将不胜感激，

谢谢

推荐答案

我需要在task.js中再次要求Sequelize，该文件定义了快速路由.尽管Task.js本身需要续集，但仅需要Task.js还是不够的.

I needed to require Sequelize again in tasks.js, the file the defines the express routes. It wasn't enough just to require Task.js although Task.js does itself require sequelize.

const Sequelize = require('sequelize')
var express = require("express")
var router = express.Router()
const Task = require("../model/Task")

在查询周围和双引号内还需要加括号，

Also brackets needed around the query and inside the double quotes,

Level_id: Sequelize.literal("(SELECT level_id FROM level_tbl WHERE Level = 'Higher')"),

这篇关于将原始SQL查询与Sequelize ORM和文字一起使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！