CODEWITHSUNDEEP
mysqlnode.jssequelize.js
Ricardo ACB
Ricardo ACB

Reputation: 268

Sequelize 'where' on parent and child tables

I need to make a query in two tables (parent and child), through sequelize (with mysql).

The problem it's that when i use where clause on parent table it discards results on child's table.

In SQL Words i need something like

SELECT * 
FROM PARENT 
INNER JOIN CHILD 
  ON PARENT.ID = CHILD.PARENTID 
WHERE PARENT.FIELD LIKE 'A' 
  OR CHILD.FIELD LIKE 'A'

Instead, sequelize always returns something like

SELECT * 
FROM PARENT 
INNER JOIN CHILD 
  ON PARENT.ID = CHILD.PARENTID 
 AND (CHILD.deletedAt IS NULL AND (CHILD.FIELD LIKE 'A')) 
WHERE (PARENT.deletedAt IS NULL AND (PARENT.FIELD LIKE 'A'))

I have checked the data and the child query brings results but because of the where statement in the parent query it discards child results

UPDATE

My code is something like:

var childTable = {model:child.model,where.$or:{childField:{$like:'A'}}};
queryOptions = {};
queryOptions.include = [chilldTable];
queryOptions.where = {$or:{parentField:{$like:'A'}}}
Parent.findAll(queryOptions).then(res.send.bind(res))
  .catch(errorHandler(res, next));

Does anybody now why this it's happening?

Thanks!

Upvotes: 1

Views: 12552

Answers (1)

Ricardo ACB
Ricardo ACB

Reputation: 268

Well only for knowing purposes, the answer i found it in this page:

https://www.bountysource.com/issues/8468317-querying-on-where-association

After reading a lot of forums and posts about, it results that if i set the "$" typo before and after the field this make the query to work... It will be something like:

var childTable = {model:child.model};
queryOptions = {};
queryOptions.include = [chilldTable];
queryOptions.where = {$or:{parentField:{$like:'A'}, '$childTable.field$':{$like:'A'}}
Parent.findAll(queryOptions).then(res.send.bind(res))
.catch(errorHandler(res, next));

Upvotes: 6

Related Questions