Haresh Vidja Haresh Vidja - 3 months ago 35
MySQL Question

Sequelize.js can't generate condition in WHERE clause for associated table field

I have below code in Node.js with sequelize.js module

// association Users belongs to Companies
Users.belongsTo(Companies, {
foreignKey: 'company_id',
});

// default conditions
var conditions = {deleted: '0'};
var companyConditions = {deleted: '0'};

// custom conditions parameters
conditions['$or'] = {first_name: {'$like': '%' + req.query.search + '%'}, last_name: {'$like': '%' + req.query.search + '%'}, email: {'$like': '%' + req.query.search + '%'}};
companyConditions['$or'] = {name: {'$like': req.query.search + '%'}};

// generrate select query
Users.findAll({include: [{model: Companies, where: companyConditions}], where: conditions});


But it produce different query than want to achieve

SELECT `users`.*, `company`.* FROM `users` AS `users`
INNER JOIN `companies` AS `company` ON `users`.`company_id` = `company`.`id` AND `company`.`deleted` = '0' AND (`company`.`name` LIKE 'town%')
WHERE `users`.`deleted` = '0' AND (`users`.`first_name` LIKE '%town%' OR `users`.`last_name` LIKE '%town%' OR `users`.`email` LIKE '%town%');


If I change custom condition
its not working


// custom conditions parameters
conditions['$or'] = {'company.name': {'$like': req.query.search + '%'},first_name: {'$like': '%' + req.query.search + '%'}, last_name: {'$like': '%' + req.query.search + '%'}, email: {'$like': '%' + req.query.search + '%'}};
// companyConditions['$or'] = {name: {'$like': req.query.search + '%'}};


Then node.js throws error
Unhandled rejection SequelizeDatabaseError: ER_BAD_FIELD_ERROR: Unknown column 'users.company.name' in 'where clause'


What I want to achieve?

SELECT users.*, company.* FROM users AS users
INNER JOIN companies AS company
ON users.company_id = company.id AND company.deleted = '0'
WHERE users.deleted = '0' AND (users.first_name LIKE '%town%' OR users.last_name LIKE '%town%' OR users.email LIKE '%town%' OR company.name LIKE 'town%');


Any one have any idea about this? please help me.

Thanks in Advance

Answer

You should wrap column name in main where with $ symbol, that allows to do query to columns in include

// custom conditions parameters
conditions['$or'] = {'$company.name$': {'$like': req.query.search + '%'}, first_name: {'$like': '%' + req.query.search + '%'},...