Sequelize: findAll() include same models 2 times with different condition

Question

I want to select one field from the join table and set to 2 output fields with different condition.

Table Employee:

---------------
empId | name 
---------------
001   | James
002   | Alex
003   | Lisa
---------------

Table EmpDate:

------------------------------
empId | dateType | date
------------------------------
001   | REG      | 2018-01-01
001   | TMN      | 2018-12-31
002   | TMN      | 2018-02-01
003   | REG      | 2018-01-01
------------------------------

Desired Output:

----------------------------------------
empId | name  | regisDate  | TermDate
----------------------------------------
001   | James | 2018-01-01 | 2018-12-31
002   | Alex  |            | 2018-02-01
003   | Lisa  | 2018-01-01 |
----------------------------------------

Here's my SQL script for the case (work correctly on MySQL workbench).

SELECT emp.empId
       , emp.name
       , reg.date AS regisDate
       , tmn.date AS termDate
FROM Employee AS emp
LEFT JOIN EmpDate AS reg
     ON emp.empId = reg.empId
LEFT JOIN EmpDate AS tmn
     ON emp.empId = tmn.empId
WHERE reg.dateType = 'REG'
      AND tmn.dateType = 'TMN'

Here's my current Sequelize code (still cannot select the desired data since it resulted in 3 output fields).

exports.getEmployeeData = () => {
    const emp = db.Employee
    const empDate = db.EmpDate

    return emp.findAll({
        raw: true,
        attributes: [ 'empId', 'name', 'EmpDates.date' ],
        include: [{
            required: false,
            model: empDate,
            attributes: [],
            where: { dateType: ['REG', 'TMN'] }
        }]
    })
}

I tried to use model alias like this but it didn't work.

exports.getEmployeeData() = () => {
    const emp = db.Employee
    const empDate = db.EmpDate

    return emp.findAll({
        raw: true,
        attributes: [
            'empId',
            'name',
            'reg.date'
            'tmn.date'
        ],
        include: [{
            required: false,
            model: empDate,
            attributes: [],
            as: 'reg',
            where: { dateType: 'REG' }
        }, {
            required: false,
            model: empDate,
            attributes: [],
            as: 'tmn',
            where: { dateType: 'TMN' }
        }]
    })
}

Can anyone guide me how to work on this case with sequelize findAll() or would it be better if I change to sequelize query()? Thanks in advance.

Answer 1

You can not define attributes as reg.date or tmn.date. Instead from the result object, you can construct a new object with custom attributes. For Example,

emp.findAll({
    raw: true,
    attributes: [
        'empId',
        'name'
    ],
    include: [{
        model: empDate,
        as: 'reg',
        where: { dateType: 'REG' }
    }, {
        model: empDate,
        as: 'tmn',
        where: { dateType: 'TMN' }
    }]
})

From the above result, you will get result as,

[{
    empId: 001,
    name: 'James',
    emp: {
        date: '2018-01-01'
    },
    tmn: {
        date: '2018-01-01'
    }
}, {
    empId: 002,
    name: 'Alex',
    emp: {
        date: '2018-01-01'
    },
    tmn: {
        date: '2018-01-01'
    }
}]

From this object, you can construct your result as,

result.regisDate = result.emp.date;
result.termDate = result.tmn.date;