Sequelize returns "Unknown column" when including tables

Question

The idea is to get numbers that as associated with jobs on the call server. Each job has a bunch of numbers associated with it. When somebody calls back, I need to get the job associated with the caller ID.

The query that causes the issue:

    // Look for messages to play
    db.job.find( {
        'where': { 'status' : [ 'new', 'inprogress', 'done' ] },
//      'limit' : 1, // Obviously
        'order' : [[ 'jobID' , 'DESC' ]], // Latest
        'include' : [
                     { 
                         'model' : db.number,
                         'where' : { 'number' : this.number }
                     }
                     ]
    } ).complete( ... );

The database snippets:

var job = sequelize.define( 'job', {
    'id': {
        type: Sequelize.INTEGER( 10 ),
        field: 'job_id',
        allowNull: false,
        primaryKey: true,
        autoIncrement: true
    },
    'externalID' : {
        type:  Sequelize.STRING( 45 ),
        field: 'external_id',
        allowNull: false,
        unique: true,
        validate: {
            notEmpty: true
        }
    },
    'status' : {
        type:  Sequelize.STRING( 16 ),
        field: 'status',
        allowNull: false,
        defaultValue: 'processing'
    },
    'filename' : {
        type:  Sequelize.STRING( 64 ),
        field: 'filename',
        allowNull: true
    }
}, {
    'createdAt' : 'created',
    'updatedAt' : 'modified',
    'tableName' : 'jobs'
} );

var number = sequelize.define( 'number', {
    'id': {
        type: Sequelize.BIGINT( 20 ),
        field: 'number_id',
        allowNull: false,
        primaryKey: true,
        autoIncrement: true
    },
    'jobID': {
        type: Sequelize.INTEGER( 10 ),
        field: 'job_id',
        allowNull: false
    },
    'externalID' : {
        type:  Sequelize.STRING( 45 ),
        field: 'external_id',
        allowNull: false,
        unique: true,
        validate: {
            notEmpty: true
        }
    },
    'number' : {
        type:  Sequelize.STRING( 16 ),
        field: 'number',
        allowNull: false,
        validate: {
            notEmpty: true
        }
    },
    'extension' : {
        type:  Sequelize.STRING( 16 ),
        field: 'extension',
        allowNull: true
    },
    'status' : {
        type:  Sequelize.STRING( 16 ),
        field: 'status',
        allowNull: false,
        defaultValue: 'new'
    },
    'lastCall' : {
        type:  Sequelize.DATE,
        field: 'last_call',
        allowNull: true
    },
    'tries' : {
        type:  Sequelize.INTEGER,
        field: 'tries',
        allowNull: false,
        defaultValue: 0
    },
}, {
    'createdAt' : 'created',
    'updatedAt' : 'modified',
    'tableName' : 'numbers'
} );

job.hasMany( number, { 'foreignKey' : 'job_id' } );
number.belongsTo( job, { 'foreignKey' : 'job_id' } );

Somehow the query it generates is this:

SELECT `job`.*,
       `numbers`.`number_id` AS `numbers.id`,
       `numbers`.`job_id` AS `numbers.jobID`,
       `numbers`.`external_id` AS `numbers.externalID`,
       `numbers`.`number` AS `numbers.number`,
       `numbers`.`extension` AS `numbers.extension`,
       `numbers`.`status` AS `numbers.status`,
       `numbers`.`last_call` AS `numbers.lastCall`,
       `numbers`.`tries` AS `numbers.tries`,
       `numbers`.`created` AS `numbers.created`,
       `numbers`.`modified` AS `numbers.modified`,
       `numbers`.`job_id` AS `numbers.job_id`
FROM
  (SELECT `job`.`job_id` AS `id`,
          `job`.`external_id` AS `externalID`,
          `job`.`status`,
          `job`.`filename`,
          `job`.`created`,
          `job`.`modified`
   FROM `jobs` AS `job`
   WHERE `job`.`status` IN ('new',
                            'inprogress',
                            'done')
     AND
       (SELECT `job_id`
        FROM `numbers` AS `numbers`
        WHERE `job`.`id` = `numbers`.`job_id`
          AND `numbers`.`number` IN ('5556656565') LIMIT 1) IS NOT NULL
   ORDER BY `job`.`jobID` DESC LIMIT 1) AS `job`
INNER JOIN `numbers` AS `numbers` ON `job`.`id` = `numbers`.`job_id`
AND `numbers`.`number` IN ('5556656565')
ORDER BY `job`.`jobID` DESC;

Which as you can see by the table structure is giving this error:

error: Callback: Error fetching job via number. SequelizeDatabaseError: ER_BAD_FIELD_ERROR: Unknown column 'job.id' in 'where clause'

Where have I gone wrong here?

Answer 1

There seems to be an open issue about it, actually.

https://github.com/sequelize/sequelize/issues/3403