Sequelize – How to get reviews for post with likes count for each review

I have a function like this. It receives comments on the post. And it doesn’t work – it makes endless requests.

async getReviews(entityId) {
        let settings = {
            include: [{
                model: db.User,
                attributes: ['nickname', 'img', 'role'],
            }, {
                attributes: [],
                model: db.Like
            }],
            attributes: {
                include: [[db.Sequelize.fn("COUNT", db.Sequelize.col("likes.id")), "likesCount"]]
            },
            raw: true,
            nest: true
        }
        
        const reviews = await db.Review.findAll({
            where: {
                parentId: entityId,
                type: 'review',
                isDeleted: false
            },
            ...settings
        });
        
        await db.Review.includeNested(reviews, settings);
        
        return reviews;
    }

If we remove this line, the function returns one comment with counted likes.

await db.Review.includeNested(reviews, settings);

And if you remove this block:

            attributes: {
                include: [[db.Sequelize.fn("COUNT", db.Sequelize.col("likes.id")), "likesCount"]]
            },

… then the function will return an ARRAY of comments. Why does the like counting make only one comment come back?

How to get an array of reviews with counted likes? Here is the nesting function:

this.includeNested = async function f(reviews, settings) {
                const idArr = reviews.map(el => el.id);
                const children = await models.Review.findAll({
                    where: {
                        parentId: idArr
                    },
                    ...settings
                });
    
                reviews.forEach(review => {
                    review.children = children.filter(ch => ch.parentId === review.id);
                });
                
                if (children.length)
                    await this.includeNested(children, settings);
            }

endless query strings while full code:

Executing (default): SELECT `review`.`id`, `review`.`level`, `review`.`isDeleted`, `review`.`parentId`, `review`.`text`, `review`.`type`, `review`.`createdAt`, `review`.`updatedAt`, `review`.`userId`, COUNT(`likes`.`id`) AS `likesCo
unt`, `user`.`nickname` AS `user.nickname`, `user`.`img` AS `user.img`, `user`.`role` AS `user.role` FROM `reviews` AS `review` LEFT OUTER JOIN `users` AS `user` ON `review`.`userId` = `user`.`id` LEFT OUTER JOIN `likes` AS `likes`
ON `review`.`id` = `likes`.`reviewId` WHERE `review`.`parentId` IN (NULL);

without nested:

Executing (default): SELECT `review`.`id`, `review`.`level`, `review`.`isDeleted`, `review`.`parentId`, `review`.`text`, `review`.`type`, `review`.`createdAt`, `review`.`updatedAt`, `review`.`userId`, COUNT(`likes`.`id`) AS `likesCo
unt`, `user`.`nickname` AS `user.nickname`, `user`.`img` AS `user.img`, `user`.`role` AS `user.role` FROM `reviews` AS `review` LEFT OUTER JOIN `users` AS `user` ON `review`.`userId` = `user`.`id` LEFT OUTER JOIN `likes` AS `likes`
ON `review`.`id` = `likes`.`reviewId` WHERE `review`.`parentId` = '099d8a11-0fbe-420b-9a18-514989388c83' AND `review`.`type` = 'review' AND `review`.`isDeleted` = false;

without include: [[db.Sequelize.fn("COUNT"...:

Executing (default): SELECT `review`.`id`, `review`.`level`, `review`.`isDeleted`, `review`.`parentId`, `review`.`text`, `review`.`type`, `review`.`createdAt`, `review`.`updatedAt`, `review`.`userId`, `user`.`nickname` AS `user.nick
name`, `user`.`img` AS `user.img`, `user`.`role` AS `user.role`, `likes`.`id` AS `likes.id`, `likes`.`userId` AS `likes.userId`, `likes`.`reviewId` AS `likes.reviewId` FROM `reviews` AS `review` LEFT OUTER JOIN `users` AS `user` ON
`review`.`userId` = `user`.`id` LEFT OUTER JOIN `likes` AS `likes` ON `review`.`id` = `likes`.`reviewId` WHERE `review`.`parentId` = '099d8a11-0fbe-420b-9a18-514989388c83' AND `review`.`type` = 'review' AND `review`.`isDeleted` = fa
lse;

Answer

Try adding group by to reviews

const reviews = await db.Review.findAll({
    where: {
        parentId: entityId,
        type: 'review',
        isDeleted: false
    },
    // add group by id
    group: ["id"],
    ...settings
});

Advertisement

Answer