Sequelize Native Query - sequelize.query() 사용법

통계 등 복잡한 쿼리는 시퀄라이즈를 사용하면 더 복잡해져서 native 쿼리를 사용하였다.

 

Model.sequelize.query(

    query,

    { type: QueryTypes.SELECT }

); 

 

아래 예제는 연도별 사용자 접속 통계 쿼리이다.

Postgresql을 사용중이라면 generate_series를 사용해 날짜/시간을 간단하게 생성할 수 있다.

const sequelize = require("sequelize");
const { QueryTypes, Op } = require("sequelize");
const { LogMng } = require('../database/models');


function findAllCompany(params) {
    let search = {};

    if (params?.startYmd || params?.endYmd) {
        search.createdAt = { [Op.between]: [params.startYmd, params.endYmd] };
    }

    const startYmd = params.startYmd.substr(0, 4);
    const endYmd = params.endYmd.substr(0, 4);
    const query = `
        select	g.cpname as id,
                g.ymd :: varchar as x,
                count(l.cpname) as y
        from (
                select	ymd, cpname
                from ( select	* from	generate_series(${startYmd}, ${endYmd}) as ymd) as g
                join ( select distinct cpname from	log_mng) as l
                on	1 = 1	
        ) as g
        left join log_mng as l
        on	g.cpname = l.cpname and g.ymd :: varchar = to_char(l.created_at, 'YYYY')
        group by ymd, g.cpname
        order by id, x
    `;

    return LogMng.sequelize.query(query, { type: QueryTypes.SELECT }).then(result => {
        return LogMng.findAll({
            where: search,
            attributes: [sequelize.fn('distinct', sequelize.col('cpname')), 'cpname'],
            raw: true
        }).then(cpList => {
            return cpList.map(cp => {
                const cpname = cp.cpname;
                const ymdList = result.filter(t => t.id === cpname);
                if (ymdList) {
                    let params = {
                        id: cpname,
                        data: ymdList.map(xy => {
                            return {
                                x: xy.x,
                                y: parseInt(xy.y)
                            }
                        })
                    };
                    return params;
                }
            });
        });
    });
}