sqlsequelize.jsnested-select

SQL to Sequelize with nested select


Need to write the following SQL Query in Sequelize Node js

select a, b, c
from (
    select tbl.*, 
       count(*) over(partition by a) cnt,
       row_number() over (partition by a order by b) brn,
       row_number() over (partition by a order by c) crn
    from tbl
    where c in (4, 5)
) t
where cnt = 2 and brn = crn;

This is what I've come up with. I cannot figure out where to put the condition where cnt = 2 and brn = crn;

t.findAll({
    attributes: {
        include: [
            [sequelize.literal('count(*) over(partition by a)'),'cnt'],
            [sequelize.literal('row_number() over (partition by a order by b)'),'brn'],
            [sequelize.literal('row_number() over (partition by a order by c)'),'crn'],
        ]   
    },
    where: {
            c: {[Op.in]:[4,5]},
        }
    })

Solution

  • Sequelize doesn't seem to support selecting from subqueries (https://github.com/sequelize/sequelize/issues/5354)

    You can use a raw query instead (https://sequelize.org/master/manual/raw-queries.html):

    const { QueryTypes } = require('sequelize');
    let result = await sequelize.query(`
        select a, b, c
        from (
            select tbl.*, 
               count(*) over(partition by a) cnt,
               row_number() over (partition by a order by b) brn,
               row_number() over (partition by a order by c) crn
            from tbl
            where c in (4, 5)
        ) t
        where cnt = 2 and brn = crn;
    `, {type: QueryTypes.SELECT});