Finding friends with sequelize associations

Question

I'm still rather new to sequelize with mysql, trying to better understand how associations work. I've created a simple app where user can add each other as friends, which if I'm correct would be a many-to-many relationship?

I have a users table and a friend_binds table. The users table is a typical users table and the friend_binds table is being created like this:

CREATE TABLE `friend_binds` (
  `id` char(36) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  `friend_id` char(36) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `parent_id` char(36) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `friendKey` (`parent_id`,`friend_id`),
  CONSTRAINT `friend_binds_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE

The way I originally had it working was every time I added a friend, two entries would get made to friend_binds (A to B and B to A). This method worked fine for me, and I would be able to pull friends regardless if they were A or B.

Original User model association:

User.associate = models => {
    User.hasMany(models.friendBind, {
      foreignKey: {
        name: 'parentId',
        allowNull: false
      },
      onDelete: 'cascade',
      hooks: true
    })
  }

Original FriendBinds model association:

FriendBind.associate = models => {
    FriendBind.belongsTo(models.user, {
      foreignKey: {
        name: 'parentId',
        type: DataTypes.UUID,
        allowNull: false
      },
      onDelete: 'cascade'
    })
  }

I'm trying to see if I can make it so I would only ever need one entry into my friend_binds table(i.e A to B only). I would eventually like to add a status column (sent, seen, rejected, etc) and I feel like having to update two rows every time is just doing it wrong.

The problem I'm running into right now is that when I do user.getFriends() it'll only get the friends where user is friend A (parent_id). Am I wrong in thinking that I should be able to find records if the user is A or B? Do I just need to .findAll() twice, once searching parentId and once searching friendId?

Here is the new users association

User.associate = models => {
    User.belongsToMany(models.user, {
      as: "friends",
      through: "friend_binds",
      foreignKey: "userId",
      otherKey: "friendId"
    })
  }

In this instance I read somewhere that I do not even need to define my friend_binds table as sequelize creates it for me so I'm doing it that way

The new query that gets ran for the friend_binds create table

CREATE TABLE `friend_binds` (
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `user_id` char(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  `friend_id` char(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  PRIMARY KEY (`user_id`,`friend_id`),
  KEY `friend_id` (`friend_id`),
  CONSTRAINT `friend_binds_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `friend_binds_ibfk_2` FOREIGN KEY (`friend_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE

The related code to find friends

module.exports.getFriendsNew = async (req, res) => {
  try {
    const user = await db.user
      .findByPk(req.user.id)
      .catch(error => { throw new Error(error) })

    const friends = await user
      .getFriends()
      .then(results => {
        if (results.length === 0) { throw new Error("You have no friends") }
        return results
      })
      .catch(error => { throw new Error(error) })

    res.send(friends)

  } catch (error) {
    res.send(error.message)
  }
}res.send(error)
  }

With this code, I can find friend A records, but if I search for friend B it returns 0 friends.

user_9266709 · Answer

A user can have many friend, and that friend, which is also a user can have many friends which is also users.

Definitely it is a many to many relationship between users and users.

To hold this relationship you need to define a pivot table. I would call it UsersFriends.

So the association that would you have will be like below.

User.belongsToMany(models.User, { 
  as: 'friends',
  foreignKey: 'user_id',
  through: UsersFriends
});
User.belongsToMany(models.User, { 
  as: 'userFriends',
  foreignKey: 'friend_id',
  through: UsersFriends
});

// UsersFriends 
module.exports = (sequelize, DataTypes) => {
  const TaskSparePart = sequelize.define('UsersFriends', {
    user_id: DataTypes.BIGINT,
    friend_id: DataTypes.BIGINT,
    created_at: DataTypes.DATE,
    updated_at: DataTypes.DATE,
  }, {
    tableName: 'users_friends',
  });
  return UsersFriends;
};

// To get friends
module.exports.getFriends = async (req, res) => {
  try {
     const user = await db.User.findOne({
        where: {
        id: req.user.id,
        },
        include: 'friends'
    })
    if (!user) throw new Error('User not found!');
    res.send(user.friends);
  } catch (error) {
    res.send(error.message)
  }
}

Finding friends with sequelize associations

1 Answers