Sequelize with MYSQL: Raw query returns a “duplicate” result
0
I have this method that performs a raw query:
Friendship.getFriends= async (userId)=>{
const result = await sequelize.query(`select id,email from users where
users.id in(SELECT friendId FROM friendships where friendships.userId =
${userId})`);
return result;
};
The result seems to contain the same exact data, but twice:
[ [ TextRow { id: 6, email: 'example3@gmail.com' },
TextRow { id: 1, email: 'yoyo@gmail.com' } ],
[ TextRow { id: 6, email: 'example3@gmail.com' },
TextRow { id: 1, email: 'yoyo@gmail.com' } ] ]
Only two records should actually be found by this query(id's 1 and 6), yet it returns an array with the same records twice.
Can somebody explain me what's going on here?
Edit: the models:
module.exports = (sequelize, DataTypes) => {
const User = sequelize.define('User', {
email: { type: DataTypes.STRING, unique: true },
password: DataTypes.STRING,
isActive:{type:DataTypes.BOOLEAN,defaultValue:true}
});
module.exports = (sequelize, DataTypes) => {
const Friendship = sequelize.define('Friendship', {
userId: DataTypes.INTEGER,
friendId: DataTypes.INTEGER,
});
mysql node.js sequelize.js
asked Nov 24 '18 at 17:36
sheff2k1sheff2k1
380212
add a comment |
0
I have this method that performs a raw query:
Friendship.getFriends= async (userId)=>{
const result = await sequelize.query(`select id,email from users where
users.id in(SELECT friendId FROM friendships where friendships.userId =
${userId})`);
return result;
};
The result seems to contain the same exact data, but twice:
[ [ TextRow { id: 6, email: 'example3@gmail.com' },
TextRow { id: 1, email: 'yoyo@gmail.com' } ],
[ TextRow { id: 6, email: 'example3@gmail.com' },
TextRow { id: 1, email: 'yoyo@gmail.com' } ] ]
Only two records should actually be found by this query(id's 1 and 6), yet it returns an array with the same records twice.
Can somebody explain me what's going on here?
Edit: the models:
module.exports = (sequelize, DataTypes) => {
const User = sequelize.define('User', {
email: { type: DataTypes.STRING, unique: true },
password: DataTypes.STRING,
isActive:{type:DataTypes.BOOLEAN,defaultValue:true}
});
module.exports = (sequelize, DataTypes) => {
const Friendship = sequelize.define('Friendship', {
userId: DataTypes.INTEGER,
friendId: DataTypes.INTEGER,
});
mysql node.js sequelize.js
asked Nov 24 '18 at 17:36
sheff2k1sheff2k1
380212
add a comment |
0
0
0
I have this method that performs a raw query:
Friendship.getFriends= async (userId)=>{
const result = await sequelize.query(`select id,email from users where
users.id in(SELECT friendId FROM friendships where friendships.userId =
${userId})`);
return result;
};
The result seems to contain the same exact data, but twice:
[ [ TextRow { id: 6, email: 'example3@gmail.com' },
TextRow { id: 1, email: 'yoyo@gmail.com' } ],
[ TextRow { id: 6, email: 'example3@gmail.com' },
TextRow { id: 1, email: 'yoyo@gmail.com' } ] ]
Only two records should actually be found by this query(id's 1 and 6), yet it returns an array with the same records twice.
Can somebody explain me what's going on here?
Edit: the models:
module.exports = (sequelize, DataTypes) => {
const User = sequelize.define('User', {
email: { type: DataTypes.STRING, unique: true },
password: DataTypes.STRING,
isActive:{type:DataTypes.BOOLEAN,defaultValue:true}
});
module.exports = (sequelize, DataTypes) => {
const Friendship = sequelize.define('Friendship', {
userId: DataTypes.INTEGER,
friendId: DataTypes.INTEGER,
});
mysql node.js sequelize.js
asked Nov 24 '18 at 17:36
sheff2k1sheff2k1
380212
I have this method that performs a raw query:
Friendship.getFriends= async (userId)=>{
const result = await sequelize.query(`select id,email from users where
users.id in(SELECT friendId FROM friendships where friendships.userId =
${userId})`);
return result;
};
The result seems to contain the same exact data, but twice:
[ [ TextRow { id: 6, email: 'example3@gmail.com' },
TextRow { id: 1, email: 'yoyo@gmail.com' } ],
[ TextRow { id: 6, email: 'example3@gmail.com' },
TextRow { id: 1, email: 'yoyo@gmail.com' } ] ]
Only two records should actually be found by this query(id's 1 and 6), yet it returns an array with the same records twice.
Can somebody explain me what's going on here?
Edit: the models:
module.exports = (sequelize, DataTypes) => {
const User = sequelize.define('User', {
email: { type: DataTypes.STRING, unique: true },
password: DataTypes.STRING,
isActive:{type:DataTypes.BOOLEAN,defaultValue:true}
});
module.exports = (sequelize, DataTypes) => {
const Friendship = sequelize.define('Friendship', {
userId: DataTypes.INTEGER,
friendId: DataTypes.INTEGER,
});
mysql node.js sequelize.js
mysql node.js sequelize.js
asked Nov 24 '18 at 17:36
sheff2k1sheff2k1
380212
asked Nov 24 '18 at 17:36
sheff2k1sheff2k1
380212
edited Nov 25 '18 at 11:19
sheff2k1
asked Nov 24 '18 at 17:36
sheff2k1sheff2k1
380212
asked Nov 24 '18 at 17:36
sheff2k1sheff2k1
380212
asked Nov 24 '18 at 17:36
sheff2k1sheff2k1
380212
380212
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
1
Try to set query type to SELECT in the second argument.
sequelize.query(queryString, {type: sequelize.QueryTypes.SELECT})
answered Nov 24 '18 at 18:00
DmytroKDmytroK
1015
Works, thank you.
– sheff2k1
Nov 24 '18 at 18:04
add a comment |
1
I am not sure but try below code.
const result = await sequelize.query("select id,email from users where
users.id in(SELECT friendId FROM friendships where friendships.userId =
${userId})", {type: sequelize.QueryTypes.SELECT});
One more thing : Use join instead of in
answered Nov 24 '18 at 18:05
Prayag C. PatelPrayag C. Patel
687
Yeah hehe, thats what the previous commenter wrote..it works. About the join: I was trying to construct it correctly, with no success. My SQL is very rusty. Could u write the join version of that query?
– sheff2k1
Nov 24 '18 at 18:15
select id,email from users u inner join friendships f on u.id = f.userId where u.id = ${userId}
– Prayag C. Patel
Nov 24 '18 at 18:21
Well that's actually the query i came up with, before switching to the sub query alternative. This one doesn't work properly. It returns the email of the current user ("userId"), instead of the "friend"(some other user). So i basically get a list of friends, with "my own email"
– sheff2k1
Nov 24 '18 at 19:02
Can you please share that two table schema. So, we can write perfect query.
– Prayag C. Patel
Nov 25 '18 at 4:40
select id,email from users u inner join friendships f on u.id = f. friendId where f. userId = ${userId}
– Prayag C. Patel
Nov 25 '18 at 17:29
|
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%2f53460754%2fsequelize-with-mysql-raw-query-returns-a-duplicate-result%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
Try to set query type to SELECT in the second argument.
sequelize.query(queryString, {type: sequelize.QueryTypes.SELECT})
answered Nov 24 '18 at 18:00
DmytroKDmytroK
1015
Works, thank you.
– sheff2k1
Nov 24 '18 at 18:04
add a comment |
1
Try to set query type to SELECT in the second argument.
sequelize.query(queryString, {type: sequelize.QueryTypes.SELECT})
answered Nov 24 '18 at 18:00
DmytroKDmytroK
1015
Works, thank you.
– sheff2k1
Nov 24 '18 at 18:04
add a comment |
1
1
1
Try to set query type to SELECT in the second argument.
sequelize.query(queryString, {type: sequelize.QueryTypes.SELECT})
answered Nov 24 '18 at 18:00
DmytroKDmytroK
1015
Try to set query type to SELECT in the second argument.
sequelize.query(queryString, {type: sequelize.QueryTypes.SELECT})
answered Nov 24 '18 at 18:00
DmytroKDmytroK
1015
answered Nov 24 '18 at 18:00
DmytroKDmytroK
1015
answered Nov 24 '18 at 18:00
DmytroKDmytroK
1015
answered Nov 24 '18 at 18:00
DmytroKDmytroK
1015
1015
Works, thank you.
– sheff2k1
Nov 24 '18 at 18:04
add a comment |
Works, thank you.
– sheff2k1
Nov 24 '18 at 18:04
Works, thank you.
– sheff2k1
Nov 24 '18 at 18:04
Works, thank you.
– sheff2k1
Nov 24 '18 at 18:04
add a comment |
1
I am not sure but try below code.
const result = await sequelize.query("select id,email from users where
users.id in(SELECT friendId FROM friendships where friendships.userId =
${userId})", {type: sequelize.QueryTypes.SELECT});
One more thing : Use join instead of in
answered Nov 24 '18 at 18:05
Prayag C. PatelPrayag C. Patel
687
Yeah hehe, thats what the previous commenter wrote..it works. About the join: I was trying to construct it correctly, with no success. My SQL is very rusty. Could u write the join version of that query?
– sheff2k1
Nov 24 '18 at 18:15
select id,email from users u inner join friendships f on u.id = f.userId where u.id = ${userId}
– Prayag C. Patel
Nov 24 '18 at 18:21
Well that's actually the query i came up with, before switching to the sub query alternative. This one doesn't work properly. It returns the email of the current user ("userId"), instead of the "friend"(some other user). So i basically get a list of friends, with "my own email"
– sheff2k1
Nov 24 '18 at 19:02
Can you please share that two table schema. So, we can write perfect query.
– Prayag C. Patel
Nov 25 '18 at 4:40
select id,email from users u inner join friendships f on u.id = f. friendId where f. userId = ${userId}
– Prayag C. Patel
Nov 25 '18 at 17:29
|
show 1 more comment
1
I am not sure but try below code.
const result = await sequelize.query("select id,email from users where
users.id in(SELECT friendId FROM friendships where friendships.userId =
${userId})", {type: sequelize.QueryTypes.SELECT});
One more thing : Use join instead of in
answered Nov 24 '18 at 18:05
Prayag C. PatelPrayag C. Patel
687
Yeah hehe, thats what the previous commenter wrote..it works. About the join: I was trying to construct it correctly, with no success. My SQL is very rusty. Could u write the join version of that query?
– sheff2k1
Nov 24 '18 at 18:15
select id,email from users u inner join friendships f on u.id = f.userId where u.id = ${userId}
– Prayag C. Patel
Nov 24 '18 at 18:21
Well that's actually the query i came up with, before switching to the sub query alternative. This one doesn't work properly. It returns the email of the current user ("userId"), instead of the "friend"(some other user). So i basically get a list of friends, with "my own email"
– sheff2k1
Nov 24 '18 at 19:02
Can you please share that two table schema. So, we can write perfect query.
– Prayag C. Patel
Nov 25 '18 at 4:40
select id,email from users u inner join friendships f on u.id = f. friendId where f. userId = ${userId}
– Prayag C. Patel
Nov 25 '18 at 17:29
|
show 1 more comment
1
1
1
I am not sure but try below code.
const result = await sequelize.query("select id,email from users where
users.id in(SELECT friendId FROM friendships where friendships.userId =
${userId})", {type: sequelize.QueryTypes.SELECT});
One more thing : Use join instead of in
answered Nov 24 '18 at 18:05
Prayag C. PatelPrayag C. Patel
687
I am not sure but try below code.
const result = await sequelize.query("select id,email from users where
users.id in(SELECT friendId FROM friendships where friendships.userId =
${userId})", {type: sequelize.QueryTypes.SELECT});
One more thing : Use join instead of in
answered Nov 24 '18 at 18:05
Prayag C. PatelPrayag C. Patel
687
answered Nov 24 '18 at 18:05
Prayag C. PatelPrayag C. Patel
687
answered Nov 24 '18 at 18:05
Prayag C. PatelPrayag C. Patel
687
answered Nov 24 '18 at 18:05
Prayag C. PatelPrayag C. Patel
687
687
Yeah hehe, thats what the previous commenter wrote..it works. About the join: I was trying to construct it correctly, with no success. My SQL is very rusty. Could u write the join version of that query?
– sheff2k1
Nov 24 '18 at 18:15
select id,email from users u inner join friendships f on u.id = f.userId where u.id = ${userId}
– Prayag C. Patel
Nov 24 '18 at 18:21
Well that's actually the query i came up with, before switching to the sub query alternative. This one doesn't work properly. It returns the email of the current user ("userId"), instead of the "friend"(some other user). So i basically get a list of friends, with "my own email"
– sheff2k1
Nov 24 '18 at 19:02
Can you please share that two table schema. So, we can write perfect query.
– Prayag C. Patel
Nov 25 '18 at 4:40
select id,email from users u inner join friendships f on u.id = f. friendId where f. userId = ${userId}
– Prayag C. Patel
Nov 25 '18 at 17:29
|
show 1 more comment
Yeah hehe, thats what the previous commenter wrote..it works. About the join: I was trying to construct it correctly, with no success. My SQL is very rusty. Could u write the join version of that query?
– sheff2k1
Nov 24 '18 at 18:15
select id,email from users u inner join friendships f on u.id = f.userId where u.id = ${userId}
– Prayag C. Patel
Nov 24 '18 at 18:21
Well that's actually the query i came up with, before switching to the sub query alternative. This one doesn't work properly. It returns the email of the current user ("userId"), instead of the "friend"(some other user). So i basically get a list of friends, with "my own email"
– sheff2k1
Nov 24 '18 at 19:02
Can you please share that two table schema. So, we can write perfect query.
– Prayag C. Patel
Nov 25 '18 at 4:40
select id,email from users u inner join friendships f on u.id = f. friendId where f. userId = ${userId}
– Prayag C. Patel
Nov 25 '18 at 17:29
Yeah hehe, thats what the previous commenter wrote..it works. About the join: I was trying to construct it correctly, with no success. My SQL is very rusty. Could u write the join version of that query?
– sheff2k1
Nov 24 '18 at 18:15
Yeah hehe, thats what the previous commenter wrote..it works. About the join: I was trying to construct it correctly, with no success. My SQL is very rusty. Could u write the join version of that query?
– sheff2k1
Nov 24 '18 at 18:15
select id,email from users u inner join friendships f on u.id = f.userId where u.id = ${userId}
– Prayag C. Patel
Nov 24 '18 at 18:21
select id,email from users u inner join friendships f on u.id = f.userId where u.id = ${userId}
– Prayag C. Patel
Nov 24 '18 at 18:21
Well that's actually the query i came up with, before switching to the sub query alternative. This one doesn't work properly. It returns the email of the current user ("userId"), instead of the "friend"(some other user). So i basically get a list of friends, with "my own email"
– sheff2k1
Nov 24 '18 at 19:02
Well that's actually the query i came up with, before switching to the sub query alternative. This one doesn't work properly. It returns the email of the current user ("userId"), instead of the "friend"(some other user). So i basically get a list of friends, with "my own email"
– sheff2k1
Nov 24 '18 at 19:02
Can you please share that two table schema. So, we can write perfect query.
– Prayag C. Patel
Nov 25 '18 at 4:40
Can you please share that two table schema. So, we can write perfect query.
– Prayag C. Patel
Nov 25 '18 at 4:40
select id,email from users u inner join friendships f on u.id = f. friendId where f. userId = ${userId}
– Prayag C. Patel
Nov 25 '18 at 17:29
select id,email from users u inner join friendships f on u.id = f. friendId where f. userId = ${userId}
– Prayag C. Patel
Nov 25 '18 at 17:29
|
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%2f53460754%2fsequelize-with-mysql-raw-query-returns-a-duplicate-result%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
