CODEWITHSUNDEEP
javascriptsequelize.js
NickW
NickW

Reputation: 1343

Sequelize: DatabaseError - Unknown column in 'order clause'

TLDR:

The inclusion of the order direction 'ASC' or 'DESC' property causes the error Unknown column 'application.ASC' in 'order clause'

const applications = await Application.findAll({
    order: ['createdAt', 'ASC'],
    distinct: true,
    attributes: [ 
        'id',
        'applicantId',
        'jobId',
    ]
});

Original:

I've been getting this error and I can't quite work out why. I assumed it was a naming problem with the 'createdAt' field, but it seems to be the 'ASC'/'DESC' property that the error is pointing to that's causing the problem

This works:

const applications = await Application.findAll({
    order: ['createdAt'],      
    distinct: true,
    attributes: [ 
        'id',
        'applicantId',
        'jobId',
    ]
});

SQL query being run:

SELECT `id`, `applicantId`, `jobId` FROM `applications` AS `application` ORDER BY `application`.`createdAt`;

But this doesn't: (adding 'ASC'/'DESC' to the order property: order: ['createdAt', 'ASC'],)

const applications = await Application.findAll({
    order: ['createdAt', 'ASC'],      
    distinct: true,
    attributes: [ 
        'id',
        'applicantId',
        'jobId',
    ]
});

SQL:

SELECT `id`, `applicantId`, `jobId` FROM `applications` AS `application` ORDER BY `application`.`createdAt`, `application`.`ASC`;

Resulting in: Unknown column 'application.ASC' in 'order clause'

I don't know why it seems to be treating 'ASC' as a column? I think I'm following the correct syntax, described here

This is my model:

const Application = sequelize.define('application', {
    id: {
        type: Sequelize.INTEGER,
        primaryKey: true,
        autoIncrement: true,
        notNull: true
    }
});

I thought explicitly adding the createdAt property to the model might make a difference to the query being run, or the error, but it doesn't:

const Application = sequelize.define('application', {
    id: {
        type: Sequelize.INTEGER,
        primaryKey: true,
        autoIncrement: true,
        notNull: true
    },
    createdAt: {
        type: Sequelize.DATE,
        field: 'created_at',
    }
});

This is the table in WorkBench:

Application table

Would appreciate input as I've got myself a little mixed up.

Thanks

Upvotes: 1

Views: 2186

Answers (1)

Anatoly
Anatoly

Reputation: 22783

If you need to indicate a direction of sorting then you need to use extended syntax of order option - an array of arrays with pairs - a column name and a sorting direction:

order: [['createdAt', 'ASC']],

Upvotes: 4

Related Questions