Missing FROM-clause using include and .col()

[Verdier]

Since sequelize 3.5, the following:

User.findAll({
    where: {
        receivedLastOperationNotificationAt: {
            $lte: sixDaysAgo
        }
    },
    include: [{
        model: Operation,
        where: {
            type: 'bank',
            validated: false,
            createdAt: {
                $gte: sails.sequelize.col('user.receivedLastOperationNotificationAt')
            }
        },
        limit: 1
    }]
});

results in two queries:

SELECT
  "user"."id",
  (...)
FROM "users" AS "user"
WHERE "user"."receivedLastOperationNotificationAt" <= '2015-08-07 11:16:16.613 +00:00';

and:

SELECT
  "operation".*,
  "transaction"."id"              AS "transaction.id",
  (...)
FROM ((SELECT
         "id",
         "type",
         (...)
       FROM "operations" AS "operation"
       WHERE (("operation"."type" = 'bank' AND "operation"."validated" = FALSE AND
               "operation"."createdAt" >= "user"."receivedLastOperationNotificationAt")) AND
             "operation"."ownerId" = 3
       LIMIT 1)
       AS "operation" LEFT OUTER JOIN "transactions" AS "operations.transaction"
       ON "operations.transactionId" = "operations.transaction"."id";

But in the second query, user is missing in from clause:

SequelizeDatabaseError: missing FROM-clause entry for table "user"

[janmeier]

3.5 introduce include.separate which will split your query into multiple parts, if the include contains a limit, beacuse the limit is easier to do in two separate queries.

However, in this case it doesn't seem like a good idea... But we'd need a better way to know that you're referring to the main table .. Its hard to know when its just a string :). Slightly related to #3095

You could set separate: false in the include, then it will use a single query - but I'm not sure the limit actually works then? @mickhansen

[Verdier]

Perfect ! The limit is just to ensure that the selected user as at least one operation which respect the condition (I do not need the operation at all, just the condition, that's just for performance, probably attributes: [] is better...).

I'm surprised that there is two queries, because the first one select all the users, and the final filtering is based on the result of the second query.

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. If this is still an issue, just leave a comment 🙂