.= 'tag.htm'; break; case 'flag': $pre .= $default_pre .= 'flag.htm'; break; case 'my': $pre .= $default_pre .= 'my.htm'; break; case 'my_password': $pre .= $default_pre .= 'my_password.htm'; break; case 'my_bind': $pre .= $default_pre .= 'my_bind.htm'; break; case 'my_avatar': $pre .= $default_pre .= 'my_avatar.htm'; break; case 'home_article': $pre .= $default_pre .= 'home_article.htm'; break; case 'home_comment': $pre .= $default_pre .= 'home_comment.htm'; break; case 'user': $pre .= $default_pre .= 'user.htm'; break; case 'user_login': $pre .= $default_pre .= 'user_login.htm'; break; case 'user_create': $pre .= $default_pre .= 'user_create.htm'; break; case 'user_resetpw': $pre .= $default_pre .= 'user_resetpw.htm'; break; case 'user_resetpw_complete': $pre .= $default_pre .= 'user_resetpw_complete.htm'; break; case 'user_comment': $pre .= $default_pre .= 'user_comment.htm'; break; case 'single_page': $pre .= $default_pre .= 'single_page.htm'; break; case 'search': $pre .= $default_pre .= 'search.htm'; break; case 'operate_sticky': $pre .= $default_pre .= 'operate_sticky.htm'; break; case 'operate_close': $pre .= $default_pre .= 'operate_close.htm'; break; case 'operate_delete': $pre .= $default_pre .= 'operate_delete.htm'; break; case 'operate_move': $pre .= $default_pre .= 'operate_move.htm'; break; case '404': $pre .= $default_pre .= '404.htm'; break; case 'read_404': $pre .= $default_pre .= 'read_404.htm'; break; case 'list_404': $pre .= $default_pre .= 'list_404.htm'; break; default: $pre .= $default_pre .= theme_mode_pre(); break; } if ($config['theme']) { $conffile = APP_PATH . 'view/template/' . $config['theme'] . '/conf.json'; $json = is_file($conffile) ? xn_json_decode(file_get_contents($conffile)) : array(); } !empty($json['installed']) and $path_file = APP_PATH . 'view/template/' . $config['theme'] . '/htm/' . ($id ? $id . '_' : '') . $pre; (empty($path_file) || !is_file($path_file)) and $path_file = APP_PATH . 'view/template/' . $config['theme'] . '/htm/' . $pre; if (!empty($config['theme_child']) && is_array($config['theme_child'])) { foreach ($config['theme_child'] as $theme) { if (empty($theme) || is_array($theme)) continue; $path_file = APP_PATH . 'view/template/' . $theme . '/htm/' . ($id ? $id . '_' : '') . $pre; !is_file($path_file) and $path_file = APP_PATH . 'view/template/' . $theme . '/htm/' . $pre; } } !is_file($path_file) and $path_file = APP_PATH . ($dir ? 'plugin/' . $dir . '/view/htm/' : 'view/htm/') . $default_pre; return $path_file; } function theme_mode_pre($type = 0) { global $config; $mode = $config['setting']['website_mode']; $pre = ''; if (1 == $mode) { $pre .= 2 == $type ? 'portal_category.htm' : 'portal.htm'; } elseif (2 == $mode) { $pre .= 2 == $type ? 'flat_category.htm' : 'flat.htm'; } else { $pre .= 2 == $type ? 'index_category.htm' : 'index.htm'; } return $pre; } ?>sequelize.js - sequelize-typescript many-to-many on same table, include acts weird - Stack Overflow

科技改变生活-雨落星辰 - 所有的伟大,都源于一个勇敢的开始

    最新消息：雨落星辰是一个专注网站SEO优化、网站SEO诊断、搜索引擎研究、网络营销推广、网站策划运营及站长类的自媒体原创博客
    你的位置： 首页>programmer>sequelize.js - sequelize-typescript many-to-many on same table, include acts weird - Stack Overflow

    sequelize.js - sequelize-typescript many-to-many on same table, include acts weird - Stack Overflow

    programmeradmin2浏览0评论

    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[]

    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[]
    • sequelize.js
    • many-to-many
    • sequelize-typescript
    Share Improve this question edited Feb 6 at 21:04 shaharsol asked Jan 30 at 12:24 shaharsolshaharsol 1,0222 gold badges13 silver badges33 bronze badges 9
    • Could you add the generated SQL for both queries in the post? – Anatoly Commented Jan 30 at 21:27
    • @Anatoly added to the post itself, too long for a comment – shaharsol Commented Jan 30 at 21:58
    • 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 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 Commented Feb 2 at 20:46
    • Could you replace the SQL with the separated following query SQL ? – Anatoly Commented Feb 3 at 21:32
     |  Show 4 more comments

    1 Answer 1

    Reset to default 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')

    与本文相关的文章

    发布评论

    评论列表(0)

    1. 暂无评论