时间戳

首页 > 解决方案 > 使用复合外键时使用 Sequelize 的“包含”问题

javascript - 使用复合外键时使用 Sequelize 的“包含”问题

问题描述

这是我的主表的示例：

+-------------+------------------------+--------------------+
| tenant      | vas_id                 | friendly_name      |
+-------------+------------------------+--------------------+
| brand_1     | 1gb_data_zone1         | 1GB Data in Zone 1 |
| brand_1     | promo_summer_2019_10GB | 10GB for Summer    |
| brand_1     | roaming_prepaid        | Roaming            |
| brand_1     | voicemail_prepaid      | Voicemail          |
| brand_2     | test_vas               | Test               |
| brand_2     | roaming_prepaid        | Roaming            |
| brand_2     | voicemail_prepaid      | Voicemail          |
+-------------+------------------------+--------------------+

tenant并且vas_id是该表中的两个主键（也称为复合主键），它们一起用作对另一个表的约束，1:N 关系：

+---------+------------------------+-----------------+-------------------+-------------------+
| tenant  | vas_id                 | activation_cost | deactivation_cost | modification_cost |
+---------+------------------------+-----------------+-------------------+-------------------+
| brand_1 | 1gb_data_zone1         |            2000 |                 0 |                 0 |
| brand_1 | promo_summer_2019_10GB |               0 |                 0 |                 0 |
| brand_1 | roaming_prepaid        |               0 |                 0 |                 0 |
| brand_1 | voicemail_prepaid      |               0 |                 0 |                 0 |
| brand_2 | test_vas               |               0 |                 0 |                 0 |
| brand_2 | roaming_prepaid        |               0 |                 0 |                 0 |
| brand_2 | voicemail_prepaid      |               0 |                 0 |                 0 |
+---------+------------------------+-----------------+-------------------+-------------------

你说这个结构能和Sequelize相处吗？

这是我用来标记两个主键的代码：

const vas = serviceLayerDB.define('vas',
    { // Database columns:
        tenant: {
            type: Sequelize.STRING(45),
            primaryKey: true
        },
        vas_id: {
            type: Sequelize.STRING(100),
            primaryKey: true
        }
        friendly_name: {
            type: Sequelize.STRING(100)
        }
    }

const vas_pricing = serviceLayerDB.define('vas_pricing',
    { // Database columns:
        tenant: {
            type: Sequelize.STRING(45),
            primaryKey: true
        },
        vas_id: {
            type: Sequelize.STRING(100),
            primaryKey: true
        },
        activation_cost: {
            type: Sequelize.NUMBER
        },
        deactivation_cost: {
            type: Sequelize.NUMBER
        },
        modification_cost: {
            type: Sequelize.NUMBER
        }
    });

...这是我用来将上面的表格与另一个表格（vas_pricing）相关联的代码：

vas.hasOne(vas_pricing, { foreignKey: 'vas_id' });
vas.hasOne(vas_pricing, { foreignKey: 'tenant' });

例如，在执行以下在主表和子表中找到的代码时，会发生奇怪的事情：

let options = {
    where: {
        tenant: 'brand_1',
        vas_id: 'promo_summer_2019_10GB'
    },
    include: [
        {
            model: vas_pricing,
            required: false
        }
    ]
};
vas.findAll(options)
    .then(function(data) {
        console.log(JSON.stringify(data, null, 2))
    })
    .catch(function(error) {
        console.error(error);
    });

结果：

[
    {
        "tenant": "brand_1",
        "vas_id": "promo_summer_2019_10GB",
        "friendly_name": "10GB During Summer",
        "vas_pricing": {
            "tenant": "brand_1",
            "vas_id": "1gb_data_zone1",
            "activation_cost": 20,
            "deactivation_cost": 0,
            "modification_cost": 0
        }
    },
    {
        "tenant": "brand_1",
        "vas_id": "promo_summer_2019_10GB",
        "friendly_name": "10GB During Summer",
        "vas_pricing": {
            "tenant": "brand_1",
            "vas_id": "promo_summer_2019_10GB",
            "activation_cost": 0,
            "deactivation_cost": 0,
            "modification_cost": 0
        }
    },
    {
        "tenant": "brand_1",
        "vas_id": "promo_summer_2019_10GB",
        "friendly_name": "10GB During Summer",
        "vas_pricing": {
            "tenant": "brand_1",
            "vas_id": "roaming_prepaid",
            "activation_cost": 0,
            "deactivation_cost": 0,
            "modification_cost": 0
        }
    },
    {
        "tenant": "brand_1",
        "vas_id": "promo_summer_2019_10GB",
        "friendly_name": "10GB During Summer",
        "vas_pricing": {
            "tenant": "brand_1",
            "vas_id": "voicemail_prepaid",
            "activation_cost": 0,
            "deactivation_cost": 0,
            "modification_cost": 0
        }
    }
]

预期结果：

[
    {
        "tenant": "brand_1",
        "vas_id": "promo_summer_2019_10GB",
        "friendly_name": "10GB During Summer",
        "vas_pricing": {
            "tenant": "brand_1",
            "vas_id": "promo_summer_2019_10GB",
            "activation_cost": 0,
            "deactivation_cost": 0,
            "modification_cost": 0
        }
    }
]

几天来，我一直在努力寻找解决方案，但没有成功。有任何想法吗？

标签： javascriptmysqlsqlnode.jssequelize.js

解决方案

在我看来，您vas_pricing比vas. 因此，我建议您vas_pricing改为进行查询：

let options = {
  where: {
    tenant: 'brand_1',
    vas_id: 'promo_summer_2019_10GB'
  },
  include: [
    {
      model: vas,
      required: false
    }
  ]
};

vas_pricing.findAll(options)
.then(function(data) {
    console.log(JSON.stringify(data, null, 2))
})
.catch(function(error) {
    console.error(error);
});

推荐阅读