I've come across this problem several times in the past and have read a number of solutions. Before I go through them again, I was hoping someone could explain exactly what the problem is - because despite eventually fixing it, I never really understand why

The error:

original: Error: Unknown column 'job.createdAt' in 'field list'

What I assume is the source (as I just added it):

[Sequelize.fn('date_format', Sequelize.col('job.createdAt' ), '%d/%m/%y'), 'jobDate']

The error suggests that createdAt isn't a column in the jobs table - several solutions suggest defining createdAt in your model (which I'd already done).

What also confuses me is that in workbench I can run the query: SELECT * FROM test.jobs ORDER BY test.jobs.createdAt; and it works, and that I don't have this problem with the equivalent Company attribute.

Job Model:

const Job = sequelize.define('job', {
    id: {
        type: Sequelize.INTEGER,
        autoIncrement: true,
        allowNull: false,
        primaryKey: true
    },
    title: {
        type: Sequelize.STRING,
        allowNull: false
    },

    ...

    createdAt: {
        type: Sequelize.DATE(3),
        allowNull: false,
    },
    updatedAt: {
        type: Sequelize.DATE(3),
        allowNull: false,
    }
});

The options object for the query:

const options = {
    include: [
        {
            model: Job,
            attributes: [
                'id',
                'title',
                // ...
                'createdAt',
                [Sequelize.fn('date_format', Sequelize.col('job.createdAt' ), '%d/%m/%y'), 'jobDate'],

            ]
        },
    ],
    attributes: [ 
        'id', 
        'name',
        'createdAt',
        [Sequelize.fn('date_format', Sequelize.col('company.createdAt' ), '%d/%m/%y'), 'companyDate'],
    ],
    order: [[ orderField, orderDirection ]],
    distinct: true
}

Workbench showing the column:

As far as I can tell the relevant part(?) of the actual sql query looks right too

`jobs`.`createdAt` AS `jobs.createdAt`, date_format(`job`.`createdAt`, '%d/%m/%y') AS `jobs.jobDate`,

Appreciate any help