Sequelize: Querying on where association
Curious if the implementation has changed on how to query on associations. I created a test case that used to work on v2.0.0-rc8 but is currently not working on v2.0.0. Was hoping for some clarity on how to adjust my code for the new changes. Thanks for assistance.
v2.0.0-rc8 query:
`Company`.`id`,
`Company`.`name`,
`Company`.`createdAt`,
`Company`.`updatedAt`,
`Company`.`presidentId`,
`Company`.`departmentId`,
`President`.`id` AS `President.id`,
`President`.`name` AS `President.name`,
`President`.`createdAt` AS `President.createdAt`,
`President`.`updatedAt` AS `President.updatedAt`,
`Department`.`id` AS `Department.id`,
`Department`.`name` AS `Department.name`,
`Department`.`createdAt` AS `Department.createdAt`,
`Department`.`updatedAt` AS `Department.updatedAt`,
`Department`.`leadId` AS `Department.leadId`,
`Department.Lead`.`id` AS `Department.Lead.id`,
`Department.Lead`.`name` AS `Department.Lead.name`,
`Department.Lead`.`createdAt` AS `Department.Lead.createdAt`,
`Department.Lead`.`updatedAt` AS `Department.Lead.updatedAt`
FROM
`Companies` AS `Company`
LEFT OUTER JOIN
`Users` AS `President` ON `Company`.`presidentId` = `President`.`id`
LEFT OUTER JOIN
`Departments` AS `Department` ON `Company`.`departmentId` = `Department`.`id`
LEFT OUTER JOIN
`Users` AS `Department.Lead` ON `Department`.`leadId` = `Department.Lead`.`id`
WHERE
(`President`.`name` = 'OurLead'
OR `Department.Lead`.`name` = 'OurLead');
v2.0.0 query:
`Company`.`id`,
`Company`.`name`,
`Company`.`createdAt`,
`Company`.`updatedAt`,
`Company`.`presidentId`,
`Company`.`departmentId`,
`President`.`id` AS `President.id`,
`President`.`name` AS `President.name`,
`President`.`createdAt` AS `President.createdAt`,
`President`.`updatedAt` AS `President.updatedAt`,
`Department`.`id` AS `Department.id`,
`Department`.`name` AS `Department.name`,
`Department`.`createdAt` AS `Department.createdAt`,
`Department`.`updatedAt` AS `Department.updatedAt`,
`Department`.`leadId` AS `Department.leadId`,
`Department.Lead`.`id` AS `Department.Lead.id`,
`Department.Lead`.`name` AS `Department.Lead.name`,
`Department.Lead`.`createdAt` AS `Department.Lead.createdAt`,
`Department.Lead`.`updatedAt` AS `Department.Lead.updatedAt`
FROM
`Companies` AS `Company`
LEFT OUTER JOIN
`Users` AS `President` ON `Company`.`presidentId` = `President`.`id`
LEFT OUTER JOIN
`Departments` AS `Department` ON `Company`.`departmentId` = `Department`.`id`
LEFT OUTER JOIN
`Users` AS `Department.Lead` ON `Department`.`leadId` = `Department.Lead`.`id`
WHERE
(`Company`.`President.name` = 'OurLead'
OR `Company`.`Department.Lead.name` = 'OurLead');
toaster33
All 62 comments
Pretty sure that where with nested keys didn't work in rc8 either.
But yeah nested keys are insanely error prone so we don't really support them currently.
mickhansen
on 10 Feb 2015
We need some new syntax for defining include based where's that don't go on a join (for OR) mostly.
mickhansen
on 10 Feb 2015
I don't think it is the right long term solution, but I think this could be accomplished in the short term (until a better solution is implemented) using a literal. If you know the table name, as we do in that test case I think we could just do:
where: Sequelize.or({Sequelize.literal('`President`.`name`'): 'OurLead'}, {Sequelize.liternal('`Department.Lead`.`name`'): 'OurLead'})
instead of
where: Sequelize.or({'President.name': 'OurLead'}, {'Department.Lead.name': 'OurLead'})
DavidTPate
on 11 Feb 2015
@DavidTPate Unfortuneatly literal() can't be used as a key.
I'm very open to general API discussion for this. But i don't think relying on nested keys is the way to go.
mickhansen
on 11 Feb 2015
I think I was able to narrow down the cause...
\abstract\query-generator.js
v2.0.0
else if (Utils._.isPlainObject(smth)) {
return self.whereItemsQuery(smth, {
model: factory,
prefix: prepend && tableName
});
}
v2.0.0-rc8
else if (Utils._.isPlainObject(smth)) {
if (prepend) {
if (tableName) options.keysEscaped = true;
smth = this.prependTableNameToHash(tableName, smth);
}
result = this.hashToWhereConditions(smth, factory, options);
}
To get conversations / ideas rolling...
First I was thinking another attribute within the include but cannot figure out how the attribute would be combined with other attributes. If the following existed, I still would not know if the attributes should be ANDed or ORed.
{
include: [
{
model: President,
attribute: Sequelize.and({name: 'OurLead'})
},
{
model: Department,
attribute: Sequelize.and({name: 'OurLead'})
}
]
}
Building out the where using includes but this seems too cumbersome and redundant:
{
where: Sequelize.or({
include: [
model: 'Users',
as: 'President',
where: { name: 'OurLead' }
]
},
{
include: [
model: Departments,
as: Department,
include: [
model: 'Users',
as: 'Department.Lead',
where: { name: 'OurLead' }
]
]
})
}
Another variation of what worked previously (please refer to test link line 80):
{
where: Sequelize.or(
{ Company.associations.President.as: { name: 'OurLead' }},
{ Section.associations.Lead.as: { name: 'OurLead' }},
)
}
Unfortuneatly it's not possible to have references as keys (atleast in ES5).
The problem with nested keys is that it was error prone, but perhaps we could use a special syntax to denote that it's an include: {'$Company.Department.key$': value}.
That still poses a general issue though, we took out include on string because it was very error prone and not very deterministic.
mickhansen
on 13 Feb 2015
Since it's possible to save association references, perhaps something like:
where: {
$or: [
{$association: Company.associations.President, where: {
name: 'OurLead'
}}
]
}
I like your last suggestion which would not incur much of a change (at least for me).
toaster33
on 14 Feb 2015
Hi there,
Just want to know if you decided how to fix this issue / improvement ?
sebay00
on 25 Feb 2015
@sebay00 not finally, but likely the last proposal i commenetd.
mickhansen
on 25 Feb 2015
@mickhansen I think you last suggestion of adding $association would work well.
DavidTPate
on 25 Feb 2015
:+1: for last proposal of @mickhansen
OlivierCuyp
on 10 Mar 2015
馃憤
davidlanger
on 11 Mar 2015
:+1: Too !
Lookin' forward !
sebay00
on 11 Mar 2015
:+1:
josephpage
on 26 Mar 2015
Hi, I see you might have found the solution to this issue. Do you plan on implementing it in the near future ? Maybe you could give us a deadline or a timeline to throw us a bone :)
JayQuid
on 26 Mar 2015
Plan on yes :) Working on perfecting the API still and have to find the time to implement it.
mickhansen
on 26 Mar 2015
Hi guys, maybe FYI, I didn't get the work around to function described in #3527, when using "findAndCountAll" and includes with "as". It didn't join the table while doing the "SELECT count(*)...". But a simple empty object assigned on the "where" attribute of the includes works well.
options.include = [{
model: Employee,
as: 'employee',
where: {}
}];
options.where = {
$or: [{
name: {
$ilike: `%${options.search}%`
}
},
db.sequelize.where(db.sequelize.cast(db.sequelize.col('employee.age'), 'TEXT'), 'ILIKE', `%${options.search}%`)
]
};
ppetzold
on 11 May 2015
+1
vbogdanov
on 3 Jun 2015
+1
majodev
on 9 Jun 2015
Hello, what is the last status on the $association feature ? (do WHERE outside the JOIN, i asked in #3903) Thank you.
Orvisky
on 9 Jun 2015
No work done yet.
mickhansen
on 9 Jun 2015
+1
johnnncodes
on 6 Jul 2015
+1
tmcleroy
on 27 Jul 2015
+1 any update on the progress? =)
nicolasgramlich
on 30 Jul 2015
@nicolasgramlich No work being done by anyone afaik.
mickhansen
on 30 Jul 2015
Are any decisions made regarding the API yet?
Personally, I think something like this would be a nice way:
Project.findAll({
where: {
active: true,
$User.createdAt: {
$gt: (new Date() - (3600 * 1000)
},
$User.Address.country: 麓Denmark麓
},
include: [
{ model: User, include: [ Address ] }
]
}).then(function (projects) {
console.log(projects);
});
Of course this could cause conflicts in the rare case models have the same name as keywords in Sequelize. This could be fixed by using $.Project instead of $Project, but that would kind of be a syntax change(?).
The mentioned $association method is also an option, but in nested queries like my example you would have to use the (loooong) word associations multiple times (if I understand correctly):
Project.findAll({
where: {
active: true,
Project.association.User.createdAt: {
$gt: (new Date() - (3600 * 1000)
},
Project.association.User.association.Address.country: 麓Denmark麓
},
include: [
{ model: User, include: [ Address ] }
]
}).then(function (projects) {
console.log(projects);
});
Also, a nested object within an associated model should be easily accessible. All together, the $.Model included to avoid conflicts, I think this would be a good option to consider:
Project.findAll({
where: {
active: true,
$.User.createdAt: { // Using $.Model to avoid conflicts with Sequelize keywords ($gt, $lt)
$gt: (new Date() - (3600 * 1000)
},
$.User.Address.country: 麓Denmark麓 // Easy access by just providing the property name
$.User.House: { // Nested object to easily access multiple properties of a nested model
color: 'green',
height: '25ft'
}
},
include: [
{ model: User, include: [ Address, House ] }
]
}).then(function (projects) {
console.log(projects);
});
Just so you know, this weird little thing would result in all projects that are active, and contain a user that has been created in the past hour, lives in Denmark and has a green house with a height of 25ft.
yp28
on 4 Aug 2015
@pprandee suggested solution worked for me, but then there was an update on sequelize 3.6 now it gives an error stating that the value is not a valid boolean
gaboAcosta
on 28 Aug 2015
@gaboAcosta Thats related to https://github.com/sequelize/sequelize/issues/4314
janmeier
on 31 Aug 2015
This feature request is fulfilled by 825eb75f31e71a242477a16e9a593ffc4519cd84 available in Sequelize 3.12.0!
Verdier
on 15 Oct 2015
@Verdier Not completely yet, the implementation is very basic and does not do any type of renaming (so you have to match the generated query in term of naming).
mickhansen
on 17 Oct 2015
@Verdier @mickhansen any chance to get a couple of examples on how much can be achieved with Sequelize 3.12.0 ? I've got a hack in place to be able to integrate datatables search with Sequelize, our use case is that we build a little library that works as an interface between Sequelize and md-datatables. We have achieved to be able to create tables by asigning a Model to the table and declare all the dependencies to be added, but in the end, there is a search input where users expect that if the search criteria matches any of the columns it should be shown on the result set (the user doesn't know or care that this is an account datatable and the owners email is a different entity)
gaboAcosta
on 19 Oct 2015
The latest version supports having where keys wrapped in $ to match a different table or field.
For instance: {'$nested.table.field$': 'value'}.
mickhansen
on 19 Oct 2015
@mickhansen Any quick example for this? I currently have this code:
Model.UserChallenge.findAll({
where: {
'$UserChallengeState.state' : 'Pending'
},
include: [
{model: Model.UserChallengeState, as: 'UserChallengeState'}
]
}).then(function(uc) {
res.json(uc);
});
But that returns following error (v3.12.2)
Unhandled rejection SequelizeDatabaseError: ER_BAD_FIELD_ERROR:
Unknown column 'userchallenge.$UserChallengeState.state' in 'where clause'
Carnewal
on 25 Oct 2015
@Carnewal Was a typo in my code, needs to be a $ on both sides wrapping it. $UserChallengeState.state$
mickhansen
on 25 Oct 2015
@mickhansen That did the trick. Thanks!
Carnewal
on 25 Oct 2015
This works great for me but I've noticed that adding a limit to the findAll breaks. E.g. this works:
BSDPerson.findAll({
order: [[Sequelize.fn( 'RANDOM' )]],
where: {
'$assignedCalls.id$' : null
},
include: [
{
model: BSDAssignedCall,
required: false,
as: 'assignedCalls',
}
]})
but this puts the WHERE assignedCalls.id clause inside a nested SELECT statement which doesn't work since the join is outside:
BSDPerson.findAll({
order: [[Sequelize.fn( 'RANDOM' )]],
limit: 10,
where: {
'$assignedCalls.id$' : null
},
include: [
{
model: BSDAssignedCall,
required: false,
as: 'assignedCalls',
}
]})
saikat
on 3 Dec 2015
@saikat
I had the same problem, and i can't use with findAndCountAll too when required is false, sequelize uses $nested.table.field$ in "where" clause, even there is no "join" with nested table.
does anyone know a solution?
mmbfreitas
on 10 Dec 2015
@mmbfreitas the where clause is used for both calls, sequelize doesn't know what to filter out, not sure it should know.
You're probably better off just having two seperate calls. Or if using postgres add a window count instead.
mickhansen
on 11 Dec 2015
i'm having the same trouble on findAndCountAll...
rcolmenarez
on 2 Mar 2016
where: {
'$User.name$' : null
}
this where clause should be written in docs! :+1:
hoonyland
on 24 Mar 2016
Any fix to this yet?
Just confirmed what @saikat said. Adding limit breaks the query throwing:
{
"errors": [
{
"code": "SequelizeDatabaseError",
"title": "SQLITE_ERROR: no such column: $SomeModel.AnAttribute$"
}
]
}
diosney
on 21 Apr 2016
@diosney I wouldn't expect an answer in this thread, if there's a bug an issue should be submitted with a test case reproducing it (following the guidelines of the project) so that it can be tracked and fixed.
DavidTPate
on 22 Apr 2016
This is partly solved, however there are general issues with trying to filter on joins and using limit - It's not a trivial problem to solve.
As @DavidTPate, specific issues with SSCCE's or even better pull requests with failing tests can help us fix them - But cases like these require a lot of work, and the result is usually less than performant. Consider if you can solve your case in a different way.
mickhansen
on 22 Apr 2016
@DavidTPate @mickhansen Thanks for your answer. Then I will use two separate queries to solve this. Thanks again.
diosney
on 22 Apr 2016
Hi all, i have a some problem with concat() in associated entity
Model.findAndCountAll({
where: Model.sequelize.where(Model.sequelize.fn("concat", Model.sequelize.col('propertyManager.FirstName'), Model.sequelize.col('propertyManager.LastName')), {
$iLike: '%test%'
})
})
In this example SELECT count(...) query, generated by sequelize works fine and return valid count, but when its try to get data sequelize generate smth look lke this(copy from uploads). Here "propertyManager" used in another JOIN section, and i got error
Table "propertyManager" not exists in FROM section
LINE 3: WHERE concat("propertyManager"."FirstName", "propertyManager...
Uploads: 
AshBringer4eg
on 8 Feb 2017
This issue thread really helped me find out how to use the query association syntax in the where clause from https://github.com/sequelize/sequelize/issues/3095#issuecomment-149277205: $table_name.field_name$: field_value
I didn't see this in the docs. Is it there and I just missed it, or do we need a PR? 馃槃
Edit: I see it now, but it was not easily understood at first.
ConAntonakos
on 21 Jun 2017
@ConAntonakos if you don't see it in the docs, I'd suggest putting in a PR.
DavidTPate
on 22 Jun 2017
here is my issue.
when include is duplicated, error shows
queryParams.include = [
{
model: models.role,
as: 'role',
include: [
{model: models.right, as: 'rightList'}
]
}
]
if (query['search'])
queryParams.where = {
"$or": [
{
email: {
$like: '%' + query['search'] + '%'
}
},{
"$role.label$": {
$like: '%' + query['search'] + '%'
}
}
]
};
if I didn't include rightList, then this query works well.
but if I use sub include, error shows "SequelizeDatabaseError"
starcraft0429
on 1 Dec 2017
if you want to query base on the association, do it like this.
sequelize@^4.33.4
testfile.js
import model from './models';
import Sequelize, { Op } from 'sequelize';
model.Service.findAll({
where: {
[Op.or]: {
name: {
[Op.like]: '%elvis%'
},
description: {
[Op.like]: '%elvis%'
},
first_name: Sequelize.where(Sequelize.col('User.first_name'), {
[Op.like]: '%elvis%'
}),
middle_name: Sequelize.where(Sequelize.col('User.middle_name'), {
[Op.like]: '%elvis%'
}),
surname: Sequelize.where(Sequelize.col('User.surname'), {
[Op.like]: '%elvis%'
})
}
},
include: [
{
model: model.User
}
]
})
.then(data => {
console.log('');
data.forEach(d => {
console.log('service:', d.name, 'owned by:', d.User.first_name);
});
});
query generated
SELECT
`Service`.`service_id`,
`Service`.`user_id`,
`Service`.`cover_image`,
`Service`.`name`,
`Service`.`description`,
`Service`.`created_at`,
`Service`.`updated_at`,
`Service`.`service_type_id`,
`User`.`user_id` AS `User.user_id`,
`User`.`first_name` AS `User.first_name`,
`User`.`middle_name` AS `User.middle_name`,
`User`.`surname` AS `User.surname`,
`User`.`email` AS `User.email`,
`User`.`password` AS `User.password`,
`User`.`sex` AS `User.sex`,
`User`.`profile_image` AS `User.profile_image`,
`User`.`confirm_token` AS `User.confirm_token`,
`User`.`is_confirmed` AS `User.is_confirmed`,
`User`.`remember_token` AS `User.remember_token`,
`User`.`created_at` AS `User.created_at`,
`User`.`updated_at` AS `User.updated_at`
FROM `service` AS `Service` LEFT OUTER JOIN `user` AS `User` ON `Service`.`user_id` = `User`.`user_id`
WHERE (`Service`.`name` LIKE '%elvis%' OR `Service`.`description` LIKE '%elvis%' OR `User`.`first_name` LIKE '%elvis%' OR `User`.`middle_name` LIKE '%elvis%' OR `User`.`surname` LIKE '%elvis%');
output
service: Satterfield - Kiehn owned by: Elvis
service: Effertz - Mitchell owned by: Elvis
aprilmintacpineda
on 23 Feb 2018
if you want to query base on the association, do it like this.
sequelize@^4.33.4testfile.js
import model from './models'; import Sequelize, { Op } from 'sequelize'; model.Service.findAll({ where: { [Op.or]: { name: { [Op.like]: '%elvis%' }, description: { [Op.like]: '%elvis%' }, first_name: Sequelize.where(Sequelize.col('User.first_name'), { [Op.like]: '%elvis%' }), middle_name: Sequelize.where(Sequelize.col('User.middle_name'), { [Op.like]: '%elvis%' }), surname: Sequelize.where(Sequelize.col('User.surname'), { [Op.like]: '%elvis%' }) } }, include: [ { model: model.User } ] }) .then(data => { console.log(''); data.forEach(d => { console.log('service:', d.name, 'owned by:', d.User.first_name); }); });query generated
SELECT `Service`.`service_id`, `Service`.`user_id`, `Service`.`cover_image`, `Service`.`name`, `Service`.`description`, `Service`.`created_at`, `Service`.`updated_at`, `Service`.`service_type_id`, `User`.`user_id` AS `User.user_id`, `User`.`first_name` AS `User.first_name`, `User`.`middle_name` AS `User.middle_name`, `User`.`surname` AS `User.surname`, `User`.`email` AS `User.email`, `User`.`password` AS `User.password`, `User`.`sex` AS `User.sex`, `User`.`profile_image` AS `User.profile_image`, `User`.`confirm_token` AS `User.confirm_token`, `User`.`is_confirmed` AS `User.is_confirmed`, `User`.`remember_token` AS `User.remember_token`, `User`.`created_at` AS `User.created_at`, `User`.`updated_at` AS `User.updated_at` FROM `service` AS `Service` LEFT OUTER JOIN `user` AS `User` ON `Service`.`user_id` = `User`.`user_id` WHERE (`Service`.`name` LIKE '%elvis%' OR `Service`.`description` LIKE '%elvis%' OR `User`.`first_name` LIKE '%elvis%' OR `User`.`middle_name` LIKE '%elvis%' OR `User`.`surname` LIKE '%elvis%');output
service: Satterfield - Kiehn owned by: Elvis service: Effertz - Mitchell owned by: Elvis
This method works as long as there is a single mode in the join. It fails the moment when there is another model with a different type of relation, i.e. belongsToMany
mdemblani
on 13 Jan 2019
Table0->Table1->Table2
field_name: Sequelize.where(Sequelize.col('Table1->Table2.field_name'), {
[Op.like]: '%field_name'%'
}),
smk-data
on 21 Apr 2019
if you want to query base on the association, do it like this.
sequelize@^4.33.4
testfile.jsimport model from './models'; import Sequelize, { Op } from 'sequelize'; model.Service.findAll({ where: { [Op.or]: { name: { [Op.like]: '%elvis%' }, description: { [Op.like]: '%elvis%' }, first_name: Sequelize.where(Sequelize.col('User.first_name'), { [Op.like]: '%elvis%' }), middle_name: Sequelize.where(Sequelize.col('User.middle_name'), { [Op.like]: '%elvis%' }), surname: Sequelize.where(Sequelize.col('User.surname'), { [Op.like]: '%elvis%' }) } }, include: [ { model: model.User } ] }) .then(data => { console.log(''); data.forEach(d => { console.log('service:', d.name, 'owned by:', d.User.first_name); }); });query generated
SELECT `Service`.`service_id`, `Service`.`user_id`, `Service`.`cover_image`, `Service`.`name`, `Service`.`description`, `Service`.`created_at`, `Service`.`updated_at`, `Service`.`service_type_id`, `User`.`user_id` AS `User.user_id`, `User`.`first_name` AS `User.first_name`, `User`.`middle_name` AS `User.middle_name`, `User`.`surname` AS `User.surname`, `User`.`email` AS `User.email`, `User`.`password` AS `User.password`, `User`.`sex` AS `User.sex`, `User`.`profile_image` AS `User.profile_image`, `User`.`confirm_token` AS `User.confirm_token`, `User`.`is_confirmed` AS `User.is_confirmed`, `User`.`remember_token` AS `User.remember_token`, `User`.`created_at` AS `User.created_at`, `User`.`updated_at` AS `User.updated_at` FROM `service` AS `Service` LEFT OUTER JOIN `user` AS `User` ON `Service`.`user_id` = `User`.`user_id` WHERE (`Service`.`name` LIKE '%elvis%' OR `Service`.`description` LIKE '%elvis%' OR `User`.`first_name` LIKE '%elvis%' OR `User`.`middle_name` LIKE '%elvis%' OR `User`.`surname` LIKE '%elvis%');output
service: Satterfield - Kiehn owned by: Elvis service: Effertz - Mitchell owned by: ElvisThis method works as long as there is a single mode in the join. It fails the moment when there is another model with a different type of relation, i.e. belongsToMany
sry, I'm suffering the same problem, I have many different relation types inside a model, this method faileds, did you find any solution?
luqimin
on 10 Feb 2020
sry, I'm suffering the same problem, I have many different relation types inside a model, this method faileds, did you find any solution?
You can use something like this:
model.Service.findAll({
where: {
[Op.or]: {
name: {
[Op.like]: '%elvis%'
},
description: {
[Op.like]: '%elvis%'
}
}
},
include: [
{
model: model.User,
where: {
[Op.or]: {
first_name: {
[Op.like]: '%elvis%'
}),
middle_name: {
[Op.like]: '%elvis%'
}),
surname: {
[Op.like]: '%elvis%'
})
}
},
include: [{
model: model.Data,
where: {
first_name: {
[Op.like]: '%elvis%'
})
}
}]
}
]
})
sry, I'm suffering the same problem, I have many different relation types inside a model, this method faileds, did you find any solution?
You can use something like this:
model.Service.findAll({ where: { [Op.or]: { name: { [Op.like]: '%elvis%' }, description: { [Op.like]: '%elvis%' } } }, include: [ { model: model.User, where: { [Op.or]: { first_name: { [Op.like]: '%elvis%' }), middle_name: { [Op.like]: '%elvis%' }), surname: { [Op.like]: '%elvis%' }) } }, include: [{ model: model.Data, where: { first_name: { [Op.like]: '%elvis%' }) } }] } ] })
this may not work in my case:
Project.hasOne(Task);
Task.belongsTo(User, { as: 'createdBy' });
Task.belongsToMany(User, { as: 'members' });
// I need to get the results of '`project.name` is `testProject` or `task.name` is `testTask`'
Project.findAndCountAll({
where: {
{[Op.or]: [ { name: 'testProject' }, { '$task.name$': 'testTask' } ]}
},
include: [
{ model: Task, required: false, include: [{ model: User, as: 'members' }] }
]
})
Above gets the sql output:
SELECT
`project`.*,
`task`.`id` AS `task.id`,
`task`.`name` AS `task.name`,
`task->members`.`id` AS `task.members.id`,
`task->members->TaskUser`.`user_id` AS `task.members.TaskUser.userId`,
`task->members->TaskUser`.`task_id` AS `task.members.TaskUser.taskId`
FROM
(
SELECT
`project`.`id`,
`project`.`task_id` AS `taskId`,
`project`.`task_id`
FROM
`project` AS `project`
WHERE
(
`project`.`deleted_at` IS NULL
AND (
`project`.`name` = 'testProject'
OR `task`.`name` = 'testTask'
))
LIMIT 0,
10
) AS `project`
LEFT OUTER JOIN `task` AS `task` ON `project`.`taskId` = `task`.`id`
AND (
`task`.`deleted_at` IS NULL
)
LEFT OUTER JOIN (
`TaskUser` AS `task->members->TaskUser`
INNER JOIN `user` AS `task->members` ON `task->members`.`id` = `task->members->TaskUser`.`user_id`
) ON `task`.`id` = `task->members->TaskUser`.`task_id`
AND ( `task->members`.`deleted_at` IS NULL );
And here is sql Error:
1054 - Unknown column 'task.name' in 'where clause', Time: 0.005000s
this may not work in my case:
try to use subQuery: false in findAndCountAll options. In this option your WHEREcase will be in the end of all statements.
Project.findAndCountAll({
where: {
{[Op.or]: [ { name: 'testProject' }, { '$task.name$': 'testTask' } ]}
},
include: [
{ model: Task, required: false, include: [{ model: User, as: 'members' }] }
],
subQuery: false
})
Or another solution. it may replace Task where case in one row with JOIN
Project.findAndCountAll({
where: {
name: 'testProject'
},
include: [{
model: Task,
required: false,
where: {
name: 'testTask'
},
include: [{ model: User, as: 'members' }]
}]
})
try to use subQuery: false in findAndCountAll options. In this option your WHEREcase will be in the end of all statements.
Thank you so much!!! @AshBringer4eg
luqimin
on 11 Feb 2020
Is it possible to reference a table querying JSONB and the $col$ operator? like:
{
"$Media$.audio.length": {
[Op.gt]: 20
}
}
Gives me an error.
fabioespinosa
on 5 Mar 2020
Does anyone realized that i found something that is working for this kind of problem. I've been suffering for 3 days and finally i found it out.
const findInvoice = await model.invoices_ht.findAll({
where: {
location_id: location_id,
status: 1,
created_at: Op.where(Op.literal('invoices_ht.created_at '), '>', moment('2020-10-31').toDate()),
[Op.where]: Op.literal('product_category IN (1,5,3)')
},
attributes: [
'id',
'currency',
'amount_due',
'date_paid',
'recurring',
],
include: [{
model: model.invoices_dt,
attributes: ['product_name'],
include: [{
required: false,
model: model.products,
attributes: ['product_category']
}]
}],
order: [
['created_at', 'DESC']
]
});
kevinramadhan
on 17 Dec 2020
Does anyone realized that i found something that is working for this kind of problem. I've been suffering for 3 days and finally i found it out.
Try something like this:
const findInvoice = await model.invoices_ht.findAll({
where: {
location_id: location_id,
status: 1,
created_at: {
[Op.gt]: moment('2020-10-31').toDate()
}
},
attributes: ['id', 'currency', 'amount_due', 'date_paid', 'recurring'],
include: [{
model: model.invoices_dt,
attributes: ['product_name'],
include: [{
required: false,
model: model.products,
attributes: ['product_category']
where: {
product_category: {
[Op.in]: [1, 3, 5]
}
}
}]
}],
order: [
['created_at', 'DESC']
]
});
this may not work in my case:
try to use subQuery: false in findAndCountAll options. In this option your WHEREcase will be in the end of all statements.Project.findAndCountAll({ where: { {[Op.or]: [ { name: 'testProject' }, { '$task.name$': 'testTask' } ]} }, include: [ { model: Task, required: false, include: [{ model: User, as: 'members' }] } ], subQuery: false })Or another solution. it may replace Task where case in one row with JOIN
Project.findAndCountAll({ where: { name: 'testProject' }, include: [{ model: Task, required: false, where: { name: 'testTask' }, include: [{ model: User, as: 'members' }] }] })The first solution has one problem:
When in findcountall, the returned rows are not as expected when limit is used.
The second solution is different from the original question
The condition is or, but your reply becomes and between query and sub queries
StevenWung
on 9 Mar 2021
Related issues
chris-rock
路
79Comments
clutariomark
路
133Comments
jy95
路
70Comments
ludydoo
路
114Comments
cusspvz
路
146Comments
Most helpful comment
this where clause should be written in docs! :+1: