mysql - Excluding foreign key attributes causes nested queries to fail -

on models have created , updated foreign key columns link user model. when query database using findall() example, include them user's username , other details returned results - exclude foreign key columns result @ makes no sense returning created object includes id , and createdbyid node it's duplicate data

"createdbyid": "21499bb6-4476-11e6-beb8-9e71128cae77", "createdby": {     "id": "21499bb6-4476-11e6-beb8-9e71128cae77",     "username": "mo.gusbi" }, "updatedbyid": "21499bb6-4476-11e6-beb8-9e71128cae77", "updatedby": {     "id": "21499bb6-4476-11e6-beb8-9e71128cae77",     "username": "mo.gusbi" } 

so exclude createdbyid , updatedbyid attributes , works great, until have join other tables , include constraints (for pagination, cause subquery generated) looks createdbyid , updatedbyid columns excluded, causing sql error

sequelizedatabaseerror: sqlite_error: no such column: category.createdbyid 

from generated query

select `category`.*, `children`.`id` `children.id`,   `children`.`name` `children.name`,   `createdby`.`id` `createdby.id`,   `createdby`.`username` `createdby.username`,   `updatedby`.`id` `updatedby.id`,   `updatedby`.`username` `updatedby.username`,   `deletedby`.`id` `deletedby.id`,   `deletedby`.`username` `deletedby.username` (   select `category`.`id`,     `category`.`name`,     `category`.`parentid`   `categories` `category`   (`category`.`deletedat` null     , `category`.`parentid` = null)   limit 0, 10 ) `category` left outer join `categories` `children`   on `category`.`id` = `children`.`parentid`     , `children`.`deletedat` null left outer join `users` `createdby`   on `category`.`createdbyid` = `createdby`.`id`     , `createdby`.`deletedat` null left outer join `users` `updatedby`   on `category`.`updatedbyid` = `updatedby`.`id`     , `updatedby`.`deletedat` null left outer join `users` `deletedby`   on `category`.`deletedbyid` = `deletedby`.`id`     , `deletedby`.`deletedat` null ; 

models

user = sequelize.define('user', {   id: {     type: datatypes.uuid,     defaultvalue: datatypes.uuidv1,     primarykey: true   },   username: {     type: datatypes.string,     unique: true,     allownull: false,     validate: {       notempty: true,       is: regex.username     }   } }, {   paranoid: true,   classmethods: {     associate: (models) => {       user.belongsto(models.user, {         foreignkey: 'createdbyid',         as: 'createdby'       });        user.belongsto(models.user, {         foreignkey: 'updatedbyid',         as: 'updatedby'       });        user.belongsto(models.user, {         foreignkey: 'deletedbyid',         as: 'deletedby'       });     }   } });  category = sequelize.define('category', {   id: {     type: datatypes.uuid,     defaultvalue: datatypes.uuidv1,     primarykey: true   },   name: {     type: datatypes.string,     allownull: false,     validate: {       notempty: true     }   } }, {   indexes: [{     unique: true,     fields: ['name', 'parentid']   }],   paranoid: true,   classmethods: {     associate: (models: any) => {       category.belongsto(models.user, {         foreignkey: 'createdbyid',         as: 'createdby'       });        category.belongsto(models.user, {         foreignkey: 'updatedbyid',         as: 'updatedby'       });        category.belongsto(models.user, {         foreignkey: 'deletedbyid',         as: 'deletedby'       });        category.belongsto(models.category, {         foreignkey: 'parentid',         as: 'parent'       });        category.hasmany(models.category, {         foreignkey: 'parentid',         as: 'children'       });     }   } }); 

query

category   .findall({     attributes: [       'id',       'name',       'parentid'     ],     where: {       parentid: req.query.parentid     },     include: [       {         model: category,         as: 'children',         attributes: [           'id',           'name'         ]       },       {         model: user,         as: 'createdby',         attributes: [           'id',           'username'         ]       },       {         model: user,         as: 'updatedby',         attributes: [           'id',           'username'         ]       },       {         model: user,         as: 'deletedby',         attributes: [           'id',           'username'         ]       }     ],     offset: 0,     limit: 10 });