sequelize-typescript many-to-many on same table, include acts weird

Question

I am implementing a social network where a User can follow another user. So I have a Follow model:

export default class Follow extends Model {
    @PrimaryKey
    @ForeignKey(() => User)
    @Column(DataType.UUID)
    followerId: string;

    @PrimaryKey
    @ForeignKey(() => User)
    @Column(DataType.UUID)
    followeeId: string;
}

and in the User class I create the M:M relation:

@BelongsToMany(() => User, () => Follow, 'followerId')
following: User[]

@BelongsToMany(() => User, () => Follow, 'followeeId')
followers: User[]

Now when I fetch a User like so:

const me = await User.findByPk('1230ae30-dc4f-4752-bd84-092956f5c633', {
    include: [
        {
            model: User,
            as: 'followers'
        },{
            model: User,
            as: 'following'
        }
    ] 
})

I get the followers alright, but as for the following, I get a wrong result: it returns an array with a single item which is the User itself, whereas in the database I see 3 other users as following.

Any idea what I am doing wrong?

Edit: generated SQL is

SELECT
    `User`.`id`,
    `User`.`name`,
    `User`.`username`,
    `User`.`password`,
    `User`.`created_at` AS `createdAt`,
    `User`.`updated_at` AS `updatedAt`,
    `followers`.`id` AS `followers.id`,
    `followers`.`name` AS `followers.name`,
    `followers`.`username` AS `followers.username`,
    `followers`.`password` AS `followers.password`,
    `followers`.`created_at` AS `followers.createdAt`,
    `followers`.`updated_at` AS `followers.updatedAt`,
    `followers->Follow`.`follower_id` AS `followers.Follow.followerId`,
    `followers->Follow`.`followee_id` AS `followers.Follow.followeeId`,
    `followers->Follow`.`created_at` AS `followers.Follow.createdAt`,
    `followers->Follow`.`updated_at` AS `followers.Follow.updatedAt`,
    `following`.`id` AS `following.id`,
    `following`.`name` AS `following.name`,
    `following`.`username` AS `following.username`,
    `following`.`password` AS `following.password`,
    `following`.`created_at` AS `following.createdAt`,
    `following`.`updated_at` AS `following.updatedAt`,
    `following->Follow`.`follower_id` AS `following.Follow.followerId`,
    `following->Follow`.`followee_id` AS `following.Follow.followeeId`,
    `following->Follow`.`created_at` AS `following.Follow.createdAt`,
    `following->Follow`.`updated_at` AS `following.Follow.updatedAt`
FROM
    `users` AS `User`
    LEFT OUTER JOIN (
        `follows` AS `followers->Follow`
        INNER JOIN `users` AS `followers` ON `followers`.`id` = `followers->Follow`.`follower_id`
    ) ON `User`.`id` = `followers->Follow`.`followee_id`
    LEFT OUTER JOIN (
        `follows` AS `following->Follow`
        INNER JOIN `users` AS `following` ON `following`.`id` = `following->Follow`.`follower_id`
    ) ON `User`.`id` = `following->Follow`.`follower_id`
WHERE
    `User`.`id` = '1230ae30-dc4f-4752-bd84-092956f5c633';

The SQL of a query that includes only the following (the faulty part that returns a single record of the user itself instead of 3 other users):

SELECT
    `User`.`id`,
    `User`.`name`,
    `User`.`username`,
    `User`.`password`,
    `User`.`created_at` AS `createdAt`,
    `User`.`updated_at` AS `updatedAt`,
    `following`.`id` AS `following.id`,
    `following`.`name` AS `following.name`,
    `following`.`username` AS `following.username`,
    `following`.`password` AS `following.password`,
    `following`.`created_at` AS `following.createdAt`,
    `following`.`updated_at` AS `following.updatedAt`,
    `following->Follow`.`follower_id` AS `following.Follow.followerId`,
    `following->Follow`.`followee_id` AS `following.Follow.followeeId`,
    `following->Follow`.`created_at` AS `following.Follow.createdAt`,
    `following->Follow`.`updated_at` AS `following.Follow.updatedAt`
FROM
    `users` AS `User`
    LEFT OUTER JOIN (
        `follows` AS `following->Follow`
        INNER JOIN `users` AS `following` ON `following`.`id` = `following->Follow`.`follower_id`
    ) ON `User`.`id` = `following->Follow`.`follower_id`
WHERE
    `User`.`id` = '1230ae30-dc4f-4752-bd84-092956f5c633'

Edit: Now that I look at this query I see the mistake. Should have been:

FROM
    `users` AS `User`
    LEFT OUTER JOIN (
        `follows` AS `following->Follow`
        INNER JOIN `users` AS `following` ON `following`.`id` = `following->Follow`.`followee_id`
    ) ON `User`.`id` = `following->Follow`.`follower_id`
WHERE
    `User`.`id` = '1230ae30-dc4f-4752-bd84-092956f5c633'

If there's no bad on my end, it seems like Sequelize is doing something wrong. Moreover the followers come up right, which makes it all more weird.

*** solution *** use otherKey:

@BelongsToMany(() => User, () => Follow, 'followeeId', 'followerId')
followers: User[]

@BelongsToMany(() => User, () => Follow, 'followerId', 'followeeId')
following: User[]

Could you add the generated SQL for both queries in the post? — Anatoly
– Anatoly, Commented Jan 30 at 21:27
Also it's not recommended to fetch more than one many-to-many in one query. Try to divide it into two queries and check both results again — Anatoly
– Anatoly, Commented Jan 31 at 19:11
@Anatoly divided it to two queries and it works the same. the followers part comes in correctly but the following part ins't. — shaharsol
– shaharsol, Commented Feb 2 at 20:46
Could you replace the SQL with the separated following query SQL ? — Anatoly
– Anatoly, Commented Feb 3 at 21:32

Anatoly · Accepted Answer · 2025-02-08 23:00:39Z

1

You should indicate otherKey parameter as well in @BelongsToMany (it goes after foreignKey parameter, which you already indicated).
It helps you to indicate the correct field in the many-to-many table that corresponds to the model on the other end of M:N relationship.

Model1:
ID (PK)

M_N_Model:
ID (PK)
MODEL1_FK
MODEL2_FK

Model2:
ID (PK)

So in the above setup for MODEL1 it should look like:

@BelongsToMany(() => MODEL2, () => M_N_Model, 'MODEL1_FK', 'MODEL2_FK')

for MODEL2:

@BelongsToMany(() => MODEL1, () => M_N_Model, 'MODEL2_FK', 'MODEL1_FK')

answered Feb 8 at 23:00

Anatoly

23.2k3 gold badges33 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

sequelize-typescript many-to-many on same table, include acts weird

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related