//用户表
const sequelize = require('sequelize');
const db = require('../db');
// 定义一个 user model
const {
STRING
} = sequelize;
//0是普通会员 1是管理员
const User = db.define('user', {
user_name: {
type: STRING(32),
unique: true
},
password: {
type: STRING(32),
defaultValue: "123456"
},
auth: {
type: STRING(32),
defaultValue: "0"
}
});
//用户房间关联表
const RoomInof = db.define('RoomInof', {
room_name: {
type: STRING(32),
unique: true,
},
room_desc: {
type: TEXT,
defaultValue: "未填写"
},
room_price: {
type: STRING(255),
defaultValue: "0"
},
room_status: {
type: STRING(255),
defaultValue: "0" //0就是没有人预定 1就是有人预定
},
room_imgs: {
type: TEXT,
defaultValue: ""
}
});
router.get('/', function (req, res, next) {
//建立表关联关系 当前表(User)的字段: user_name 关联表(userRoom)的字段user_id
User.belongsTo(userRoom, { foreignKey: 'user_name', targetKey: 'user_id' });
User.findAll({
include: [{
model: userRoom,
'where': {
'user_id': 'admin'
},
through: {
attributes: ['createdAt', 'startedAt', 'finishedAt'], //过滤属性
where: {completed: true}
}
required: false //默认为true 当userRoom表没有数据 回调将会返回为空 fasle 的时候返回用户信息 userRoom表有信息则添加一个字段 没有则之返回用户信息
}]
}).then((data) => {
console.log(data, 'xcq888888888888');
if (data !== null) {
res.send({
code: '200',
data: {
code: '200',
data
}
})
} else {
console.log("no frends");
res.send({
code: '200',
data: {
code: '200',
data: '87878'
}
})
}
// console.log(JSON.stringify(data));
}).catch(function (err) {
console.log('err' + err);
});
});
//同一字段多条件在一个表中查询,在RoomInof表中查找id为 7 和8的数据
RoomInof.findAll({ where: { id: [7, 8] } }).then(r => {
console.log(r, 'xcq')
res.send({
code: '200',
data: {
code: '200',
data: r
}
})
})
//模糊查询 在RoomInof表中查找 room_name中含有1的数据
RoomInof.findAll({
where: {
room_name: {
[Sequelize.Op.like]: '%1%'
}
}
}).then(r => {
res.send({
code: '200',
data: {
code: '200',
data: r
}
})
})