How can I bind a variable to sequelize literal?
1
I have this subquery that is used to check the existence of a column related to the source model.
const defaultingLoans = await Loan.findAll({
where: {
[Op.and]: database.sequelize.literal('EXISTS(SELECT * FROM "Instalments" WHERE "Instalments"."loanId" = "Loan"."id" AND "Instalments"."status" = 'pending')')
}
});
The query works fine but the value pending ideally won't be fixed so I'll like to have a variable there that can be used to query for different status.
How can I replace the pending string with a variable.
Concatenation didn't work here because Sequelize has a weird way of parsing concatenated SQL queries which result in an error. An example is here https://pastebin.com/u8tr4Xbt and I took a screenshot of the error here
javascript node.js sequelize.js
asked Dec 28 '18 at 23:23
protonproton
127116
add a comment |
1
I have this subquery that is used to check the existence of a column related to the source model.
const defaultingLoans = await Loan.findAll({
where: {
[Op.and]: database.sequelize.literal('EXISTS(SELECT * FROM "Instalments" WHERE "Instalments"."loanId" = "Loan"."id" AND "Instalments"."status" = 'pending')')
}
});
The query works fine but the value pending ideally won't be fixed so I'll like to have a variable there that can be used to query for different status.
How can I replace the pending string with a variable.
Concatenation didn't work here because Sequelize has a weird way of parsing concatenated SQL queries which result in an error. An example is here https://pastebin.com/u8tr4Xbt and I took a screenshot of the error here
javascript node.js sequelize.js
asked Dec 28 '18 at 23:23
protonproton
127116
1
Are you familiar with concatenating variables to strings?
– Taplar
Dec 28 '18 at 23:26
Possible duplicate of How to interpolate variables in strings in JavaScript, without concatenation?
– Hydrothermal
Dec 28 '18 at 23:27
@Hydrothermal String concatenation doesn't work with sequelize literal
– proton
Dec 28 '18 at 23:27
1
What makes you say that?literal()is being given a string as an argument. It doesn't matter how it's constructed. Just that it is a valid string.literal('abc')andliteral('ab'+ 'c')would both pass the same string to the method
– Taplar
Dec 28 '18 at 23:29
There seems to be a problem with the way sequelize parses concatenated SQL strings, take a look at the query: pastebin.com/u8tr4Xbt The image is the error gotten when I run that: imagebin.ca/v/4RbsQOAPaxvO
– proton
Dec 28 '18 at 23:39
add a comment |
1
1
1
I have this subquery that is used to check the existence of a column related to the source model.
const defaultingLoans = await Loan.findAll({
where: {
[Op.and]: database.sequelize.literal('EXISTS(SELECT * FROM "Instalments" WHERE "Instalments"."loanId" = "Loan"."id" AND "Instalments"."status" = 'pending')')
}
});
The query works fine but the value pending ideally won't be fixed so I'll like to have a variable there that can be used to query for different status.
How can I replace the pending string with a variable.
Concatenation didn't work here because Sequelize has a weird way of parsing concatenated SQL queries which result in an error. An example is here https://pastebin.com/u8tr4Xbt and I took a screenshot of the error here
javascript node.js sequelize.js
asked Dec 28 '18 at 23:23
protonproton
127116
I have this subquery that is used to check the existence of a column related to the source model.
const defaultingLoans = await Loan.findAll({
where: {
[Op.and]: database.sequelize.literal('EXISTS(SELECT * FROM "Instalments" WHERE "Instalments"."loanId" = "Loan"."id" AND "Instalments"."status" = 'pending')')
}
});
The query works fine but the value pending ideally won't be fixed so I'll like to have a variable there that can be used to query for different status.
How can I replace the pending string with a variable.
Concatenation didn't work here because Sequelize has a weird way of parsing concatenated SQL queries which result in an error. An example is here https://pastebin.com/u8tr4Xbt and I took a screenshot of the error here
javascript node.js sequelize.js
javascript node.js sequelize.js
asked Dec 28 '18 at 23:23
protonproton
127116
asked Dec 28 '18 at 23:23
protonproton
127116
edited Dec 28 '18 at 23:42
proton
asked Dec 28 '18 at 23:23
protonproton
127116
asked Dec 28 '18 at 23:23
protonproton
127116
asked Dec 28 '18 at 23:23
protonproton
127116
127116
1
Are you familiar with concatenating variables to strings?
– Taplar
Dec 28 '18 at 23:26
Possible duplicate of How to interpolate variables in strings in JavaScript, without concatenation?
– Hydrothermal
Dec 28 '18 at 23:27
@Hydrothermal String concatenation doesn't work with sequelize literal
– proton
Dec 28 '18 at 23:27
1
What makes you say that?literal()is being given a string as an argument. It doesn't matter how it's constructed. Just that it is a valid string.literal('abc')andliteral('ab'+ 'c')would both pass the same string to the method
– Taplar
Dec 28 '18 at 23:29
There seems to be a problem with the way sequelize parses concatenated SQL strings, take a look at the query: pastebin.com/u8tr4Xbt The image is the error gotten when I run that: imagebin.ca/v/4RbsQOAPaxvO
– proton
Dec 28 '18 at 23:39
add a comment |
1
Are you familiar with concatenating variables to strings?
– Taplar
Dec 28 '18 at 23:26
Possible duplicate of How to interpolate variables in strings in JavaScript, without concatenation?
– Hydrothermal
Dec 28 '18 at 23:27
@Hydrothermal String concatenation doesn't work with sequelize literal
– proton
Dec 28 '18 at 23:27
1
What makes you say that?literal()is being given a string as an argument. It doesn't matter how it's constructed. Just that it is a valid string.literal('abc')andliteral('ab'+ 'c')would both pass the same string to the method
– Taplar
Dec 28 '18 at 23:29
There seems to be a problem with the way sequelize parses concatenated SQL strings, take a look at the query: pastebin.com/u8tr4Xbt The image is the error gotten when I run that: imagebin.ca/v/4RbsQOAPaxvO
– proton
Dec 28 '18 at 23:39
1
1
Are you familiar with concatenating variables to strings?
– Taplar
Dec 28 '18 at 23:26
Are you familiar with concatenating variables to strings?
– Taplar
Dec 28 '18 at 23:26
Possible duplicate of How to interpolate variables in strings in JavaScript, without concatenation?
– Hydrothermal
Dec 28 '18 at 23:27
Possible duplicate of How to interpolate variables in strings in JavaScript, without concatenation?
– Hydrothermal
Dec 28 '18 at 23:27
@Hydrothermal String concatenation doesn't work with sequelize literal
– proton
Dec 28 '18 at 23:27
@Hydrothermal String concatenation doesn't work with sequelize literal
– proton
Dec 28 '18 at 23:27
1
1
What makes you say that?
literal() is being given a string as an argument. It doesn't matter how it's constructed. Just that it is a valid string. literal('abc') and literal('ab'+ 'c') would both pass the same string to the method– Taplar
Dec 28 '18 at 23:29
What makes you say that?
literal() is being given a string as an argument. It doesn't matter how it's constructed. Just that it is a valid string. literal('abc') and literal('ab'+ 'c') would both pass the same string to the method– Taplar
Dec 28 '18 at 23:29
There seems to be a problem with the way sequelize parses concatenated SQL strings, take a look at the query: pastebin.com/u8tr4Xbt The image is the error gotten when I run that: imagebin.ca/v/4RbsQOAPaxvO
– proton
Dec 28 '18 at 23:39
There seems to be a problem with the way sequelize parses concatenated SQL strings, take a look at the query: pastebin.com/u8tr4Xbt The image is the error gotten when I run that: imagebin.ca/v/4RbsQOAPaxvO
– proton
Dec 28 '18 at 23:39
add a comment |
1 Answer
1
active
oldest
votes
1
You can turn defaultingLoans into a function which accepts an amount:
const defaultingLoans = amount => await Loan.findAll({
where: {
[Op.and]: database.sequelize.literal(`EXISTS(SELECT * FROM "Instalments" WHERE "Instalments"."loanId" = "Loan"."id" AND "Instalments"."amount" = ${amount})`)
}
});
usage:
const loans = defaultingLoans(2000);
answered Dec 28 '18 at 23:31
ic3b3rgic3b3rg
10.7k41945
There seems to be a problem with the way sequelize parses concatenated SQL strings, take a look at the query: pastebin.com/u8tr4Xbt The image is the error gotten when I run that: imagebin.ca/v/4RbsQOAPaxvO
– proton
Dec 28 '18 at 23:40
That's becauseclause1andclause2are strings - you need to include quotes around the concatenated values when using strings
– ic3b3rg
Dec 28 '18 at 23:43
Something like this database.sequelize.literal(EXISTS(SELECT * FROM "Instalments" WHERE "Instalments"."loanId" = "Loan"."id" AND "Instalments"."status" IN ("${clause1}", "${clause2}"))) yeah? Still gives me the same error.
– proton
Dec 28 '18 at 23:45
If not, can you help share a sample of what you're talking about, thanks.
– proton
Dec 28 '18 at 23:46
1
Wow. That worked. Thanks a lot
– proton
Dec 28 '18 at 23:48
|
show 1 more comment
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53965349%2fhow-can-i-bind-a-variable-to-sequelize-literal%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
You can turn defaultingLoans into a function which accepts an amount:
const defaultingLoans = amount => await Loan.findAll({
where: {
[Op.and]: database.sequelize.literal(`EXISTS(SELECT * FROM "Instalments" WHERE "Instalments"."loanId" = "Loan"."id" AND "Instalments"."amount" = ${amount})`)
}
});
usage:
const loans = defaultingLoans(2000);
answered Dec 28 '18 at 23:31
ic3b3rgic3b3rg
10.7k41945
There seems to be a problem with the way sequelize parses concatenated SQL strings, take a look at the query: pastebin.com/u8tr4Xbt The image is the error gotten when I run that: imagebin.ca/v/4RbsQOAPaxvO
– proton
Dec 28 '18 at 23:40
That's becauseclause1andclause2are strings - you need to include quotes around the concatenated values when using strings
– ic3b3rg
Dec 28 '18 at 23:43
Something like this database.sequelize.literal(EXISTS(SELECT * FROM "Instalments" WHERE "Instalments"."loanId" = "Loan"."id" AND "Instalments"."status" IN ("${clause1}", "${clause2}"))) yeah? Still gives me the same error.
– proton
Dec 28 '18 at 23:45
If not, can you help share a sample of what you're talking about, thanks.
– proton
Dec 28 '18 at 23:46
1
Wow. That worked. Thanks a lot
– proton
Dec 28 '18 at 23:48
|
show 1 more comment
1
You can turn defaultingLoans into a function which accepts an amount:
const defaultingLoans = amount => await Loan.findAll({
where: {
[Op.and]: database.sequelize.literal(`EXISTS(SELECT * FROM "Instalments" WHERE "Instalments"."loanId" = "Loan"."id" AND "Instalments"."amount" = ${amount})`)
}
});
usage:
const loans = defaultingLoans(2000);
answered Dec 28 '18 at 23:31
ic3b3rgic3b3rg
10.7k41945
There seems to be a problem with the way sequelize parses concatenated SQL strings, take a look at the query: pastebin.com/u8tr4Xbt The image is the error gotten when I run that: imagebin.ca/v/4RbsQOAPaxvO
– proton
Dec 28 '18 at 23:40
That's becauseclause1andclause2are strings - you need to include quotes around the concatenated values when using strings
– ic3b3rg
Dec 28 '18 at 23:43
Something like this database.sequelize.literal(EXISTS(SELECT * FROM "Instalments" WHERE "Instalments"."loanId" = "Loan"."id" AND "Instalments"."status" IN ("${clause1}", "${clause2}"))) yeah? Still gives me the same error.
– proton
Dec 28 '18 at 23:45
If not, can you help share a sample of what you're talking about, thanks.
– proton
Dec 28 '18 at 23:46
1
Wow. That worked. Thanks a lot
– proton
Dec 28 '18 at 23:48
|
show 1 more comment
1
1
1
You can turn defaultingLoans into a function which accepts an amount:
const defaultingLoans = amount => await Loan.findAll({
where: {
[Op.and]: database.sequelize.literal(`EXISTS(SELECT * FROM "Instalments" WHERE "Instalments"."loanId" = "Loan"."id" AND "Instalments"."amount" = ${amount})`)
}
});
usage:
const loans = defaultingLoans(2000);
answered Dec 28 '18 at 23:31
ic3b3rgic3b3rg
10.7k41945
You can turn defaultingLoans into a function which accepts an amount:
const defaultingLoans = amount => await Loan.findAll({
where: {
[Op.and]: database.sequelize.literal(`EXISTS(SELECT * FROM "Instalments" WHERE "Instalments"."loanId" = "Loan"."id" AND "Instalments"."amount" = ${amount})`)
}
});
usage:
const loans = defaultingLoans(2000);
answered Dec 28 '18 at 23:31
ic3b3rgic3b3rg
10.7k41945
answered Dec 28 '18 at 23:31
ic3b3rgic3b3rg
10.7k41945
answered Dec 28 '18 at 23:31
ic3b3rgic3b3rg
10.7k41945
answered Dec 28 '18 at 23:31
ic3b3rgic3b3rg
10.7k41945
10.7k41945
There seems to be a problem with the way sequelize parses concatenated SQL strings, take a look at the query: pastebin.com/u8tr4Xbt The image is the error gotten when I run that: imagebin.ca/v/4RbsQOAPaxvO
– proton
Dec 28 '18 at 23:40
That's becauseclause1andclause2are strings - you need to include quotes around the concatenated values when using strings
– ic3b3rg
Dec 28 '18 at 23:43
Something like this database.sequelize.literal(EXISTS(SELECT * FROM "Instalments" WHERE "Instalments"."loanId" = "Loan"."id" AND "Instalments"."status" IN ("${clause1}", "${clause2}"))) yeah? Still gives me the same error.
– proton
Dec 28 '18 at 23:45
If not, can you help share a sample of what you're talking about, thanks.
– proton
Dec 28 '18 at 23:46
1
Wow. That worked. Thanks a lot
– proton
Dec 28 '18 at 23:48
|
show 1 more comment
There seems to be a problem with the way sequelize parses concatenated SQL strings, take a look at the query: pastebin.com/u8tr4Xbt The image is the error gotten when I run that: imagebin.ca/v/4RbsQOAPaxvO
– proton
Dec 28 '18 at 23:40
That's becauseclause1andclause2are strings - you need to include quotes around the concatenated values when using strings
– ic3b3rg
Dec 28 '18 at 23:43
Something like this database.sequelize.literal(EXISTS(SELECT * FROM "Instalments" WHERE "Instalments"."loanId" = "Loan"."id" AND "Instalments"."status" IN ("${clause1}", "${clause2}"))) yeah? Still gives me the same error.
– proton
Dec 28 '18 at 23:45
If not, can you help share a sample of what you're talking about, thanks.
– proton
Dec 28 '18 at 23:46
1
Wow. That worked. Thanks a lot
– proton
Dec 28 '18 at 23:48
There seems to be a problem with the way sequelize parses concatenated SQL strings, take a look at the query: pastebin.com/u8tr4Xbt The image is the error gotten when I run that: imagebin.ca/v/4RbsQOAPaxvO
– proton
Dec 28 '18 at 23:40
There seems to be a problem with the way sequelize parses concatenated SQL strings, take a look at the query: pastebin.com/u8tr4Xbt The image is the error gotten when I run that: imagebin.ca/v/4RbsQOAPaxvO
– proton
Dec 28 '18 at 23:40
That's because
clause1 and clause2 are strings - you need to include quotes around the concatenated values when using strings– ic3b3rg
Dec 28 '18 at 23:43
That's because
clause1 and clause2 are strings - you need to include quotes around the concatenated values when using strings– ic3b3rg
Dec 28 '18 at 23:43
Something like this database.sequelize.literal(
EXISTS(SELECT * FROM "Instalments" WHERE "Instalments"."loanId" = "Loan"."id" AND "Instalments"."status" IN ("${clause1}", "${clause2}"))) yeah? Still gives me the same error.– proton
Dec 28 '18 at 23:45
Something like this database.sequelize.literal(
EXISTS(SELECT * FROM "Instalments" WHERE "Instalments"."loanId" = "Loan"."id" AND "Instalments"."status" IN ("${clause1}", "${clause2}"))) yeah? Still gives me the same error.– proton
Dec 28 '18 at 23:45
If not, can you help share a sample of what you're talking about, thanks.
– proton
Dec 28 '18 at 23:46
If not, can you help share a sample of what you're talking about, thanks.
– proton
Dec 28 '18 at 23:46
1
1
Wow. That worked. Thanks a lot
– proton
Dec 28 '18 at 23:48
Wow. That worked. Thanks a lot
– proton
Dec 28 '18 at 23:48
|
show 1 more comment
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53965349%2fhow-can-i-bind-a-variable-to-sequelize-literal%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
Are you familiar with concatenating variables to strings?
– Taplar
Dec 28 '18 at 23:26
Possible duplicate of How to interpolate variables in strings in JavaScript, without concatenation?
– Hydrothermal
Dec 28 '18 at 23:27
@Hydrothermal String concatenation doesn't work with sequelize literal
– proton
Dec 28 '18 at 23:27
1
What makes you say that?
literal()is being given a string as an argument. It doesn't matter how it's constructed. Just that it is a valid string.literal('abc')andliteral('ab'+ 'c')would both pass the same string to the method– Taplar
Dec 28 '18 at 23:29
There seems to be a problem with the way sequelize parses concatenated SQL strings, take a look at the query: pastebin.com/u8tr4Xbt The image is the error gotten when I run that: imagebin.ca/v/4RbsQOAPaxvO
– proton
Dec 28 '18 at 23:39