SQL to Sequelize

Sequelize was going great, but I stopped in a problem, how could I make this statement valid for Sequelize, I was testing the COUNT functions and reading the documentation.

SELECT videos.*, COUNT(*) AS views FROM views INNER JOIN videos WHERE views.videoId = videos.id GROUP BY videoId ORDER BY views DESC

I tried

const data = await Views.count({
    group: 'videoId',
    include: [
        {
            model: Videos
        },

    ],
});

but COUNT ignores the ‘include’ attributes, obviously I’m not surprised that it doesn’t work but I couldn’t get something like that.

Answer

Model: Video.js

"use strict";
const { Model } = require("sequelize");
module.exports = (sequelize, DataTypes) => {
    class Video extends Model {
    }
    Video.init({
        title: { type: DataTypes.STRING, },
    }, {
        sequelize,
        modelName: "Video",
        defaultScope: {
            attributes: { exclude: ["createdAt", "updatedAt"] }
        },
    });
    return Video;
};

Model: View.js

"use strict";
const { Model } = require("sequelize");
module.exports = (sequelize, DataTypes) => {
    class View extends Model {
        static associate(models) {
            this.belongsTo(models.Video, {
                foreignKey: "videoId",
                as: 'video'
            });
        }
    }
    View.init({
        videoId: { type: DataTypes.INTEGER, },
    }, {
        sequelize,
        modelName: "View",
        defaultScope: {
            attributes: { exclude: ["createdAt", "updatedAt"] }
        },
    });
    return View;
};

Controller:

var v = await db.View.findAll({
    attributes: [
        [Sequelize.fn(`COUNT`, Sequelize.col("videoId")), 'count'],
    ],
    group: ['videoId'],
    include: ['video'],
})
console.log(JSON.parse(JSON.stringify(v)))

// output
[ { count: 8, video: { id: 1, title: 'k1' } } ]

Leave a Reply

Your email address will not be published. Required fields are marked *