New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

DatabaseError: Column does not exist when querying with nested include and where #6400

Closed
felixfbecker opened this Issue Aug 9, 2016 · 10 comments

Comments

4 participants
@felixfbecker
Contributor

felixfbecker commented Aug 9, 2016

What you are doing?

Post a minimal code sample that reproduces the issue, including models and associations

const Mail = sequelize.define('Mail', {
    subject: Sequelize.TEXT,
    content: Sequelize.TEXT
}, {
    paranoid: true
});

const MailboxEntry = sequelize.define('MailboxEntry', {
    movedToTrashAt: {
        type: Sequelize.DATE,
        allowNull: true
    },
    read: {
        type: Sequelize.BOOLEAN,
        allowNull: false,
        defaultValue: false
    }
}, {
    paranoid: true
});

const User = sequelize.define('User', {
    username: {
        type: Sequelize.TEXT,
        unique: true,
        allowNull: false,
    },
    firstName: {
        type: Sequelize.TEXT,
        allowNull: false
    },
    lastName: {
        type: Sequelize.TEXT,
        allowNull: false
    }
})

Mail.belongsTo(User, {
    as: 'sender',
    foreignKey: {
        name: 'senderId',
        allowNull: false
    }
});
Mail.belongsToMany(User, {
    as: 'recipients',
    through: 'MailRecipients',
    otherKey: {
        name: 'recipientId',
        allowNull: false
    },
    foreignKey: {
        name: 'mailId',
        allowNull: false
    }
});

Mail.hasMany(MailboxEntry, {
    as: 'mailboxEntries',
    foreignKey: {
        name: 'mailId',
        allowNull: false
    }
});

MailboxEntry.belongsTo(User, {
    as: 'owner',
    foreignKey: {
        name: 'ownerId',
        allowNull: false
    }
});

MailboxEntry.belongsTo(Mail, {
    as: 'mail',
    foreignKey: {
        name: 'mailId',
        allowNull: false
    }
});

const userId = 1;

sequelize.sync().then(() => MailboxEntry.findAndCount({
        offset: 0,
        limit: 10,
        order: [['createdAt', 'DESC']],
        include: [
            {
                association: MailboxEntry.associations.mail,
                attributes: { exclude: ['content'] },
                include: [
                    {
                        association: Mail.associations.recipients,
                        attributes: ['id', 'username', 'firstName', 'lastName'],
                        through: {
                            where: {
                                recipientId: userId
                            }
                        },
                        required: true
                    }
                ],
                required: true
            }
        ],
        where: {
            ownerId: userId,
            movedToTrashAt: null
        },
        attributes: { exclude: ['movedToTrashAt'] }
    }).then(result => console.log(result), err => console.error(err));

What do you expect to happen?

Getting back a list of mailbox entries with included mail (content) with included recipients, that belong to a user (ownerId), where the user is the recipient and that are not moved to trash.

What is actually happening?

SequelizeDatabaseError: Column Mail.mailId does not exist

Executed query:

SELECT "MailboxEntry".*,
       "mail.recipients"."id" AS "mail.recipients.id",
       "mail.recipients"."username" AS "mail.recipients.username",
       "mail.recipients"."firstName" AS "mail.recipients.firstName",
       "mail.recipients"."lastName" AS "mail.recipients.lastName",
       "mail.recipients.MailRecipients"."createdAt" AS "mail.recipients.MailRecipients.createdAt",
       "mail.recipients.MailRecipients"."updatedAt" AS "mail.recipients.MailRecipients.updatedAt",
       "mail.recipients.MailRecipients"."mailId" AS "mail.recipients.MailRecipients.mailId",
       "mail.recipients.MailRecipients"."recipientId" AS "mail.recipients.MailRecipients.recipientId"
FROM
  (SELECT "MailboxEntry"."id",
          "MailboxEntry"."read",
          "MailboxEntry"."createdAt",
          "MailboxEntry"."updatedAt",
          "MailboxEntry"."deletedAt",
          "MailboxEntry"."ownerId",
          "MailboxEntry"."mailId",
          "mail"."id" AS "mail.id",
          "mail"."subject" AS "mail.subject",
          "mail"."createdAt" AS "mail.createdAt",
          "mail"."updatedAt" AS "mail.updatedAt",
          "mail"."deletedAt" AS "mail.deletedAt",
          "mail"."senderId" AS "mail.senderId"
   FROM "MailboxEntries" AS "MailboxEntry"
   INNER JOIN "Mails" AS "mail" ON "MailboxEntry"."mailId" = "mail"."id"
   AND "mail"."deletedAt" IS NULL
   WHERE ("MailboxEntry"."deletedAt" IS NULL
          AND ("MailboxEntry"."ownerId" = '1'
               AND "MailboxEntry"."movedToTrashAt" IS NULL))
     AND
       (SELECT "Mail"."id"
        FROM "Mails" AS "Mail"
        INNER JOIN ( "MailRecipients" AS "recipients.MailRecipients"
                    INNER JOIN "Users" AS "recipients" ON "recipients"."id" = "recipients.MailRecipients"."recipientId"
                    AND "recipients.MailRecipients"."recipientId" = '1') ON "Mail"."id" = "recipients.MailRecipients"."mailId"
        AND "recipients"."deletedAt" IS NULL
        WHERE "MailboxEntry"."id" = "Mail"."mailId" LIMIT 1) IS NOT NULL
   ORDER BY "MailboxEntry"."createdAt" DESC LIMIT 10
   OFFSET 0) AS "MailboxEntry"
INNER JOIN ("MailRecipients" AS "mail.recipients.MailRecipients"
            INNER JOIN "Users" AS "mail.recipients" ON "mail.recipients"."id" = "mail.recipients.MailRecipients"."recipientId"
            AND "mail.recipients.MailRecipients"."recipientId" = '1') ON "mail.id" = "mail.recipients.MailRecipients"."mailId"
AND "mail.recipients"."deletedAt" IS NULL
ORDER BY "MailboxEntry"."createdAt" DESC;

Dialect: postgres
Database version: 9.5.2
Sequelize version: 3.23.3

@felixfbecker felixfbecker changed the title from Column does not exist when querying with nested include and where to DatabaseError: Column does not exist when querying with nested include and where Aug 9, 2016

@felixfbecker

This comment has been minimized.

Contributor

felixfbecker commented Aug 9, 2016

Commenting out the recipients inclusion makes the query work, but it will of course return all mailbox entries, not only where the user is the recipient

@felixfbecker

This comment has been minimized.

Contributor

felixfbecker commented Aug 9, 2016

Including just the sender works. But as soon as recipients is included, unknown column error occur

@felixfbecker

This comment has been minimized.

Contributor

felixfbecker commented Aug 10, 2016

@mickhansen help please 🆘

@felixfbecker

This comment has been minimized.

Contributor

felixfbecker commented Aug 11, 2016

The problem is here:

        WHERE "MailboxEntry"."id" = "Mail"."mailId" LIMIT 1) IS NOT NULL

it should be the other way around. It seems like sequelize swappes the columns by mistake.
I would try to fix this but I have no idea where to look for.

@mlegenhausen

This comment has been minimized.

Contributor

mlegenhausen commented Aug 30, 2016

Same problem in combination of belongsTo and then referencing a belongsToMany relation.

@felixfbecker felixfbecker added the bug label Aug 30, 2016

@willdawsonme

This comment has been minimized.

willdawsonme commented Sep 19, 2016

Problem seems to be in lib/dialects/abstract/query-generator.js around line 1285. Only happens for me when using limit with a nested include where. Sequelize is assuming the top-level include is a hasMany association, when in fact it is a belongsTo association, hence why it is "swapping" the primary/foreign key WHERE clause.

@felixfbecker

This comment has been minimized.

Contributor

felixfbecker commented Sep 21, 2016

@willdawsonme I would applaud you if you could submit the fix in a PR.

@willdawsonme

This comment has been minimized.

willdawsonme commented Sep 25, 2016

@felixfbecker Would that require probing the nature of the relation to determine which primary/foreignKey attributes to use where, or would it always be a belongsTo association? I can look into it further if you're unsure.

@felixfbecker

This comment has been minimized.

Contributor

felixfbecker commented Sep 26, 2016

I would start by adding a failing test case to experiment when the bug occurs and then fixing it

@jlevesque

This comment has been minimized.

jlevesque commented Sep 24, 2018

I still have the issue in v4.38...
@felixfbecker how is it possible? Or am I doing something wrong?

My association:

User.belongsTo(Role, {foreignKey: 'roleId', as: 'role'});

My query :

User.findById(req.params.id, {
        include: [{ model: Role, as: 'role' }]
    }).then(response => res.status(200).json({
        error: false,
        data: response,
    })).catch(error => res.json({
        error: true,
        data: error
    }));

I get :
... LEFT OUTER JOIN Roles AS role ON User.id = role.roleId WHERE User.id = '1';"

It's suppose to be :
... LEFT OUTER JOIN Roles AS role ON User.roleId = role.id WHERE User.id = '1';"

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment