首页 文章

如何使用Sequelize查询匹配“where”条件的关联表

提问于
浏览 次
0

我正在尝试使用sequelize检索属于多个集合的产品 . 方言是Postgres，我正在使用Sequelize 4.15.1

我有一个Product模型，一个Collection模型和一个关联模型CollectionProduct . 产品属于许多集合和集合属于许多产品 .

产品型号：

const Product = sequelize.define( 'Product', {
    id: {
      type: DataTypes.STRING,
      primaryKey: true
    },
    name: DataTypes.STRING,
  } );
  Product.associate = function( models ) {
    Product.belongsToMany( models.Collection, {
      through: models.CollectionProduct
    } );
  }

收集模型：

const Collection = sequelize.define( 'Collection', {
  name: {
    type: Sequelize.STRING
  },
  slug: {
    type: Sequelize.STRING
  },
} );
Collection.associate = function( models ) {
  Collection.belongsToMany( models.Product, {
    through: models.CollectionProduct
  } );
}

这是我的续集查询

models.Product.findAll( {
  attributes: ["id", "name"],
  include: [ {
    model: models.Collection,
    attributes: ["id", "name", "slug"],
    where: {
      slug: {
        [Op.like]: { [Op.all]: ['collection-a', 'collection-b']}
      }
    }
  } ]
} ).then( products => {
  console.log( products )
} ).catch( function ( err ) {
  console.log( err );
} );

这是生成的postgres查询

SELECT "Product"."id",
       "Product"."name",
       "Collections"."id" AS "Collections.id",
       "Collections"."name" AS "Collections.name",
       "Collections"."slug" AS "Collections.slug",
       "Collections->CollectionProduct"."createdAt" AS "Collections.CollectionProduct.createdAt",
       "Collections->CollectionProduct"."updatedAt" AS "Collections.CollectionProduct.updatedAt",
       "Collections->CollectionProduct"."CollectionId" AS "Collections.CollectionProduct.CollectionId",
       "Collections->CollectionProduct"."ProductId" AS "Collections.CollectionProduct.ProductId"
FROM "Products" AS "Product"
INNER JOIN ("CollectionProducts" AS "Collections->CollectionProduct"
            INNER JOIN "Collections" AS "Collections" ON "Collections"."id" = "Collections->CollectionProduct"."CollectionId") ON "Product"."id" = "Collections->CollectionProduct"."ProductId"
AND "Collections"."slug" LIKE ALL (ARRAY['collection-a',
                                         'collection-b']);

我想退回属于A和B集合的产品 .

不幸的是，查询执行不会返回任何结果 .

node.js postgresql sequelize.js

1 回答

  • 0

    这可能会成功 . grab 所有productid在集合之间相交 - 一个id和集合-b id .

    select
  t1.slug as slug_ca,
  t2.slug as slug_cb,
  t1.productid
from
(
  select
    p3.slug,
    p1.id as productid
  from products p1
       inner join collectionproducts p2
         on p1.id = p2.ProductId
       inner join collections p3
         on p2.CollectionId = p3.id
  where lower(p3.slug) like '%collection-a%'
  group by 1,2
) t1
inner join
(
  select
    p3.slug,
    p1.id as productid
  from products p1
       inner join collectionproducts p2
         on p1.id = p2.ProductId
       inner join collections p3
         on p2.CollectionId = p3.id
  where lower(p3.slug) like '%collection-b%'
  group by 1,2
) t2
  on t1.productid = t2.productid
group by 1,2,3;
    回复于

相关问题