postgresql - Sequelize 和 2 个连接表，麻烦

Question

我们有问题。我们不能使用 sequelize 在下面的结构上执行 SQL SELECT。

这是我们的结构，见下图。

我们正在尝试执行这个 sequelize 查询:

query = {
    'attributes': [models.sequelize.fn('count', models.sequelize.col('sent_deals.user_id'))],


    order: [['alert.keywords', ' DESC']],
    attributes: ['alert.keywords'],
    include: [
        {
            model: models.alert,
            attributes: ['keywords']
        }, {
            where: {
                'products.sent_deal.created_at': {
                    between: [moment(start).startOf('day').format('YYYY-MM-DD HH:mm'), moment(end).endOf('day').format('YYYY-MM-DD HH:mm')]
                }
            },
            model: models.product,
            attributes: ['id']
        }
    ]
};



models.userAlerts.findAll(query)

但是随后我们收到以下错误: "error: missing FROM-clause entry for table "alert"" ，因为我们试图在 alert.keywords 之外也选择属性 include[] ，但它在 Sequelize JS 的先前版本中仍然有效!现在我们不能再按警报订购了...... :( 由于“属于”连接，它总是返回一个警报，所以理论上它必须工作。

我的猜测是因为查询不执行直接连接，而是在子查询上执行 SELECT FROM，见下文:

SELECT "userAlerts".*
    ,"alert"."id" AS "alert.id"
    ,"alert"."keywords" AS "alert.keywords"
    ,"products"."id" AS "products.id"
    ,"products.sent_deal"."user_alert_id" AS "products.sent_deal.user_alert_id"
    ,"products.sent_deal"."deal_id" AS "products.sent_deal.deal_id"
    ,"products.sent_deal"."created_at" AS "products.sent_deal.created_at"
FROM (
    SELECT "userAlerts"."id"
        ,"userAlerts"."user_id"
        ,"userAlerts"."alert_id"
        ,"userAlerts"."activationToken"
        ,"userAlerts"."activatedAt"
        ,"userAlerts"."createdAt"
        ,"userAlerts"."updatedAt"
    FROM "user_alerts" AS "userAlerts"
    WHERE (
            SELECT "products.sent_deal"."user_alert_id"
            FROM "sent_deals" AS "products.sent_deal"
            INNER JOIN "deals" AS "products" ON "products"."id" = "products.sent_deal"."deal_id"
            WHERE "userAlerts"."id" = "products.sent_deal"."user_alert_id"
                AND (
                    "products.sent_deal"."created_at" BETWEEN '2014-11-17 00:00'
                        AND '2014-12-02 23:59'
                    ) LIMIT 1
            ) IS NOT NULL
    ORDER BY "alert"."keywords" DESC LIMIT 1
    ) AS "userAlerts"
LEFT JOIN "alerts" AS "alert" ON "alert"."id" = "userAlerts"."alert_id"
INNER JOIN (
    "sent_deals" AS "products.sent_deal" INNER JOIN "deals" AS "products" ON "products"."id" = "products.sent_deal"."deal_id"
    ) ON "userAlerts"."id" = "products.sent_deal"."user_alert_id"
    AND (
        "products.sent_deal"."created_at" BETWEEN '2014-11-17 00:00'
            AND '2014-12-02 23:59'
        )
ORDER BY "alert"."keywords" DESC

Answer 1

在include上有keywords属性，应该就够了，不需要在外层查询中添加

attributes: [models.sequelize.fn('count', models.sequelize.col('sent_deals.user_id'))],


order: [['alert.keywords', ' DESC']],
attributes: ['alert.keywords'], <-- This line is superflous
include: [
    {
        model: models.alert,
        attributes: ['keywords']
    }, {
        where: {
            'products.sent_deal.created_at': { 
                between: [moment(start).startOf('day').format('YYYY-MM-DD HH:mm'), moment(end).endOf('day').format('YYYY-MM-DD HH:mm')]
            }
        },
        model: models.product,
        attributes: ['id']
    }
]

您不应该包括 SentDeal 以查询 products.sent_deal 吗？这个问题可能更适合于 Sequelize GH 的问题