CODEWITHSUNDEEP
mysqlnode.jsormsequelize.js
Mohhamad Hasham
Mohhamad Hasham

Reputation: 2032

How can I convert this raw query to sequelize ORM

The relationship between models is as:

I have three tables Staff,Modules,Tests

This is my raw query:

select *,count(distinct tests.testId),count(distinct staffModules.id) from modules 
left join tests on modules.moduleId = tests.moduleModuleId 
left join staffModules on modules.moduleId = staffModules.moduleModuleId
group by modules.moduleId
LIMIT 5 OFFSET 0

I have tried replicating it with Sequelize ORM but It doesn't work:

let resultModules = await modules.findAll({
            attributes: ['moduleName'],
            include: [{
                model: staff,
                attributes:['staffName'],
                through: { attributes: [[sequelize.fn('COUNT', sequelize.col('staffStaffId')), 'staffCount']] },
             },
             {
                 model: tests,
                 attributes:['testsTaken','testsCompleted','testName'],
             }
        ],
        limit:parseInt(limit),offset:parseInt(offset),
        });

Thank you for your time.

Upvotes: 1

Views: 3021

Answers (2)

Rohit Dalal
Rohit Dalal

Reputation: 866

Try following Attributes in modules findAll - 

    attributes: [
        "moduleName",
        [Sequelize.literal("(SELECT count(DISTICT testId) FROM tests T WHERE (CONDITION) )"), "testIdCount"]
    ]

Only showing for test count can add the attribute for staffModules.

Upvotes: 0

polux
polux

Reputation: 98

Maybe use possibility of raw queries for situations like this? ( http://docs.sequelizejs.com/manual/tutorial/raw-queries.html ) [ I would comment it, just don't have enough reputation ]

Upvotes: 0

Related Questions