Sequelize js how to use database functions with a model

Question

my use case is something like this,

Users can review the dishes.
Each review is stored in a table called ratings.
When a new record inserted to the ratings table, Dish tables overall rating column should be updated with the average rating value.

I go though the docs and some questions in the stack-overflow and GitHub also. What I have done so far is.

const db = require("../models");
const Sequelize = require('sequelize');

export async function create(req, res, next) {
    try {
        const body = req.swagger.params.body.value;
        const rate = await db.ratings.create(body, {
            fields: ['user_id', 'dish', 'overall', 'notes', 'createdAt', 'updatedAt']
        })

        const average = await Sequelize.fn('AVG', Sequelize.col('overall'))
        res.sendStatus(200);
    } catch (error) {
        console.log(error);
        res.status(500).json(error)
    }
}

I think average function is

Sequelize.fn('AVG', Sequelize.col('overall'))

But I don't know how to call it on the model ratings

Vivek Doshi · Accepted Answer · 2018-08-29 04:27:57Z

2

This is how you can use with attributes :

db.ratings.findAll({
    attributes : [ [Sequelize.fn('AVG', Sequelize.col('overall')),'overall'] ] // <--- All you need is this
}).then(data => {
    console.log(data);
})

For more detail : DO READ

edited Aug 29, 2018 at 4:27

answered Aug 29, 2018 at 3:20

Vivek Doshi

58.8k12 gold badges120 silver badges126 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

margherita pizza Over a year ago

First things first, thanks a lot for continuously helping me to improve. =) I have changed my query to this const average = await db.ratings.findAll({ where:{ dish:1 }, attributes: [sequelize.fn('AVG', sequelize.col('overall'))] }) then the executed query was this SELECT AVG(overall) FROM ratings AS ratings WHERE ratings.dish = 1; But I haven't get anyoutput. I got only an empty object. But I copy the excuted query and run on the phpmyadmin then query excuted and gives me the output.

margherita pizza Over a year ago

What could went wrong here? "findAll" will gives all the columns and pass the "attributes" will provide only mentioned columns. So I didn't mention any column is it the reason here?

Vivek Doshi Over a year ago

@PathumSamararathna , have updated the answer , please try that one

Collectives™ on Stack Overflow

Sequelize js how to use database functions with a model

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related