How to write mysql join queries using sequelize in node.js?

Question

Let's say I have a MySQL table like this:

How can I write the following MySQL query using sequelize?

select mt.JobId 
from   MyTable as mt join MyTable as mt2 on mt2.JobId = mt.JobId
where  mt.TaskSeq = 0 and mt.TaskState = 'Done' and mt2.TaskSeq = 1 
and mt2.TaskState = 'New'

This query returns all the JobIds whose taskA is in Done state and taskB is in New state.

In this case, it returns qwert, and zxcv.

After reading a similar stackoverflow question here. I get a little confused, because I only have 1 table. Should I use hasMany and belongsTo in order to create a join query in sequelize?

The following is part of my node.js scripts for creating this table

var MyTable = conn.define(
'MyTable',
{
    JobId: {
    type: Sequelize.STRING,
    unique: true
    },
    TaskName: {
    type: Sequelize.STRING
    },
    TaskSeq: {
    type: Sequelize.INTEGER
    },
    TaskState: {
    type: Sequelize.STRING
    }
},
{
    collate: 'utf8mb4_bin'
}
)

MyTable.sync()
module.exports = {
    MyTable, conn
}

And this is my sequelize query, which fails to return the JobIds I need.

MyTable.belongsTo(MyTable, {foreignKey: 'id'})
MyTable.findAll({
attributes: ['JobId'],
where: {
    TaskSeq: 0,
    TaskState: 'DONE'
},
include: [{
    model: MyTable,
    attributes: ['JobId'],
    where: {
        JobId: sequelize.col('MyTable.JobId'),
        TaskSeq: 1,
        TaskState: 'NEW'
    }
}]
}).then(result => {
    console.log(result)
})

Aaron Stanley King · Answer 1 · 2017-09-11T17:35:30.847

0

Yes you need to define the associations so that the include function in your query works. http://docs.sequelizejs.com/manual/tutorial/associations.html

Your MyTable.belongsTo(MyTable, {foreignKey: 'id'}) isn't using the correct id is it? If you are wanting to join on JobId then use it. I didn't even see and id column in your table above.

or try a raw query:

`sequelize.query('select mt.JobId from MyTable as mt join MyTable as mt2 on 
   mt2.JobId = mt.JobId where mt.TaskSeq = 0 and mt.TaskState = 'Done' and 
   mt2.TaskSeq = 1', { type: sequelize.QueryTypes.SELECT }
 ).then(myTables => {
   console.log(myTables)
 })`

edited Sep 11 '17 at 17:35

answered Sep 05 '17 at 19:37

Aaron Stanley King

79
1
5

The `id` column is automatically generated by sequelize. – Brian Sep 07 '17 at 10:01
I've ran the same code locally and I don't think Sequelize supports table associations back to the same table. I do know about the id pk column but what I mean was trying to change: `MyTable.belongsTo(MyTable, {foreignKey: 'id'})` to: `MyTable.belongsTo(MyTable, {foreignKey: 'JobId'})` But with the version I installed, that didn't work either. I would just use a raw query and get the exact result you want. http://docs.sequelizejs.com/manual/tutorial/raw-queries.html – Aaron Stanley King Sep 11 '17 at 17:28

Eric Ocampo · Answer 2 · 2018-04-04T02:16:57.170

If your model has an association with other table you can do something like this:

var Product = sequelize.define('Product',{
        name: {
            type: DataTypes.STRING,
            field: 'description'
        }
    },{
        classMethods: {
            associate : function(models) {
                Product.belongsTo(Category,{
                    as: 'category',
                    foreignKey: {
                        allowNull: false
                    },
                    onDelete: 'CASCADE'
                });
            }
        }
    });


Product.findAll({
  include: [{
   model: Category,
   as: "category"     
  }]
})

The result will be some like:

{
 id:1,
 name:'product1',
 category: {
  id:23,
  name: 'category1'
 }
}

How to write mysql join queries using sequelize in node.js?

2 Answers2