sql - Sequelize belongsToMany 关联不通过表工作

Question

我在 PostgreSQL 上使用 Sequelize 来存储属于组织的用户。组织拥有用户可以访问的设备。因此，从本质上讲，用户也通过他们的组织拥有设备。

我将其设置为每个设备都使用 organization_id 与一个组织相关联，每个用户都使用 organization_id 与一个组织相关联。我正在尝试使用 Sequelize 进行设置以正确阅读它。我正在尽最大努力不诉诸于编写自定义查询，但如果我最终不得不这样做也没关系。

我正在尝试获取与用户 ID 关联的所有设备。当我尝试运行 findAll(...) 命令时，Sequelize 打印出这个疯狂的查询并出错。它输出这个查询，然后输出一个空集:

SELECT 
    "receiver"."receiver_id" AS "id", 
    "receiver"."organization_id" AS "organizationID", 
    "receiver"."identifier", 
    "receiver"."secret", 
    "receiver"."iterations", 
    "receiver"."sodium", 
    "receiver"."algorithm", 
    "receiver"."created", 
    "receiver"."modified", 
    "receiver"."deleted", 
    "receiver"."organization_id", 
    "users"."user_id" AS "users.id", 
    "users"."password" AS "users.password", 
    "users"."sodium" AS "users.sodium", 
    "users"."email" AS "users.email", 
    "users"."organization_id" AS "users.organizationID", 
    "users"."iterations" AS "users.iterations", 
    "users"."algorithm" AS "users.algorithm", 
    "users"."created" AS "users.created", 
    "users"."modified" AS "users.modified", 
    "users"."deleted" AS "users.deleted", 
    "users"."organization_id" AS "users.organization_id", 
    "users.organizations"."created" AS "users.organizations.created", 
    "users.organizations"."modified" AS "users.organizations.modified", 
    "users.organizations"."organization_id" AS "users.organizations.organization_id" 
FROM "receivers" AS "receiver" 
INNER JOIN (
    "organizations" AS "users.organizations" 
    INNER JOIN "users" AS "users" 
    ON "users"."user_id" = "users.organizations"."organization_id") 
ON "receiver"."receiver_id" = "users.organizations"."organization_id" 
AND ("users"."deleted" IS NULL AND "users"."user_id" = 2) 
WHERE "receiver"."deleted" IS NULL;

我怎样才能更好地编写定义或代码？

非常感谢。

我在 Sequelize 中的表定义:

var organization = sequelize.define( 'organization', {
    'id': {
        type: Sequelize.BIGINT,
        field: 'organization_id',
        allowNull: false,
        primaryKey: true,
        autoIncrement: true
    },
    'name' : {
        type:  Sequelize.STRING( 256 ),
        field: 'name',
        allowNull: false,
        validate: {
            notEmpty: true
        }
    }
}, {
    'createdAt' : 'created',
    'updatedAt' : 'modified',
    'deletedAt' : 'deleted',
    'tableName' : 'organizations',
    'paranoid'  : true
} );

var user = sequelize.define( 'user', {
    'id': {
        type: Sequelize.BIGINT,
        field: 'user_id',
        allowNull: false,
        primaryKey: true,
        autoIncrement: true
    },
    'password': {
        type: Sequelize.STRING( 64 ),
        field: 'password',
        allowNull: false,
        validate: {
            notEmpty: true
        }
    },
    'sodium': {
        type: Sequelize.STRING( 64 ),
        field: 'sodium',
        allowNull: false,
        validate: {
            notEmpty: true
        }
    },
    'email' : {
        type:  Sequelize.STRING( 64 ),
        field: 'email',
        allowNull: false,
        validate: {
            notEmpty: true
        }
    },
    'organizationID' : {
        type:  Sequelize.BIGINT,
        field: 'organization_id',
        allowNull: false,
        validate: {
            notEmpty: true
        }
    },
    'iterations' : {
        type:  Sequelize.INTEGER,
        field: 'iterations',
        allowNull: false,
        validate: {
            notEmpty: true
        }
    },
    'algorithm' : {
        type:  Sequelize.STRING( 8 ),
        field: 'algorithm',
        allowNull: false,
        defaultValue: 'sha256'
    }
}, {
    'createdAt' : 'created',
    'updatedAt' : 'modified',
    'deletedAt' : 'deleted',
    'tableName' : 'users',
    'paranoid'  : true
} );

var receiver = sequelize.define( 'receiver', {
    'id': {
        type: Sequelize.BIGINT,
        field: 'receiver_id',
        allowNull: false,
        primaryKey: true,
        autoIncrement: true
    },
    'organizationID': {
        type: Sequelize.BIGINT,
        field: 'organization_id',
        allowNull: false,
        validate: {
            notEmpty: true
        }
    },
    'identifier': {
        type: Sequelize.STRING( 64 ),
        field: 'identifier',
        allowNull: false,
        validate: {
            notEmpty: true
        }
    },
    'secret' : {
        type:  Sequelize.STRING( 64 ),
        field: 'secret',
        allowNull: false,
        validate: {
            notEmpty: true
        }
    },
    'iterations' : {
        type:  Sequelize.INTEGER,
        field: 'iterations',
        allowNull: false,
        validate: {
            notEmpty: true
        }
    },
    'sodium': {
        type: Sequelize.STRING( 64 ),
        field: 'sodium',
        allowNull: false,
        validate: {
            notEmpty: true
        }
    },
    'algorithm' : {
        type:  Sequelize.STRING( 8 ),
        field: 'algorithm',
        allowNull: false,
        defaultValue: 'sha256'
    }
}, {
    'createdAt' : 'created',
    'updatedAt' : 'modified',
    'deletedAt' : 'deleted',
    'tableName' : 'receivers',
    'paranoid'  : true
} );

// Organizations have users and users have organizations
organization.hasMany( user, { 'foreignKey' : 'organization_id' } );
user.belongsTo( organization, { 'foreignKey' : 'organization_id' } );

// Organizations have receivers
organization.hasMany( receiver, { 'foreignKey' : 'organization_id' } );
receiver.belongsTo( organization, { 'foreignKey' : 'organization_id' } );

// Receivers to users
user.belongsToMany( receiver, { 'through' : 'organizations', 'foreignKey' : 'organization_id' } );
receiver.belongsToMany( user, { 'through' : 'organizations', 'foreignKey' : 'organization_id' } );

我用来查询的代码:

// Get the devices for this person
db.receiver.findAll( {
    'include' : [
                {
                    'model' : db.user,
                    'where' : { 'id' : 2 }
                }
            ]
} )
.complete( function( error, result ) {
    if( error ) {
        console.log( error );
    }
    else {
        console.log( result );
    }
} );

Answer 1

尝试以下操作，它会选择与 where 语句匹配的用户，并包括与其关联的组织，而组织又包括与其关联的设备，因此您最终应该得到与用户关联的设备。

// Organizations have users
user.belongsTo(organization);
// Organizations have receivers
receiver.belongsTo(organization);


// Get the devices for this person
db.user.find( {
    'include' : [
        {model: db.organization,
         include: [model: db.receiver]
        }
    ]
    'where' : { 'id' : 2 }
} )
.complete( function( error, result ) {
    if( error ) {
        console.log( error );
    }
    else {
        console.log( result );
    }
} );

如果您使用下划线 id 字段名称，如 organization_id，您可以在创建模型时指定“underscored: true”，这样您就不必在创建关联时指定外键字段。