sequelize如何实现表的关联查询?

comment表有一个主键id,记录评论

likes表记录点赞,也有一个主键id,和一个commentId键,一篇comment可能会有好多likes

现在用sequelize查表(需要查询comment列表,以及每个comment的likes列表)并处理,返回合适的数据结构给前端。

要怎么做?

关联:
commentTable.hasMany(likeTable, {foreignKey: 'commentId', targetKey: 'id'});

查询:

 comment: (params) => commentTable.findAll({
        ...params, include: [{
            model: likeTable,
            where:{commentId:sequelize.col('comments.id')}
        }]
    }),

sql:

Executing (default): SELECT `comments`.`id`, `comments`.`title`, `comments`.`comment`, `comments`.`replyUserId`, `comments`.`replyId`, `comments`.`articleId`, `comments`.`userId`, `comments`.`userNickname`, `comments`.`userAvatar`, `comments`.`isRead`, `comments`.`createdTime`, `comments`.`flag`, `likes`.`id` AS `likes.id`, `likes`.`userId` AS `likes.userId`, `likes`.`username` AS `likes.username`, `likes`.`commentId` AS `likes.commentId`, `likes`.`status` AS `likes.status` FROM `comment` AS `comments` INNER JOIN `likes` AS `likes` ON `comments`.`id` = `likes`.`commentId` AND `comments`.`id` WHERE `comments`.`replyUserId` = '0' AND `comments`.`isRead` = '0' AND `comments`.`flag` = 0;

还是查不到结果

原来sql语句用了inner join
导致了没有数据
把它改为left join就可以了

comment: (params) => commentTable.findAll({
        ...params,
        include: [{
            model: likeTable,
            where:{commentId:sequelize.col('comments.id')},
            required:false  //left join
        }]
    }),
阅读 694
评论
    2 个回答
    CBCzed
    • 111

    按照你的描述,这像是1:n的关系,comment为1,likes为n,可以在comment的模型上,构建一个1:n的关系,comment.hasMany(model.likes, {tragetId: commentId})

    楼主编辑:
    已解决
    需要先关联

    commentTable.hasMany(likeTable, {foreignKey: 'commentId', targetKey: 'id'});

    再用left join和where查询

    comment: (params) => commentTable.findAll({
            ...params,
            include: [{
                model: likeTable,
                where:{commentId:sequelize.col('comments.id')},
                required:false
            }]
        }),

    得到的数据结构就是这样的
    截屏2019-11-0715.00.22.png

    评论 赞赏