Skip to content

many-to-many on same table, one of the includes results are wrong #1741

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Open
shaharsol opened this issue Feb 2, 2025 · 0 comments
Open

many-to-many on same table, one of the includes results are wrong #1741

shaharsol opened this issue Feb 2, 2025 · 0 comments

Comments

@shaharsol
Copy link

shaharsol commented Feb 2, 2025

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.

this is the generated query extracted from the logs:

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';

and finally, here's an excerpt from the tables showing the Follow records this particular user is involved in, hence expected 3 following.

Image

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant