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.

user3026715
  • 404
  • 3
  • 5
  • 19
  • 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 '19 at 06: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 '19 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 '19 at 14:02
  • @user3026715 is your issue resolved or you are still facing it ? – Pankaj Jindal Mar 29 '19 at 06: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 '19 at 18:22

1 Answers1

0

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
  • 404
  • 3
  • 5
  • 19