node.js - 无法使用 SEQUELIZE Node.js 检索具有特定电话号码的用户数据

Question

我在 Nodejs 中使用 SEQUELIZE 创建了两个关联表(Account、AccountCLI)。 Account 表与 AccountCLI 表有很多关联。帐户表有列(用户 ID、eppusername、用户名、供应商参数、项目 ID)。 AccountCLI 表有列(电话号码，用户 ID(外键))。当用户输入电话号码时，它会返回相应的用户数据。在我的代码中，它返回所有用户数据，而不是具有电话号码的特定用户。您可以在下面找到代码。请给点建议？

表条目显示为:

  'use strict';
module.exports = (sequelize, DataTypes) => {
  const Account = sequelize.define('Account', {
    epprojectname: DataTypes.STRING,
    username: DataTypes.STRING,
    projectid: DataTypes.STRING,
    vendorparameters: DataTypes.STRING,
    credentials: DataTypes.STRING
  }, {});
  Account.associate = function(models) {
    Account.hasMany(models.AccountCLI, {
      as: 'accountcli',
      foreignKey: 'userid'
    });
  };
  return Account;
};

'use strict';
module.exports = (sequelize, DataTypes) => {
  const AccountCLI = sequelize.define('AccountCLI', {
    phonenumber: DataTypes.STRING,
    userid: DataTypes.INTEGER
  }, {});
  AccountCLI.associate = function(models) {
    AccountCLI.belongsTo(models.Account, {
      as: "account",
      foreignKey: 'userid'   
     });
  };
  return AccountCLI;
};

在输入电话号码值时检索所有用户数据的代码(假设检索特定用户数据)显示为(这是建议之后的代码):

        // find account with specific number 
exports.findSpecificUser = async (req, res) => {
var whereStatement = {};
if(req.body.phonenumber)
whereStatement['$accountcli.phonenumber$'] = {$like: '%' + req.body.phonenumber + '%'};
  Account.findAll({  include: { model: AccountCLI, as: "accountcli", where: whereStatement } })
    .then((data) => {
      res.send(data);
    })
    .catch((err) => {
      res.status(500).send({
        message:
          err.message || "Some error occurred while retrieving account with the specific phonenumber.",
      });
    });
};

这是我使用 POSTMAN 检索到的 JSON 文件。在这里，我放了 (phonenumber, 3334) ，它假设检索 ("epprojectname": "DFSumitayayabot") 用户数据，但它检索了两个用户信息。

[
    {
        "id": 68,
        "epprojectname": "DFSumitayayabot",
        "username": "Sumit",
        "projectid": "ayayabot",
        "vendorparameters": "newparameters",
        "credentials": "1589956379476-tslint.json",
        "createdAt": "2020-05-19T15:23:00.440Z",
        "updatedAt": "2020-05-20T06:36:05.903Z",
        "accountcli": [
            {
                "id": 227,
                "phonenumber": "33344",
                "userid": 68,
                "createdAt": "2020-05-20T06:36:05.997Z",
                "updatedAt": "2020-05-20T06:36:05.997Z"
            },
            {
                "id": 228,
                "phonenumber": " 447467",
                "userid": 68,
                "createdAt": "2020-05-20T06:36:05.997Z",
                "updatedAt": "2020-05-20T06:36:05.997Z"
            }
        ]
    },
    {
        "id": 67,
        "epprojectname": "DFZeyadavayachatbot",
        "username": "Zeyad",
        "projectid": "avayachatbot",
        "vendorparameters": "{\"synth_speech_cfg\": { \"voice\": { \"name\": \"en-AU-Wavenet-C\"}}}",
        "credentials": "1589958578216-AppointmentType.json",
        "createdAt": "2020-05-19T15:17:43.399Z",
        "updatedAt": "2020-05-20T07:09:38.228Z",
        "accountcli": [
            {
                "id": 249,
                "phonenumber": "44433",
                "userid": 67,
                "createdAt": "2020-05-20T07:09:38.332Z",
                "updatedAt": "2020-05-20T07:09:38.332Z"
            },
            {
                "id": 250,
                "phonenumber": " 5566",
                "userid": 67,
                "createdAt": "2020-05-20T07:09:38.332Z",
                "updatedAt": "2020-05-20T07:09:38.332Z"
            }
        ]
    }
]

Answer 1

电话号码 字段在 AccountCLI 模型中，但您在 Account 查询中使用该字段添加了条件。
尝试这样的事情:

if(req.body.phonenumber)
  whereStatement['$accountcli.phonenumber$'] = {$like: '%' + req.body.phonenumber + '%'};