Sequelize Chinese document Sequelize English document Egg Chinese document
The sequelize framework and the egg.js framework are both sequelize and egg.js.
1 Modify the returned data
toJSON()
const { list, total } = await this.service.activityManage.xxxx.xxxx()
const transferList = list.map(node= > {
const item = node.toJSON()
const{ powerRecordObj, ... rest } = itemreturn{... powerRecordObj, ... rest } })Copy the codeThe data found in find is a Model object that can be converted toJSON using toJSON()
2 model query basis
Model Basics – Model Basics
2.1 the Op. Between
Filter out data between startTime and endTime. If createdAt equals startTime/endTime, filter out data
const { Op } = this.app.Sequelize
where: {
createdAt: {
[Op.between]: [ startTime, endTime ],
},
}
Copy the code2.2 Fuzzy search op. like
where: {
sqlText: { [Op.like]: ` %${sqlText}% `}},Copy the code2.3 the Op. In
Query the data containing these ids
where: {
id: { [Op.in]: ids },
},
Copy the code2.4 and or
Return the bannerId as the id passed in, and at least one non-0 value of uv,click, and comment. In this case, use and or
where: {
[Op.and]: [
{ bannerId: id },
],
[Op.or]: [
{ uv: { [Op.ne]: 0}}, {click: { [Op.ne]: 0}}, {comment: { [Op.ne]: 0}},],},Copy the code2.5 Used with fn
The relatedCPGame. game_id is not null
where : {
XXXX,
[Op.and]: [{
[Op.not]: fn('ISNULL', col('relatedCpGame.game_id')),}],}Copy the code2.6 Not and
Return startHomeTimes startHomeUser, beginUserNum insufficiency of 0 data
{
[Op.not]: {
[Op.and]: [
{ startHomeTimes: { [Op.eq]: 0}}, {startHomeUser: { [Op.eq]: 0}}, {beginUserNum: { [Op.eq]: 0}},],},}Copy the code3 Literal usage
Literal sequelize.literal() method is used to create a literal object (val) that is passed directly into the generated SQL statement without any escape
const { Op, literal } = app.Sequelize
const [ summaryData ] = await ctx.xxxx.xxxxx.findAll({
attributes: [
[ literal('COUNT(id)'), 'count' ],
[ literal('IFNULL(SUM(pv), 0)'), 'pv']],raw: true,})Copy the code whereObj = {
startTime: {
[Op.gte]: literal(`FROM_UNIXTIME(${start})`),
},
endTime: {
[Op.lte]: literal(`FROM_UNIXTIME(${end})`),
},
}
Copy the codeFour associated
Associations of relevance
1.BelongsTo
2. HasOne
3. HasMany
4. BelongsToMany
Assume that user is a user table with user IDS and the field name is ID. UserInfo is a user information table. The field name of user IDS is userId and the phone field required by table A is in the table UserGameRecord is a purchase record table. The user ID field name is userId. A user has multiple purchase records and the user table needs to obtain the total number of all purchase records
4.1 User Primary Table Associate the userInfo table to obtain user information
user.associate = function() {
this.hasOne(app.model.userInfo, { foreignKey: 'userId'.sourceKey: 'id'.as: 'userInfo'})}Copy the code userInfo.associate = function() {
this.belongsTo(app.model.user, { foreignKey: 'userId'.targetKey: 'id'.as: 'jointUser'})}Copy the code include: [
{
model: this.app.model.userInfo,
as: 'userInfo'],},Copy the code4.2 The user master table is associated with the userGameRecord table to obtain user game records
user.associate = function() {
this.hasMany(app.model.userGameRecord, { foreignKey: 'userId'.sourceKey: 'id'.as: 'gameInfo'})}Copy the code userGameRecord.associate = function() {
this.belongsTo(app.model.user, { foreignKey: 'userId'.targetKey: 'id'.as: 'gameInfo'})}Copy the code include: [
{
model: this.app.model.userGameRecord,
as: 'gameInfo'],},Copy the code5 How do I Connect two Databases and write SQL
Egg-sequelize Original query
In the config/config. The default writing
// Various environment configurations
const dataBaseLogMap = {
local: {
database: 'xxxxx',}}module.exports = () = > {
return {
// Database configuration
sequelize: {
datasources: [{
delegate: 'model'.baseDir: '.. /.. /xxxx/model'. dataBaseMap[nowMode], }, {delegate: 'testLog'.baseDir: '.. /.. /xxxx/test_log'. dataBaseLogMap[nowMode], }], }, } }Copy the codeWrite SQL
const [{ total }] = await this.ctx.model.query(
`select xxxxxxxxxxxxxxxxx`,
{ type: 'SELECT', replacements }
)
Copy the code6 curl
curl
Sometimes the server may provide us with an interface, so to maintain consistency, we need to make a transition on the Node side
ctx = new Context()
const { ctx } = this
const result = await ctx.curl('http://example.com/foo.json',
{ method: 'GET'.dataType: 'json'});console.log(result.status, result.headers, result.data);
Copy the code7 How do I call a common method
Application
If there is a method
'use strict'
module.exports = {
/** * generates a random name *@param { number } RandomNum Number of random codes *@return { string } Random name */
getRandomName(randomNum = 4) {
xxxxxx
},
}
Copy the codeIt can be called in both service and Controller
const { app } = this
const res = app.utils.index.getRandomName()
Copy the code8 How do I verify parameters
Plugin entry egg-validate
1. Run the config/plugin.js command to configure the plugin
validate: {
enable: true.package: 'egg-validate',},Copy the code2. Use it in controller
ctx.validate({
min_reward_wealth: { required: true.type: 'string' },
max_reward_wealth: { required: true.type: 'number'}})Copy the code9 A function that returns data successfully
ctx.body, ctx.status
It mainly returns status and data
success({ status = 200, code = 0, msg = 'success', data } = {}) {
this.ctx.body = {
code,
msg,
data,
}
this.ctx.status = status
}
Copy the codeconst list = await service.activityManage.xxxx.xxxx()
this.success({ data: { list } })
Copy the code10 How do I set request information
ctx.set
ctx.set('Content-type'.'application/octet-stream')
Copy the code11 the sorting
order
This is to sort the primary table. If there are child tables, add table names.
const { rows, count } = await this.ctx.model.xxxx.findAndCountAll({
order: [[ 'id'.'DESC']],})Copy the code11.2 Sorting primary tables and sub-tables
async xxxxx() {
const { rows, count } = await this.ctx.model.xxxxx.findAndCountAll({
include: [{model: this.app.model.xxxxx,
as: 'rewardVipGames'],},order: [[ 'startDate'.'ASC' ], [ 'rewardVipGames'.'sort'.'ASC']],})return { list: rows, total: count }
}
Copy the code12 finder
model apis
A Model is equivalent to a table in a database, sometimes referred to as a "Model" or a "factory." Models cannot be created by constructors, but can only be defined by the sequlize. Define method or imported by sequlize. Import. By defining a Model, it is equivalent to defining a mapping relationship between the Model and the data table. Through the Model, operations such as adding, deleting, modifying and searching table records can be realizedCopy the codeFinders – Model queries (Finders)
FindAll does not need pagination. FindOne finds the first findAndCountAll that needs pagination
12.1 bulkCreate Batch Insert
const gameArr = gameData.map((item, sort) = > {
return {
gameId: item.gameId,
tagId: id,
sort, / / sorting}})return await this.ctx.model.XXXXX.bulkCreate(gameArr, { transaction }) // Batch insert
Copy the code12.2 destory
return this.ctx.model.xxxxx.destroy({
where: {
id,
},
})
Copy the code13 separate: true
Separate: true stackoverflow.com/questions/3…
const { rows: list, count: total } = await ctx.model.test1.findAndCountAll({
attributes: { exclude: [ 'createdAt'.'updatedAt']},include: [{model: app.model.test2,
attributes: [ 'id'].as: 'missionSaving'.separate: true],},order: [[ 'enable'.'DESC' ], [ 'createdAt'.'DESC' ]],
limit,
offset,
})
Copy the codeSeparate: true; main table test1 is one-to-many with test2; separate: true; createdAt; separate: true
How to write an interface, and a front-end call, can see my article: Using egg.js to implement routing and front-end interface calls
Recently in writing a public number [program yuan love to talk], mainly to share the front end and some small things in life, interested in ha, no code, only the story ~
Have a good life