与 WHERE IN (“ARRAY") 的续集关系 [英] Sequelize relation with WHERE IN ("ARRAY")

问题描述

是否可以在 sequelize 中定义关系，其中多个外键作为数组存储在一个字段中.基本上是一个belongsToMany，但不是关系表，所有的id都存储在一个字段中，以逗号分隔.查询将使用 WHERE IN('1,5,7').

Is it possible to define a relationships in sequelize where multiple foreign keys are stored as an array in one field. Basically a belongsToMany but instead of an relation table all ids are stored comma separated in one field. Query would be with WHERE IN('1,5,7').

推荐答案

你可以做到，但绝对没有外键约束——不可能存储外键数组.但是，您可以创建一个列，该列将是表示关联表 ID 的整数数组.然后你只需要创建一些 instanceMethods 来检索和设置数组值(记住 ARRAY 类型只在 PostgreSQL 中可用).

You can do it, but definitely without foreign key constraints - there is no possibility of storing array of foreign keys. You can however create a column which will be an array of integers representing IDs of associated table. Then you just need to create some instanceMethods for retrieving and setting the array values (remember that the ARRAY type is only available in PostgreSQL).

我不推荐此解决方案，因为它不能确保数据一致性.让我们考虑一个例子 - 用户属于 id 为 1、2 和 4 的许多类别.如果删除其中一个类别，您需要记住从用户的 categories 数组中手动​​删除此 id(使用某些hook 或其他工具)，否则该用户仍将被分配到不再存在的类别.

I do not recommend this solution due to the fact that it does not ensure data consistency. Let's consider an example - user belongs to many categories with ids 1, 2 and 4. If you delete one of those categories, you need to remember to manually remove this id from categories array of user (with use of some hook or other tool), otherwise this user will still be assigned to a category that no longer exists.

const User = sequelize.define('User', {
    categories: DataTypes.ARRAY(DataTypes.INTEGER)
}, {
    instanceMethods: {
        getCategories: function(){
            return Category.findAll({
                where: {
                    id: { $in: this.get('categories') }
                }
            }).then(categories => {
                // if user's categories was [1, 2, 4]
                // it would return categories with id=1, id=2 and id=4
                return categories;
            });
        },
        setCategories: function(ids){
            return this.setDataValues('categories', ids).save().then(self => {
                return self;
            });
        }
    }
});

这篇关于与 WHERE IN (“ARRAY") 的续集关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！