sql - 如何在带有 sequelize 的 where 子句中使用从嵌套表中提取的日期年份

Question

我尝试使用 sequelize 执行以下 mySql 查询，以从 Unite_Legales 表中获取聚合数据以构建选项卡。

SELECT legale.fk_tranche_effectif_effectifs_code,
    legale.fk_nomenclature_naf_naf_code,
    legale.categorie_entreprise,
    COUNT(*) AS nb_etablissements
FROM Unite_Legales AS legale
    JOIN Exercice_Comptables AS exo ON exo.fk_unite_legale_siren = legale.siren
WHERE ($ { whereClause })
    AND exo.chiffre_affaire_net_total IS NOT NULL
    AND YEAR(exo.date_cloture) = $ { year }
GROUP BY legale.fk_nomenclature_naf_naf_code,
    legale.fk_tranche_effectif_effectifs_code,
    legale.categorie_entreprise
ORDER BY legale.categorie_entreprise,
    legale.fk_nomenclature_naf_naf_code;

我尝试了以下方法，但在嵌套 where 子句中提取年份时遇到问题。输出为空。

const answer = await unite_legales.findAll({

    attributes: [
        'fk_tranche_effectif_effectifs_code',
        'fk_nomenclature_naf_naf_code',
        'categorie_entreprise',
        [Sequelize.fn('COUNT', '*'), 'nb_etablissements']
    ],

    where: {
        fk_nomenclature_naf_naf_code: nafs,
    },

    include: [{
        model: db.Exercice_Comptables,
        as: 'Unite_Legales_exercice_comptables',
        required: true,
        attributes: [],
        raw: true,
        where: {
            chiffre_affaire_net_total: {
                [Sequelize.Op.not]: null
            },
            date_cloture: {
                [Sequelize.fn('YEAR', Sequelize.col('date_cloture'))]: 2019
            }
        }

    }],

    group: ['fk_nomenclature_naf_naf_code',
        'fk_tranche_effectif_effectifs_code',
        'categorie_entreprise'],

    order: [
        ['categorie_entreprise', 'ASC'],
        ['fk_nomenclature_naf_naf_code', 'ASC'],
    ],

    nested: true,
    raw: true
})

我怎么能这样做？

我的模型是：

sequelize.define('Unite_Legales', {
        siren: {
            type: DataTypes.INTEGER,
            allowNull: false,
            primaryKey: true
        },
        fk_nomenclature_naf_naf_code: {
            type: DataTypes.STRING(255),
            allowNull: false,
            references: {
                model: 'Nomenclature_Nafs',
                key: 'naf_code'
            }
        },
        fk_tranche_effectif_effectifs_code: {
            type: DataTypes.INTEGER,
            allowNull: false,
            references: {
                model: 'Tranches_Effectifs',
                key: 'effectif_code'
            }
        },
        annee_effectif: {
            type: DataTypes.DATE,
            allowNull: true
        },
        denomination: {
            type: DataTypes.STRING(255),
            allowNull: false
        },
        date_creation: {
            type: DataTypes.DATE,
            allowNull: false
        },
        categorie_entreprise: {
            type: DataTypes.STRING(255),
            allowNull: true
        },
        annee_categorie_entreprise: {
            type: DataTypes.DATE,
            allowNull: true
        }
    }, {
        sequelize,
        tableName: 'Unite_Legales',
        timestamps: true,
        indexes: [
            {
                name: "PRIMARY",
                unique: true,
                using: "BTREE",
                fields: [
                    { name: "siren" },
                ]
            },
            {
                name: "fk_nomenclature_naf_naf_code",
                using: "BTREE",
                fields: [
                    { name: "fk_nomenclature_naf_naf_code" },
                ]
            },
            {
                name: "fk_tranche_effectif_effectifs_code",
                using: "BTREE",
                fields: [
                    { name: "fk_tranche_effectif_effectifs_code" },
                ]
            },
        ]
    });

和 ：

sequelize.define('Exercice_Comptables', {
        id: {
            autoIncrement: true,
            type: DataTypes.INTEGER,
            allowNull: false,
            primaryKey: true
        },
        fk_unite_legale_siren: {
            type: DataTypes.INTEGER,
            allowNull: false,
            references: {
                model: 'Unite_Legales',
                key: 'siren'
            }
        },
        date_ouverture: {
            type: DataTypes.DATE,
            allowNull: true
        },
        date_cloture: {
            type: DataTypes.DATE,
            allowNull: false
        },
        ventes_marchandises_france: {
            type: DataTypes.INTEGER,
            allowNull: true
        },
        ventes_marchandises_export: {
            type: DataTypes.INTEGER,
            allowNull: true
        },
        ventes_marchandises_total: {
            type: DataTypes.INTEGER,
            allowNull: true
        },
        production_vendue_biens_france: {
            type: DataTypes.INTEGER,
            allowNull: true
        },
        production_vendue_biens_export: {
            type: DataTypes.INTEGER,
            allowNull: true
        },
        production_vendue_biens_total: {
            type: DataTypes.INTEGER,
            allowNull: true
        },
        production_vendue_services_france: {
            type: DataTypes.INTEGER,
            allowNull: true
        },
        production_vendue_services_export: {
            type: DataTypes.INTEGER,
            allowNull: true
        },
        production_vendue_services_total: {
            type: DataTypes.INTEGER,
            allowNull: true
        },
        chiffre_affaire_net_france: {
            type: DataTypes.INTEGER,
            allowNull: true
        },
        chiffre_affaire_net_export: {
            type: DataTypes.INTEGER,
            allowNull: true
        },
        chiffre_affaire_net_total: {
            type: DataTypes.INTEGER,
            allowNull: true
        }
    }, {
        sequelize,
        tableName: 'Exercice_Comptables',
        timestamps: true,
        indexes: [
            {
                name: "PRIMARY",
                unique: true,
                using: "BTREE",
                fields: [
                    { name: "id" },
                ]
            },
            {
                name: "fk_unite_legale_siren",
                using: "BTREE",
                fields: [
                    { name: "fk_unite_legale_siren" },
                ]
            },
            {
                name: "siren_cloture",
                unique: true,
                using: "BTREE",
                fields: ["fk_unite_legale_siren", "date_cloture"]
            }

        ]
    });

具有以下关联：

Exercice_Comptables.belongsTo(Unite_Legales, { as: "Unite_Legales_exercice_comptables", foreignKey: "fk_unite_legale_siren" });
Unite_Legales.hasMany(Exercice_Comptables, { as: "Unite_Legales_exercice_comptables", foreignKey: "fk_unite_legale_siren" });

标签： sqlnode.jssequelize.js