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

  • 分类:【Nodejs
  • 浏览【1369】
  • 评论【0】
  • 更新【2015-8-28 11:21:08】

最基本的where条件: ```js 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也提供了很好的解决方案 ```js 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%' ``` 相同字段不同条件用 ```js rank: { $or: { $lt: 100, $eq: null } } // rank < 1000 OR rank IS NULL ``` 多个字段的or操作 ```js $or: [ { title: { $like: 'Boat%' } }, { description: { $like: '%boat%' } } ] //title LIKE 'Boat%' OR description LIKE '%boat%' ``` 以下是sequelize的排序使用方法: ```js 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' ] }) ``` 其中有一段需要特别说明一下: ```js // Will order by otherfunction(`col1`, 12, 'lalala') DESC [sequelize.fn('otherfunction', sequelize.col('col1'), 12, 'lalala'), 'DESC'], ``` 这个otherfunction代表了其他更多函数,比如我使用的一段代码: ```js 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`使用反单引号包括的

关键字词:sequelize