使用带有 Sequelize ORM 和文字的原始 SQL 查询 [英] 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

我定义模型的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

任何帮助将不胜感激，

谢谢，

推荐答案

我需要在 tasks.js 中再次要求 Sequelize，该文件定义了快速路由.尽管 Task.js 本身确实需要 sequelize，但仅仅需要 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')"),

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