sql - Sequelize 'where' 条件以 'AND' 而不是 'WHERE' 开头

Question

这没有意义。 Sequelize 创建了一个以 AND 而不是 WHERE 开头的 where 条件。

我正在尝试执行此查询:

var query = {
            limit: 10,
            order: [
                ['id', 'DESC']
            ],
            //attributes: ['id', 'name', 'supplier_id', 'created_at', 'url'],
            include: [
                {
                    required: false,
                    model: models.termTaxonomies,
                    include: [{
                        model: models.term
                    }, {
                        attributes: ['id'],
                        model: models.taxonomy
                    }],
                    where: ["termRelationships.product_id IS NULL"],
                },
                models.image,
                models.supplier
            ],

        };

在使用 Product.findAll(query) 执行上面的查询后创建下面的 SQL。

SELECT "product".*
    ,"termTaxonomies"."id" AS "termTaxonomies.id"
    ,"termTaxonomies"."taxonomy_id" AS "termTaxonomies.taxonomy_id"
    ,"termTaxonomies"."term_id" AS "termTaxonomies.term_id"
    ,"termTaxonomies"."parentId" AS "termTaxonomies.parentId"
    ,"termTaxonomies"."hierarchyLevel" AS "termTaxonomies.hierarchyLevel"
    ,"termTaxonomies"."distance" AS "termTaxonomies.distance"
    ,"termTaxonomies.termRelationships"."product_id" AS "termTaxonomies.termRelationships.product_id"
    ,"termTaxonomies.termRelationships"."term_taxonomy_id" AS "termTaxonomies.termRelationships.term_taxonomy_id"
    ,"termTaxonomies.term"."id" AS "termTaxonomies.term.id"
    ,"termTaxonomies.term"."name" AS "termTaxonomies.term.name"
    ,"termTaxonomies.term"."plural" AS "termTaxonomies.term.plural"
    ,"termTaxonomies.term"."sin_article" AS "termTaxonomies.term.sin_article"
    ,"termTaxonomies.term"."plu_article" AS "termTaxonomies.term.plu_article"
    ,"termTaxonomies.taxonomy"."id" AS "termTaxonomies.taxonomy.id"
    ,"images"."id" AS "images.id"
    ,"images"."deal_id" AS "images.deal_id"
    ,"images"."image" AS "images.image"
    ,"supplier"."id" AS "supplier.id"
    ,"supplier"."name" AS "supplier.name"
    ,"supplier"."url" AS "supplier.url"
    ,"supplier"."logo" AS "supplier.logo"
    ,"supplier"."clicks" AS "supplier.clicks"
    ,"supplier"."order" AS "supplier.order"
FROM (
    SELECT "product"."id"
        ,"product"."name"
        ,"product"."subtitle"
        ,"product"."url"
        ,"product"."prod_specs"
        ,"product"."prod_desc"
        ,"product"."supplier_id"
        ,"product"."created_at"
        ,"product"."updated_at"
        ,"product"."active"
    FROM "products" AS "product"
    ORDER BY "product"."id" DESC LIMIT 10
    ) AS "product"
LEFT JOIN (
    "term_relationships" AS "termTaxonomies.termRelationships" LEFT JOIN "term_taxonomies" AS "termTaxonomies" ON "termTaxonomies"."id" = "termTaxonomies.termRelationships"."term_taxonomy_id"
    ) ON "product"."id" = "termTaxonomies.termRelationships"."product_id"
    AND termRelationships.product_id IS NULL
LEFT JOIN "terms" AS "termTaxonomies.term" ON "termTaxonomies"."term_id" = "termTaxonomies.term"."id"
LEFT JOIN "taxonomies" AS "termTaxonomies.taxonomy" ON "termTaxonomies"."taxonomy_id" = "termTaxonomies.taxonomy"."id"
LEFT JOIN "images" AS "images" ON "product"."id" = "images"."deal_id"
LEFT JOIN "suppliers" AS "supplier" ON "product"."supplier_id" = "supplier"."id"
ORDER BY "product"."id" DESC;

检查倒数第 6 行 ( AND termRelationships.product_id IS NULL )。

本案例的表格:

我试图通过他们的供应商和报价获得所有产品，这些产品尚未分类(因此目前不在 termTaxonomies 内)。

使用 sql 查询很容易做到这一点，但现在我们使用的是 ORM(Sequelize)，我们希望完全使用它。谁能帮助我们？

猜想在下面发布我的所有模型有点太多了，所以我会尽量保持简短:

联想产品型号:

product.hasMany(_models.offer, {
    foreignKey: 'product_id'
});

                product.belongsToMany(_models.termTaxonomies, {
                    through: _models.termRelationships,
                    foreignKey: 'product_id'
                });

                product.hasMany(_models.image, {
                    foreignKey: 'deal_id'
                });

                product.belongsTo(_models.supplier, {
                    foreignKey: 'supplier_id'
                });

协会优惠模式:

offer.belongsTo(_models.product, {
                    foreignKey: 'product_id'
                });

                offer.hasMany(_models.sentDeals, {
                    foreignKey: 'offer_id'
                });

                offer.hasMany(_models.transaction, {
                  foreignKey: 'offer_id'
                });

协会供应商模式:

supplier.hasMany(_models.product, {
                    foreignKey: 'supplier_id'
                });
                supplier.hasMany(_models.scraperLog, {
                    foreignKey: 'scraper_id'
                });

关联术语分类模型:

termTaxonomies.belongsToMany(_models.product, {
                    through: _models.termRelationships,
                    foreignKey: 'term_taxonomy_id',
                });

                termTaxonomies.belongsTo(_models.term, {
                    foreignKey: 'term_id',
                });

                termTaxonomies.belongsTo(_models.taxonomy, {
                    foreignKey: 'taxonomy_id',
                });

Answer 1

和 是 LEFT JOIN 的延续:-

左加入 (
"term_relationships"AS "termTaxonomies.termRelationships"LEFT JOIN "term_taxonomies"AS "termTaxonomies"ON "termTaxonomies"."id"= "termTaxonomies.termRelationships"."term_taxonomies_id"
) ON "product"."id"= "termTaxonomies.termRelationships"."product_id"
AND termRelationships.product_id 为空