[大数据]sequelize关联查询（连表条件查询）

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)