I am using Sequelize ORM with mysql database for nodejs application. Somewhere I am stuck with one api whose job is just to show list based on the queryparam values. Issue is when I am not applying the pagination i.e. url is like “{{url}}/tasks?pagesize=&page=&start=2024-04-01&end=2024-05-13&task_status_id=&priority_id=&search=Dummy” with pagesize and page as empty string then “search” functionality is working fine. But when I add pagesize=10 and size=1 then it is throwing error. Error is as shown below.
original: Error: Unknown column 'customer.contact_person_name' in 'where clause'
at Packet.asError (xxxx/node_modules/mysql2/lib/packets/packet.js:728:17)
at Query.execute (xxxx/node_modules/mysql2/lib/commands/command.js:29:26)
at Connection.handlePacket (xxxx/node_modules/mysql2/lib/connection.js:481:34)
at PacketParser.onPacket (xxxx/node_modules/mysql2/lib/connection.js:97:12)
at PacketParser.executeStart (xxxx/node_modules/mysql2/lib/packet_parser.js:75:16)
at Socket.<anonymous> (xxxx/node_modules/mysql2/lib/connection.js:104:25)
at Socket.emit (node:events:514:28)
at addChunk (node:internal/streams/readable:545:12)
at readableAddChunkPushByteMode (node:internal/streams/readable:495:3)
at Readable.push (node:internal/streams/readable:375:5) {
code: 'ER_BAD_FIELD_ERROR',
errno: 1054,
sqlState: '42S22',
sqlMessage: "Unknown column 'customer.contact_person_name' in 'where clause'",
sql: "SELECT `tasks`.*, `customer`.`id` AS `customer.id`, `customer`.`organization_name` AS `customer.organization_name`, `customer`.`address` AS `customer.address`, `customer`.`latitude` AS `customer.latitude`, `customer`.`longitude` AS `customer.longitude`, `customer`.`contact_person_name` AS `customer.contact_person_name`, `customer`.`contact_person_email` AS `customer.contact_person_email`, `customer`.`contact_person_phone` AS `customer.contact_person_phone`, `customer`.`contact_person_alternate_phone` AS `customer.contact_person_alternate_phone`, `customer`.`fence_id` AS `customer.fence_id`, `customer`.`country_id` AS `customer.country_id`, `customer`.`state_id` AS `customer.state_id`, `customer`.`status` AS `customer.status`, `fence`.`id` AS `fence.id`, `fence`.`title` AS `fence.title`, `fence`.`area` AS `fence.area`, `fence`.`radius` AS `fence.radius`, `employee`.`id` AS `employee.id`, `employee`.`name` AS `employee.name`, `employee`.`personal_email` AS `employee.personal_email`, `task_status`.`id` AS `task_status.id`, `task_status`.`name` AS `task_status.name`, `task_status`.`color_code` AS `task_status.color_code`, `priority`.`id` AS `priority.id`, `priority`.`name` AS `priority.name`, `priority`.`color_code` AS `priority.color_code`, `task_documents`.`id` AS `task_documents.id`, `task_documents`.`task_id` AS `task_documents.task_id`, `task_documents`.`employee_id` AS `task_documents.employee_id`, `task_documents`.`document_type` AS `task_documents.document_type`, `task_documents`.`media_id` AS `task_documents.media_id`, `task_documents->media`.`id` AS `task_documents.media.id`, `task_documents->media`.`name` AS `task_documents.media.name`, `task_documents->media`.`file_name` AS `task_documents.media.file_name`, `task_documents->media`.`mime_type` AS `task_documents.media.mime_type`, `task_documents->media`.`directory` AS `task_documents.media.directory`, `task_documents->media`.`media_size` AS `task_documents.media.media_size`, `task_documents->media`.`disk` AS `task_documents.media.disk`, `task_documents->document_category`.`id` AS `task_documents.document_category.id`, `task_documents->document_category`.`title` AS `task_documents.document_category.title`, `task_documents->document_category`.`prefix` AS `task_documents.document_category.prefix`, `task_documents->document_category`.`storage` AS `task_documents.document_category.storage` FROM (SELECT `tasks`.`id`, `tasks`.`task_custom_id`, `tasks`.`title`, `tasks`.`description`, `tasks`.`customer_id`, `tasks`.`fence_id`, `tasks`.`planned_date`, `tasks`.`task_status_id`, `tasks`.`priority_id`, `tasks`.`employee_id`, `tasks`.`otp_requirement`, `tasks`.`doc_requirement`, `tasks`.`start_of_service_time`, `tasks`.`end_of_service_time`, `tasks`.`created_at` FROM `tasks` AS `tasks` WHERE (`tasks`.`deleted_at` IS NULL AND (((`tasks`.`id` LIKE '%Dummy%' OR `tasks`.`task_custom_id` LIKE '%Dummy%' OR `tasks`.`title` LIKE '%Dummy%' OR `tasks`.`description` LIKE '%Dummy%' OR `tasks`.`customer_id` LIKE '%Dummy%' OR `tasks`.`fence_id` LIKE '%Dummy%' OR `tasks`.`planned_date` LIKE 'Invalid date' OR `tasks`.`task_status_id` LIKE '%Dummy%' OR `tasks`.`priority_id` LIKE '%Dummy%' OR `tasks`.`employee_id` LIKE '%Dummy%' OR `tasks`.`otp_requirement` LIKE '%Dummy%' OR `tasks`.`doc_requirement` LIKE '%Dummy%' OR `tasks`.`start_of_service_time` LIKE 'Invalid date' OR `tasks`.`end_of_service_time` LIKE 'Invalid date' OR `customer`.`contact_person_name` LIKE '%Dummy%' OR `customer`.`organization_name` LIKE '%Dummy%' OR `task_status`.`name` LIKE '%Dummy%' OR `priority`.`name` LIKE '%Dummy%') AND (`tasks`.`planned_date` > '2024-04-01 00:00:00' AND `tasks`.`planned_date` <= '2024-05-14 00:00:00')))) ORDER BY `tasks`.`planned_date` DESC LIMIT 0, 10) AS `tasks` LEFT OUTER JOIN `customers` AS `customer` ON `tasks`.`customer_id` = `customer`.`id` AND (`customer`.`deleted_at` IS NULL) LEFT OUTER JOIN `fences` AS `fence` ON `tasks`.`fence_id` = `fence`.`id` AND (`fence`.`deleted_at` IS NULL) LEFT OUTER JOIN `employees` AS `employee` ON `tasks`.`employee_id` = `employee`.`id` AND (`employee`.`deleted_at` IS NULL) LEFT OUTER JOIN `task_statuses` AS `task_status` ON `tasks`.`task_status_id` = `task_status`.`id` AND (`task_status`.`deleted_at` IS NULL) LEFT OUTER JOIN `priorities` AS `priority` ON `tasks`.`priority_id` = `priority`.`id` AND (`priority`.`deleted_at` IS NULL) LEFT OUTER JOIN `task_documents` AS `task_documents` ON `tasks`.`id` = `task_documents`.`task_id` AND (`task_documents`.`deleted_at` IS NULL) LEFT OUTER JOIN `medias` AS `task_documents->media` ON `task_documents`.`media_id` = `task_documents->media`.`id` AND (`task_documents->media`.`deleted_at` IS NULL) LEFT OUTER JOIN `document_categories` AS `task_documents->document_category` ON `task_documents`.`document_type` = `task_documents->document_category`.`id` AND (`task_documents->document_category`.`deleted_at` IS NULL) ORDER BY `tasks`.`planned_date` DESC;",
parameters: undefined
I am not getting where is the issue ?? Because without pagination it is working fine !
Below mentioned is the schema for table tasks, employees, task_statuses and priorities table
// Tasks Schema
"use strict";
const {
Model
} = require("sequelize");
module.exports = (sequelize, DataTypes) => {
class tasks extends Model {
static associate(models) {
tasks.belongsTo(models.employees, {
foreignKey: "employee_id"
});
tasks.belongsTo(models.customers, {
foreignKey: "customer_id"
});
tasks.belongsTo(models.fences, {
foreignKey: "fence_id"
});
tasks.belongsTo(models.task_statuses, {
foreignKey: "task_status_id"
});
tasks.belongsTo(models.priorities, {
foreignKey: "priority_id"
});
tasks.hasMany(models.task_documents, {
foreignKey: "task_id"
});
}
}
tasks.init({
task_custom_id: DataTypes.STRING,
title: DataTypes.STRING,
description: DataTypes.TEXT,
customer_id: DataTypes.BIGINT,
fence_id: DataTypes.BIGINT,
planned_date: DataTypes.DATE,
task_status_id: DataTypes.BIGINT,
priority_id: DataTypes.BIGINT,
employee_id: DataTypes.BIGINT,
otp_requirement: DataTypes.TINYINT,
doc_requirement: DataTypes.TINYINT,
start_of_service_time: DataTypes.DATE,
end_of_service_time: DataTypes.DATE
}, {
sequelize,
modelName: "tasks",
freezeTableName: true,
paranoid: true,
createdAt: "created_at",
updatedAt: "updated_at",
deletedAt: "deleted_at"
});
return tasks;
};
// Customer Table
"use strict";
const {
Model
} = require("sequelize");
module.exports = (sequelize, DataTypes) => {
class customers extends Model {
static associate(models) {
customers.hasMany(models.tasks, {
foreignKey: "customer_id"
});
customers.belongsTo(models.fences, {
foreignKey: "fence_id"
});
customers.belongsTo(models.countries, {
foreignKey: "country_id"
});
customers.belongsTo(models.states, {
foreignKey: "state_id"
});
}
}
customers.init({
organization_name: DataTypes.STRING,
address: DataTypes.STRING,
latitude: DataTypes.STRING,
longitude: DataTypes.STRING,
contact_person_name: DataTypes.STRING,
contact_person_email: DataTypes.STRING,
contact_person_phone: DataTypes.STRING,
contact_person_alternate_phone: DataTypes.STRING,
fence_id: DataTypes.BIGINT,
country_id: DataTypes.BIGINT,
state_id: DataTypes.BIGINT,
status: DataTypes.TINYINT
}, {
sequelize,
modelName: "customers",
freezeTableName: true,
paranoid: true,
createdAt: "created_at",
updatedAt: "updated_at",
deletedAt: "deleted_at"
});
return customers;
};
// Fence Table
"use strict";
const {
Model
} = require("sequelize");
module.exports = (sequelize, DataTypes) => {
class fences extends Model {
/**
* Helper method for defining associations.
* This method is not a part of Sequelize lifecycle.
* The `models/index` file will call this method automatically.
*/
static associate(models) {
// define association here
fences.hasMany(models.tasks, {
foreignKey: "fence_id"
});
fences.hasMany(models.customers, {
foreignKey: "fence_id"
});
fences.belongsToMany(models.employees, {
foreignKey: "fence_id",
through: models.employees_fences,
as: "employees"
});
}
}
fences.init({
title: DataTypes.STRING,
area: DataTypes.STRING,
radius: DataTypes.TEXT,
status: DataTypes.TINYINT
}, {
sequelize,
modelName: "fences",
freezeTableName: true,
paranoid: true,
createdAt: "created_at",
updatedAt: "updated_at",
deletedAt: "deleted_at"
});
return fences;
};
// Employee Table
"use strict";
const helper = require("../api/helper.js");
const utils = require("../api/utils");
const {
Model
} = require("sequelize");
module.exports = (sequelize, DataTypes) => {
class employees extends Model {
static associate(models) {
employees.belongsTo(models.users, {
foreignKey: "user_id"
});
employees.belongsTo(models.medias, {
foreignKey: "profile_img"
});
employees.belongsTo(models.addresses, {
as: "present_address",
foreignKey: "present_address_id"
});
employees.belongsTo(models.addresses, {
as: "permanent_address",
foreignKey: "permanent_address_id"
});
employees.belongsTo(models.designations, {
foreignKey: "designation_id"
});
employees.belongsTo(models.departments, {
foreignKey: "department_id"
});
employees.belongsTo(models.users, {
as: "reported_to",
foreignKey: "report_to"
});
employees.belongsTo(models.users, {
as: "create_by",
foreignKey: "created_by"
});
employees.belongsTo(models.users, {
as: "employeeType",
foreignKey: "user_id"
});
employees.belongsTo(models.users, {
as: "update_by",
foreignKey: "updated_by"
});
employees.belongsTo(models.users, {
as: "delete_by",
foreignKey: "deleted_by"
});
employees.hasMany(models.attendances, {
foreignKey: "employee_id"
});
employees.hasMany(models.tasks, {
foreignKey: "employee_id"
});
employees.belongsToMany(models.fences, {
foreignKey: "employee_id",
through: models.employees_fences,
as: "fences"
});
}
}
employees.init({
name: DataTypes.STRING,
personal_email: DataTypes.STRING,
company_email: DataTypes.STRING,
phone: DataTypes.STRING,
gender: DataTypes.ENUM(helper.elementsOfArr(utils.constants.genderEnum)),
date_of_birth: DataTypes.DATEONLY,
is_married: DataTypes.TINYINT,
profile_img: DataTypes.BIGINT,
//Address
present_address_id: DataTypes.BIGINT,
permanent_address_id: DataTypes.BIGINT,
is_same_address_flag: DataTypes.TINYINT,
// Employeement Details
designation_id: DataTypes.BIGINT,
department_id: DataTypes.BIGINT,
user_id: DataTypes.BIGINT,
status: DataTypes.TINYINT,
//Who altered
created_by: DataTypes.BIGINT,
updated_by: DataTypes.BIGINT,
deleted_by: DataTypes.BIGINT,
created_at: DataTypes.DATE,
updated_at: DataTypes.DATE,
deleted_at: DataTypes.DATE
}, {
sequelize,
modelName: "employees",
freezeTableName: true,
paranoid: true,
createdAt: "created_at",
updatedAt: "updated_at",
deletedAt: "deleted_at"
});
return employees;
};
my sequelize query looks as mentioned below
//Query for fetching all tasks
function checkEndStart(startD, endD, i18next){
let startDate = moment(startD, "YYYY-MM_DD").startOf("day");
let endDate =moment(endD, "YYYY-MM_DD").startOf("day");
//check the endDate and startDate
if(endDate.isBefore(startDate)) throw new Error(i18next("DATE_END_CANNOT_BE_AHEAD_OF_START"));
//add 1 date to endDate
endDate = endDate.add("1", "day");
return {
startDate,
endDate
};
}
function pagination(pagesize, page) {
const parseSize = parseInt(pagesize);
const parsePage = parseInt(page);
return {
"limit": (parseSize) ? parseSize : null,
"offset": (page) ? ( parsePage - 1) * parseSize : null,
};
}
function addSearchCondForAllCol(rawAttributes, search, Op) {
let condition = Object.keys(rawAttributes).filter(key => !["created_at", "updated_at", "deleted_at"].includes(key)).map(key => {
return { [key]: { [Op.like]: "%" + search + "%" } };
});
return condition;
},
getAllTasksForEmployee: async (pagesize, page, start, end, search, task_status_id, priority_id, t, employee_id) => {
try {
let condition = { };
const getPagination = pagination(pagesize, page);
let getDate = checkEndStart(start, end, t);
//any values exists for these fields then append it in the main condition object
if (employee_id) condition = { employee_id };
if(search) {
//build all conditions for every columns except created_at, updated_at and deleted_at
let conditionForAllCol = addSearchCondForAllCol(db.tasks.rawAttributes, search, sequelize.Op);
//adding extra condition as I also need to search based on the customer name too
conditionForAllCol = [
...conditionForAllCol,
sequelize.where(sequelize.col("customer.contact_person_name"), { [sequelize.Op.like]: "%" + search + "%" }),
sequelize.where(sequelize.col("customer.organization_name"), { [sequelize.Op.like]: "%" + search + "%" }),
sequelize.where(sequelize.col("task_status.name"), { [sequelize.Op.like]: "%" + search + "%" }),
sequelize.where(sequelize.col("priority.name"), { [sequelize.Op.like]: "%" + search + "%" }),
];
//building the final condition for the search feature
let searchCondition = {
[sequelize.Op.or]: conditionForAllCol
};
condition = { ...condition, ...searchCondition };
}
if(!(task_status_id === null || task_status_id === undefined || task_status_id === "")) condition = { ...condition, task_status_id };
if(!(priority_id === null || priority_id === undefined || priority_id === "")) condition = { ...condition, priority_id };
//final query
const instance = await db.tasks.findAll({
limit: getPagination.limit,
offset: getPagination.offset,
where: {
[sequelize.Op.and]: [
{
"planned_date": {
[sequelize.Op.gt]: getDate.startDate,
[sequelize.Op.lte]: getDate.endDate
},
...condition
}
]
},
order: [["planned_date", "DESC"]],
attributes: {
exclude: ["updated_at", "deleted_at"]
},
include: [
{
model: db.customers,
require: false,
attributes: {
exclude: ["created_at", "updated_at", "deleted_at"]
}
},
{
model: db.fences,
require: false,
attributes: {
exclude: ["created_at", "updated_at", "deleted_at", "status"]
}
},
{
model: db.employees,
attributes: ["name", "personal_email"]
},
{
model: db.task_statuses,
attributes: ["name", "color_code"]
},
{
model: db.priorities,
attributes: ["name", "color_code"]
},
{
model: db.task_documents,
require: false,
attributes: {
exclude: ["created_at", "updated_at", "deleted_at", "status"]
},
include: [
{
model: db.medias,
attributes: {
exclude: ["created_at", "updated_at", "deleted_at", "status"]
},
},
{
model: db.document_categories,
attributes: {
exclude: ["created_at", "updated_at", "deleted_at", "status"]
},
},
]
}
]
});
return instance;
} catch (error) {
console.log(error);
throw new Error(error);
}
},
My requirement : Simply get data based on the search filter, sortname, sortvalue. Searching should happen for “contact_person_name, organization_name” col of customer table, based on “title” col of fence table, based on “name” col of task_statuses & priorities table.
Not able to know where is the issue. I am just pulling my hairs out. Kindly please guide me where I am wrong.
For this I have including the tables with required cols.
Based on the rawAttributes property given by sequelize, this will iterate all the cols of the tasks table for checking the searched value.
And additionally, I am adding customer, priority and task_status table’s specific col using the “sequelize.where(sequelize.col(….”.
This is working perfectly fine with “pagesize=&size=&” and giving proper results but not working for “pagesize=10&size=1&”.