Sequelize Basic usage

[lqshow]

字面量查询

自增自减

yield models.VoucherBatch.update(
    {
        voucherAmount: sequelize.literal('voucher_amount - 1')
    },
    {
        where: {
            id: voucher.voucherBatchId
        }, 
        transaction: tx
    }
);

case 使用

let children = models.StatsFusionEvent.findAll({
    attributes: [['event_type', 'eventType'],
        [sequelize.fn('sum', sequelize.col('count')), 'count'],
        sequelize.literal('sum(case when status = \'FAIL\' then `count` else 0 end) as \'failCount\''),
        sequelize.literal('sum(case when status = \'SUCCESS\' then `count` else 0 end) as \'mergeCount\'')
    ],
    raw: true,
    group: ['eventType']
});

条件嵌套

let whereCond = {};
let tempSQL = 'select id from `table` where  value!=0';
_.extend(whereCond, {
    id: {
        $notIn: Sequelize.literal('(' + tempSQL + ')')
    }
});

having

let patientCase = yield models.PatientCase.findAll({
    transaction: tx,
    attributes: ['id', [sequelize.fn('count', sequelize.col('patientReports.id')), 'reportNum']],
    include: [
        {
            model: models.PatientReport,
            attributes: [],
            required: false
        }
    ],
    group: ['id'],
    having: {
        'reportNum': {
            $eq: 0
        }
    }
});

Where clause for left column in LEFT OUTER JOIN

var cases = yield models.PatientCase.findOne({
    attributes: ['status', 'source'],
    include: [{
        model: models.Reservation,
        attributes: ['id']
    }],
    where: {
        id: 1,
        '$reservation.id$': 2
    }
});

references:

• Where clause for left column in LEFT OUTER JOIN
• Where condition for joined table in Sequelize ORM

  through相关

不查找关联表字段

let partitions = yield models.XtablePart.findAll({
    attributes: ['name'],
    include: [
        {
            model: models.XtableColumn,
            as: 'columns',
            attributes: [['logical_name', 'name']],
            through: {
                attributes: []
            }
        }
    ],
    where: {
        xtableUuid: xtableUuid
    },
    transaction: tx
});

关联表过滤

let team = yield models.Team.findOne({
    attributes: ['id'],
    include: [
        {
            model: models.Dataset,
            as: 'datasets',
            attributes: ['id'],
            through: {
                attributes: ['permissions'],
                where: {
                    target: 'TEAM'
                }
            },
            required: false
        },
        {
            model: models.Individual,
            as: 'members',
            attributes: ['id'],
            include: [
                {
                    model: models.User,
                    as: 'subject',
                    attributes: ['name'],
                    on: {
                        uuid: {
                            $col: 'members.id',
                        },
                        disable: false
                    }
                }
            ],
            required: false
        }
    ],
    where: {
        id: libs.utils.hexizeUuid(teamId)
    },
    transaction: tx
});

一对多关联 (hasMany)

一对多关联将一个来源与多个目标连接起来。 而多个目标接到同一个特定的源。

比如series表的外键case_uuid关联case表的uuid，而非主键。即在不同的列上关联记录，您可以使用 sourceKey 选项。

Case.hasMany(Series, {foreignKey: 'case_uuid', sourceKey: 'uuid'});

Series.belongsTo(Case, {foreignKey: 'case_uuid', targetKey: 'uuid'});

Sequelize with NodeJS can't join tables with limit

https://stackoverflow.com/questions/26021965/sequelize-with-nodejs-cant-join-tables-with-limit

sub-query in where conditions

• sub-query in where conditions
• sequelize subquery as field
• Sequelize Association Subquery Examples

findAndCountAll distinct with include but without include.where condition

https://github.com/sequelize/sequelize/issues/2713