CODEWITHSUNDEEP
javascriptmysqlleft-joinsequelize.js
big.data
big.data

Reputation: 53

Sequelize MySQL select from the same table (left join)

How I can select from the same table twice with Sequelize? Here is MySQL code:

select b.*, a.parent_id
from theSameTable as b left join theSameTable as a on b.parent_id = a.id

Here is my MySQl table

enter image description here

Here is my Sequelize code

const db = await ec.sequelize.define(tableDb, {
    id: {
        type: ec.Sequelize.INTEGER.UNSIGNED,
        primaryKey: true,
        autoIncrement: true
    },
    url: ec.Sequelize.STRING(511),
    url_hash: ec.Sequelize.STRING(32),
    name: ec.Sequelize.STRING(511),
    full_name: ec.Sequelize.STRING(511),
    parent_id: ec.Sequelize.INTEGER(11).UNSIGNED,
    cnt: ec.Sequelize.STRING(255),
    chk: ec.Sequelize.INTEGER(1)
}, {
    indexes: [{
        unique: true,
        fields: ['url_hash']
    }]
});

await ec.sequelize.sync();

Upvotes: 1

Views: 908

Answers (1)

big.data
big.data

Reputation: 53

Finally I've found an answer. Maybe to someone it will be useful. After 

await ec.sequelize.sync();

Write 

db.belongsTo(db, {
    as: 'db2',
    foreignKey: 'parent_id',
    required: false
});

let rows = await db.findAll({
    where: {
        chk: 0
    },
    include: [{
        model: db,
        as: 'db2',
        attributes: ['id', 'full_name']
    }],
    raw: true,
    limit: 20
}).catch(function (err) {
    console.log(err);
});

Upvotes: 1

Related Questions