sequelizejs中where条件与order排序的使用

最基本的where条件:

Post.findAll({
    where: {
        authorId: 2
    }
});
// SELECT \* FROM post WHERE authorId = 2

Post.findAll({
    where: {
        authorId: 12,
        status: active
    }
});

// SELECT \* FROM post WHERE authorId = 12 AND status = 'active';

Post.destroy({
    where: {
        status: 'inactive'
    }
});
// DELETE FROM post WHERE status = 'inactive';

Post.update({
    updatedAt: null,
},
{
    where: {
        deletedAt: {
            $ne: null
        }
    }
});

// UPDATE post SET updatedAt = null WHERE deletedAt NOT NULL;

如果涉及到or的使用,sequelize也提供了很好的解决方案

where: {
    rank: {
        $or: {
            $lt: 100,
            $eq: null
        }
    }
}

// rank < 1000 OR rank IS NULL
where: {
    createdAt: {
        $lt: new Date(),
        $gt: new Date(new Date() - 24 \* 60 \* 60 \* 1000)
    }
}

// createdAt < [timestamp] AND createdAt > [timestamp]
where: {
    $or: [
    {
        title: {
            $like: 'Boat%'
        }
    },
    {
        description: {
            $like: '%boat%'
        }
    }
    ]
}

// title LIKE 'Boat%' OR description LIKE '%boat%'

相同字段不同条件用

rank: {
    $or: {
        $lt: 100,
        $eq: null
    }
}

// rank < 1000 OR rank IS NULL

多个字段的or操作

$or: [
{
    title: {
        $like: 'Boat%'
    }
},
{
    description: {
        $like: '%boat%'
    }
}
]

//title LIKE 'Boat%' OR description LIKE '%boat%'

以下是sequelize的排序使用方法:

something.findOne({
    order: [
    ['username', 'DESC'],
    // Will order by max(age)
    sequelize.fn('max', sequelize.col('age')),

    // Will order by max(age) DESC
    [sequelize.fn('max', sequelize.col('age')), 'DESC'],

    // Will order by otherfunction(\`col1\`, 12, 'lalala') DESC
    [sequelize.fn('otherfunction', sequelize.col('col1'), 12, 'lalala'), 'DESC'],

    // Both the following statements will be treated literally so should be treated with care
    'name',
    'username DESC'
    ]
})

其中有一段需要特别说明一下:

// Will order by otherfunction(\`col1\`, 12, 'lalala') DESC

[sequelize.fn('otherfunction', sequelize.col('col1'), 12, 'lalala'), 'DESC'],

这个otherfunction代表了其他更多函数,比如我使用的一段代码:

where: {
    groupId: params.groupId,
    $or: [{
        state: params.state,
        releaseDate: {
            $eq: null
        }
    },
    {
        releaseDate: {
            $lt: new Date()
        }
    }
    ]
},

order: [[sequelize.fn('ifnull', sequelize.col('releaseDate'), sequelize.col('createDate')), 'DESC']]

//ORDER BY ifnull(\`releaseDate\`,\`createDate\`) DESC;

需要排序的字段也要使用`sequelize.col(‘releaseDate’)`处理,不过也不是必须的,这样是为了能使代码统一,否则就是这样:`ORDER BY ifnull(`releaseDate`, ‘creat eDate’) DESC;` `createDate`使用单引号包括的`releaseDate`使用反单引号包括的