postgresql - 如何在 Sequelize 中查询无关联的 INNER JOIN？

Question

我可以在 Sequelize 中进行自我内连接查询吗？

我有一个模型订购

ordering.js

sequelize.define('ordering', {
    id: {
      allowNull: true,
      primaryKey: true,
      type: DataTypes.STRING,
    },
    deliveryData: {
      type: DataTypes.JSONB,
    }
}, {
    schema: 'business'
  })

我想找出重复的（JSONB 字段）行，

但我找不到如何在没有关联的情况下进行内部加入

试：

Ordering.findAll({
  include: [{
    model: Ordering,
    attributes: [
      [sequelize.json('deliveryData.receiver.name'), 'name'],
      [sequelize.json('deliveryData.receiver.address'), 'address'],
      [sequelize.fn("COUNT", sequelize.col("*")), 'num']
    ],
    group: [sequelize.json('deliveryData.receiver.name'), sequelize.json('deliveryData.receiver.address')],
    having: sequelize.literal('COUNT(*) > 1'),
    on: {
      name: sequelize.json('deliveryData.receiver.name'),
      address: sequelize.json('deliveryData.receiver.address')
    }
  }]
})

结果：

Unhandled rejection SequelizeEagerLoadingError: ordering is not associated to ordering!

我可以通过 INNER JOIN 子查询

const Ordering = DB['ordering']

Ordering.findAll({
  attributes: [
    [sequelize.json('deliveryData.receiver.name'), 'name'],
    [sequelize.json('deliveryData.receiver.address'), 'address'],
    [sequelize.fn("COUNT", sequelize.col("*")), 'num']
    // include: [[sequelize.fn("COUNT", sequelize.col("name")), "test"]]
  ],
  group: [sequelize.json('deliveryData.receiver.name'), sequelize.json('deliveryData.receiver.address')],
  having: sequelize.literal('COUNT(*) > 1')
})

预期结果是

    SELECT id, "deliveryData"->'receiver'->>'name', "deliveryData"->'receiver'->>'address' FROM "business"."orderings" AS ordering
    INNER JOIN
    (
     SELECT "deliveryData"->'receiver'->>'name' as name, "deliveryData"->'receiver'->>'address' as address, COUNT(*)
     FROM "business"."orderings"
     GROUP BY "deliveryData"->'receiver'->>'name', "deliveryData"->'receiver'->>'address'
     HAVING COUNT(*) > 1
    ) od ON ordering."deliveryData"->'receiver'->>'address' = od."address" AND ordering."deliveryData"->'receiver'->>'name' = od."name"

标签： postgresqlsequelize.js