sequelize关联查询
//关键语句
sequelize:Sequelize.where(Sequelize.col(`table.name`), { [Op.like]: `%${value}%` })
mysql:where table.name LIKE %value%
sequelize:Sequelize.where(Sequelize.col(`table.name`),value)
mysql:where table.name=value
mysql:
SELECT FileSharingCopy
.id
, FileSharingCopy
.fileId
, FileSharingCopy
.dir
, FileSharingCopy
.userId
, FileSharingCopy
.tags
, FileSharingCopy
.createdAt
, FileSharingCopy
.updatedAt
, FileSharingCopy
.deletedAt
, fileInfo
.id
AS fileInfo.id
, fileInfo
.fileName
AS fileInfo.fileName
, fileInfo
.path
AS fileInfo.path
, fileInfo
.size
AS fileInfo.size
, fileInfo
.type
AS fileInfo.type
, fileInfo
.ossUrl
AS fileInfo.ossUrl
, fileInfo
.ossFileName
AS fileInfo.ossFileName
, fileInfo
.dir
AS fileInfo.dir
, fileInfo
.userId
AS fileInfo.userId
, fileInfo
.private
AS fileInfo.private
, fileInfo
.createdAt
AS fileInfo.createdAt
, fileInfo
.updatedAt
AS fileInfo.updatedAt
, fileInfo
.deletedAt
AS fileInfo.deletedAt
FROM FileSharingCopy
AS FileSharingCopy
INNER JOIN FileDataInfo
AS fileInfo
ON FileSharingCopy
.fileId
= fileInfo
.id
AND (fileInfo
.deletedAt
IS NULL AND fileInfo
.private
= false) WHERE (FileSharingCopy
.deletedAt
IS NULL AND ((JSON_CONTAINS(tags
, ‘{“tag”:“图表”}’) OR fileInfo
.fileName
LIKE ‘%图表%’) AND FileSharingCopy
.dir
= ‘/’));
sequelize:
const { dir = '/public', queryConditions = null } = ctx.request.query
const Opthion = {
include: [
{
model: sequelize.model('FileDataInfo'),
as: 'fileInfo',
where: {
private: false,
}
}
],
where: {
dir: dir,
[Op.or]: [
Sequelize.fn(
'JSON_CONTAINS',
Sequelize.col('tags'),
JSON.stringify({
tag: queryConditions,
})
),
Sequelize.where(Sequelize.col(`fileInfo.fileName`), { [Op.like]: `%${queryConditions}%` })
]
}
}
const fileSharingCopyList = await fileSharingCopyService.findAll(Opthion)