How to use .findAll and find records from two different tables and a self reference?How to find all the tables in MySQL with specific column names in them?How can I find all records for a model without doing a long list of “OR” conditions?“people you may know” sql queryHow to reference two tables using hasOne with sequelize.jsHow to SELF JOIN using Sequelize in NodeTwo tables referring to each otherSkip record from one table if record has associatied record in other tableSequelize JS - Which do I use? Model.key.references or 'Associations'?Composite primary key in junction table - SequelizeHow to find data in Sequelize using data in child tables
Shortest distance around a pyramid?
Flatten array with OPENJSON: OPENJSON on a value that may not be an array? [ [1] ], vs [1]
How might the United Kingdom become a republic?
When did the Roman Empire fall according to contemporaries?
Is there any word for "disobedience to God"?
What do the horizontal lines in a P-V phase diagram mean?
As the Dungeon Master, how do I handle a player that insists on a specific class when I already know that choice will cause issues?
Should disabled buttons give feedback when clicked?
Can I play a first turn Simic Growth Chamber to have 3 mana available in the second turn?
If the railway suggests a 5-min connection window for changing trains in the Netherlands, does that mean it's definitely doable?
If your plane is out-of-control, why does military training instruct releasing the joystick to neutralize controls?
How can I deal with a player trying to insert real-world mythology into my homebrew setting?
references on the empirical study on the practice of OR
Is Arc Length always irrational between two rational points?
Do you know your 'KVZ's?
Can fluent English speakers distinguish “steel”, “still” and “steal”?
What is the best way to stacked subscripts for a matrix?
Are randomly-generated passwords starting with "a" less secure?
Is an acid a salt or not?
Novel where a group of scientists in a spaceship encounter various aliens
How to hide what's behind an object in a non destructive way / give it an "invisibility cloak"
Why are they 'nude photos'?
<schwitz>, <zwinker> etc. Does German always use 2nd Person Singular Imperative verbs for emoticons? If so, why?
Why was hardware diversification an asset for the IBM PC ecosystem?
How to use .findAll and find records from two different tables and a self reference?
How to find all the tables in MySQL with specific column names in them?How can I find all records for a model without doing a long list of “OR” conditions?“people you may know” sql queryHow to reference two tables using hasOne with sequelize.jsHow to SELF JOIN using Sequelize in NodeTwo tables referring to each otherSkip record from one table if record has associatied record in other tableSequelize JS - Which do I use? Model.key.references or 'Associations'?Composite primary key in junction table - SequelizeHow to find data in Sequelize using data in child tables
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;
I'm working with two tables in particular. Users and Friends. Users has a bunch of information that defines the User whereas Friends has two columns aside from id: user_id and friend_id where both of them are a reference to the User table.
I'm trying to find all of the users friends in as little calls to the db as possible and I currently have 2. One to retrieve the id of a user first from a request, then another to Friends where I compare the IDs from the first call and then a third call that passes the array of friends and find all of them in the Users table. This already feels like overkill and I think that with associations, there has to be a better way.
Modification of the tables unfortunately is not an option.
One thing that I saw from "http://docs.sequelizejs.com/manual/querying.html#relations---associations"
I tried but got an interesting error.. when trying to repurpose the code snippet in the link under Relations/Associations, I get "user is associated to friends multiple times. To identify the correct association, you must use the 'as' keyword to specify the alias of the association you want to include."
const userRecord = await User.findOne(
where: id
)
const friendsIDs = await Friends.findAll(
attributes: ["friend_id"],
where:
user_id: userRecord.id
).then(results => results.map(result => result.friend_id));
const Sequelize = require("sequelize");
const Op = Sequelize.Op;
return await User.findAll(
where:
id: [Op.in]: friendsIDs
,
);
Above for my use case works. I'm just wondering if there are ways to cut down the number of calls to the db.
mysql node.js database sequelize.js sequelize-cli
asked Mar 26 at 3:15
user3026715user3026715
4110 bronze badges
add a comment |
I'm working with two tables in particular. Users and Friends. Users has a bunch of information that defines the User whereas Friends has two columns aside from id: user_id and friend_id where both of them are a reference to the User table.
I'm trying to find all of the users friends in as little calls to the db as possible and I currently have 2. One to retrieve the id of a user first from a request, then another to Friends where I compare the IDs from the first call and then a third call that passes the array of friends and find all of them in the Users table. This already feels like overkill and I think that with associations, there has to be a better way.
Modification of the tables unfortunately is not an option.
One thing that I saw from "http://docs.sequelizejs.com/manual/querying.html#relations---associations"
I tried but got an interesting error.. when trying to repurpose the code snippet in the link under Relations/Associations, I get "user is associated to friends multiple times. To identify the correct association, you must use the 'as' keyword to specify the alias of the association you want to include."
const userRecord = await User.findOne(
where: id
)
const friendsIDs = await Friends.findAll(
attributes: ["friend_id"],
where:
user_id: userRecord.id
).then(results => results.map(result => result.friend_id));
const Sequelize = require("sequelize");
const Op = Sequelize.Op;
return await User.findAll(
where:
id: [Op.in]: friendsIDs
,
);
Above for my use case works. I'm just wondering if there are ways to cut down the number of calls to the db.
mysql node.js database sequelize.js sequelize-cli
asked Mar 26 at 3:15
user3026715user3026715
4110 bronze badges
Why you are calling this query, as you already have the user id. const userRecord = await User.findOne( where: id ) Did you try using join on friends table, if so please mention the query that you used ? Did you added user_id and friend_id as foreign key in friend table ?
– Pankaj Jindal
Mar 26 at 6:03
Why not use raw SQL query? Because your requirement is not needing any associations data of final findAll. SELECT * FROM users where id IN (SuB QUERY). Even if don't want to use raw SQL completely, you can use - where: Sequelize.literal("(Sub Query)");
– Rohit Dalal
Mar 26 at 12:21
@PankajJindal yes user_id and friend_id are foreign keys in the friend table. I'm not sure I understand the confusion. The front end makes a call to a specific back end endpoint and passes the user_id to search for friends
– user3026715
Mar 26 at 14:02
@user3026715 is your issue resolved or you are still facing it ?
– Pankaj Jindal
Mar 29 at 6:13
Turns out Sequelize handles this for you if you have the proper associations in place so yes, it was a one liner user.getFriends() for me.
– user3026715
Mar 29 at 18:22
add a comment |
I'm working with two tables in particular. Users and Friends. Users has a bunch of information that defines the User whereas Friends has two columns aside from id: user_id and friend_id where both of them are a reference to the User table.
I'm trying to find all of the users friends in as little calls to the db as possible and I currently have 2. One to retrieve the id of a user first from a request, then another to Friends where I compare the IDs from the first call and then a third call that passes the array of friends and find all of them in the Users table. This already feels like overkill and I think that with associations, there has to be a better way.
Modification of the tables unfortunately is not an option.
One thing that I saw from "http://docs.sequelizejs.com/manual/querying.html#relations---associations"
I tried but got an interesting error.. when trying to repurpose the code snippet in the link under Relations/Associations, I get "user is associated to friends multiple times. To identify the correct association, you must use the 'as' keyword to specify the alias of the association you want to include."
const userRecord = await User.findOne(
where: id
)
const friendsIDs = await Friends.findAll(
attributes: ["friend_id"],
where:
user_id: userRecord.id
).then(results => results.map(result => result.friend_id));
const Sequelize = require("sequelize");
const Op = Sequelize.Op;
return await User.findAll(
where:
id: [Op.in]: friendsIDs
,
);
Above for my use case works. I'm just wondering if there are ways to cut down the number of calls to the db.
mysql node.js database sequelize.js sequelize-cli
asked Mar 26 at 3:15
user3026715user3026715
4110 bronze badges
I'm working with two tables in particular. Users and Friends. Users has a bunch of information that defines the User whereas Friends has two columns aside from id: user_id and friend_id where both of them are a reference to the User table.
I'm trying to find all of the users friends in as little calls to the db as possible and I currently have 2. One to retrieve the id of a user first from a request, then another to Friends where I compare the IDs from the first call and then a third call that passes the array of friends and find all of them in the Users table. This already feels like overkill and I think that with associations, there has to be a better way.
Modification of the tables unfortunately is not an option.
One thing that I saw from "http://docs.sequelizejs.com/manual/querying.html#relations---associations"
I tried but got an interesting error.. when trying to repurpose the code snippet in the link under Relations/Associations, I get "user is associated to friends multiple times. To identify the correct association, you must use the 'as' keyword to specify the alias of the association you want to include."
const userRecord = await User.findOne(
where: id
)
const friendsIDs = await Friends.findAll(
attributes: ["friend_id"],
where:
user_id: userRecord.id
).then(results => results.map(result => result.friend_id));
const Sequelize = require("sequelize");
const Op = Sequelize.Op;
return await User.findAll(
where:
id: [Op.in]: friendsIDs
,
);
Above for my use case works. I'm just wondering if there are ways to cut down the number of calls to the db.
mysql node.js database sequelize.js sequelize-cli
mysql node.js database sequelize.js sequelize-cli
asked Mar 26 at 3:15
user3026715user3026715
4110 bronze badges
asked Mar 26 at 3:15
user3026715user3026715
4110 bronze badges
asked Mar 26 at 3:15
user3026715user3026715
4110 bronze badges
asked Mar 26 at 3:15
user3026715user3026715
4110 bronze badges
asked Mar 26 at 3:15
user3026715user3026715
4110 bronze badges
4110 bronze badges
Why you are calling this query, as you already have the user id. const userRecord = await User.findOne( where: id ) Did you try using join on friends table, if so please mention the query that you used ? Did you added user_id and friend_id as foreign key in friend table ?
– Pankaj Jindal
Mar 26 at 6:03
Why not use raw SQL query? Because your requirement is not needing any associations data of final findAll. SELECT * FROM users where id IN (SuB QUERY). Even if don't want to use raw SQL completely, you can use - where: Sequelize.literal("(Sub Query)");
– Rohit Dalal
Mar 26 at 12:21
@PankajJindal yes user_id and friend_id are foreign keys in the friend table. I'm not sure I understand the confusion. The front end makes a call to a specific back end endpoint and passes the user_id to search for friends
– user3026715
Mar 26 at 14:02
@user3026715 is your issue resolved or you are still facing it ?
– Pankaj Jindal
Mar 29 at 6:13
Turns out Sequelize handles this for you if you have the proper associations in place so yes, it was a one liner user.getFriends() for me.
– user3026715
Mar 29 at 18:22
add a comment |
Why you are calling this query, as you already have the user id. const userRecord = await User.findOne( where: id ) Did you try using join on friends table, if so please mention the query that you used ? Did you added user_id and friend_id as foreign key in friend table ?
– Pankaj Jindal
Mar 26 at 6:03
Why not use raw SQL query? Because your requirement is not needing any associations data of final findAll. SELECT * FROM users where id IN (SuB QUERY). Even if don't want to use raw SQL completely, you can use - where: Sequelize.literal("(Sub Query)");
– Rohit Dalal
Mar 26 at 12:21
@PankajJindal yes user_id and friend_id are foreign keys in the friend table. I'm not sure I understand the confusion. The front end makes a call to a specific back end endpoint and passes the user_id to search for friends
– user3026715
Mar 26 at 14:02
@user3026715 is your issue resolved or you are still facing it ?
– Pankaj Jindal
Mar 29 at 6:13
Turns out Sequelize handles this for you if you have the proper associations in place so yes, it was a one liner user.getFriends() for me.
– user3026715
Mar 29 at 18:22
Why you are calling this query, as you already have the user id. const userRecord = await User.findOne( where: id ) Did you try using join on friends table, if so please mention the query that you used ? Did you added user_id and friend_id as foreign key in friend table ?
– Pankaj Jindal
Mar 26 at 6:03
Why you are calling this query, as you already have the user id. const userRecord = await User.findOne( where: id ) Did you try using join on friends table, if so please mention the query that you used ? Did you added user_id and friend_id as foreign key in friend table ?
– Pankaj Jindal
Mar 26 at 6:03
Why not use raw SQL query? Because your requirement is not needing any associations data of final findAll. SELECT * FROM users where id IN (SuB QUERY). Even if don't want to use raw SQL completely, you can use - where: Sequelize.literal("(Sub Query)");
– Rohit Dalal
Mar 26 at 12:21
Why not use raw SQL query? Because your requirement is not needing any associations data of final findAll. SELECT * FROM users where id IN (SuB QUERY). Even if don't want to use raw SQL completely, you can use - where: Sequelize.literal("(Sub Query)");
– Rohit Dalal
Mar 26 at 12:21
@PankajJindal yes user_id and friend_id are foreign keys in the friend table. I'm not sure I understand the confusion. The front end makes a call to a specific back end endpoint and passes the user_id to search for friends
– user3026715
Mar 26 at 14:02
@PankajJindal yes user_id and friend_id are foreign keys in the friend table. I'm not sure I understand the confusion. The front end makes a call to a specific back end endpoint and passes the user_id to search for friends
– user3026715
Mar 26 at 14:02
@user3026715 is your issue resolved or you are still facing it ?
– Pankaj Jindal
Mar 29 at 6:13
@user3026715 is your issue resolved or you are still facing it ?
– Pankaj Jindal
Mar 29 at 6:13
Turns out Sequelize handles this for you if you have the proper associations in place so yes, it was a one liner user.getFriends() for me.
– user3026715
Mar 29 at 18:22
Turns out Sequelize handles this for you if you have the proper associations in place so yes, it was a one liner user.getFriends() for me.
– user3026715
Mar 29 at 18:22
add a comment |
1 Answer
1
active
oldest
votes
Turns out Sequelize handles this for you if you have the proper associations in place so yes, it was a one liner user.getFriends() for me.
answered Mar 29 at 18:24
user3026715user3026715
4110 bronze badges
add a 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
);
);
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%2f55349331%2fhow-to-use-findall-and-find-records-from-two-different-tables-and-a-self-refere%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
Turns out Sequelize handles this for you if you have the proper associations in place so yes, it was a one liner user.getFriends() for me.
answered Mar 29 at 18:24
user3026715user3026715
4110 bronze badges
add a comment |
Turns out Sequelize handles this for you if you have the proper associations in place so yes, it was a one liner user.getFriends() for me.
answered Mar 29 at 18:24
user3026715user3026715
4110 bronze badges
add a comment |
Turns out Sequelize handles this for you if you have the proper associations in place so yes, it was a one liner user.getFriends() for me.
answered Mar 29 at 18:24
user3026715user3026715
4110 bronze badges
Turns out Sequelize handles this for you if you have the proper associations in place so yes, it was a one liner user.getFriends() for me.
answered Mar 29 at 18:24
user3026715user3026715
4110 bronze badges
answered Mar 29 at 18:24
user3026715user3026715
4110 bronze badges
answered Mar 29 at 18:24
user3026715user3026715
4110 bronze badges
answered Mar 29 at 18:24
user3026715user3026715
4110 bronze badges
4110 bronze badges
add a comment |
add a comment |
Got a question that you can’t ask on public Stack Overflow? Learn more about sharing private information with Stack Overflow for Teams.
Got a question that you can’t ask on public Stack Overflow? Learn more about sharing private information with Stack Overflow for Teams.
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.
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%2f55349331%2fhow-to-use-findall-and-find-records-from-two-different-tables-and-a-self-refere%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
Why you are calling this query, as you already have the user id. const userRecord = await User.findOne( where: id ) Did you try using join on friends table, if so please mention the query that you used ? Did you added user_id and friend_id as foreign key in friend table ?
– Pankaj Jindal
Mar 26 at 6:03
Why not use raw SQL query? Because your requirement is not needing any associations data of final findAll. SELECT * FROM users where id IN (SuB QUERY). Even if don't want to use raw SQL completely, you can use - where: Sequelize.literal("(Sub Query)");
– Rohit Dalal
Mar 26 at 12:21
@PankajJindal yes user_id and friend_id are foreign keys in the friend table. I'm not sure I understand the confusion. The front end makes a call to a specific back end endpoint and passes the user_id to search for friends
– user3026715
Mar 26 at 14:02
@user3026715 is your issue resolved or you are still facing it ?
– Pankaj Jindal
Mar 29 at 6:13
Turns out Sequelize handles this for you if you have the proper associations in place so yes, it was a one liner user.getFriends() for me.
– user3026715
Mar 29 at 18:22