mysql - 如何在连接表上运行动态原始查询？

Question

以下是使用 Sequelize 和 Mysql 为 Nodejs 编写的代码。

我正在创建一个搜索来动态搜索 2 个带有原始查询的连接表。

where 子句不起作用，它正在返回整个记录集。

任何想法PLZ为什么？

const config = require('config.json');
const mysql = require('mysql2/promise');
const { Sequelize } = require('sequelize');

module.exports = {
search
};

async function getConn() {
    // create db if it doesn't already exist
    const { host, port, user, password, database } = config.database;
    const connection = await mysql.createConnection({ host, port, user, password });

// connect to db
const sequelize = new Sequelize(database, user, password, { dialect: 'mysql' });
return sequelize;
}

async function search(criteria)
{
    const dbs = await getConn();
    let sql = "";
    let whereclause = {};

    if(criteria.manufacturer_id > 0)
        whereclause.manufacturer_id = criteria.manufacturer_id;
    if(criteria.media_id > 0)
        whereclause.media_id = criteria.media_id;
    if(criteria.scale_id > 0)
        whereclause.scale_id = criteria.scale_id;
    if(criteria.group_id > 0)
        whereclause.group_id = criteria.group_id;
    if(criteria.year_of_manufacturer)
        whereclause.year_of_manufacturer= criteria.year_of_manufacture;
    if(criteria.aircraft_name != "") 
        whereclause.aircraft_name = { aircraft_name: { [Op.like]: `%${criteria.aircraft_name}%`} };

    sql = "SELECT aircraft.* FROM aircraft INNER JOIN aircraftmodels using (aircraft_id)";
    const result = await dbs.query(sql, {
       raw:true,
       where:  whereclause});

    console.log(result);
   (await dbs).close;
}

这是我的搜索对象

{
    "manufacturer_id": 191,
    "media_id": 0,
    "scale_id": 0,
    "group_id":0,
    "year_of_manufacture":1933,
    "aircraft_name": ""
}

标签： mysqlnode.jssequelize.js