在实际开发过程中,会遇到各种各样的语法问题,我个人偏向于 从 场景
+ 语法
实现,让你更快的上手typeorm中的 QueryBuilder
内容会持续更新.... 如果你有遇到什么问题,可以持续在下方留言
包版本说明
截止2023-05-23 包都是最新版本
node v18.15.0 (14+ 应该都没问题)
"mysql2": "^3.3.1"
"typeorm": "^0.3.16"
"express": "^4.18.2"
mysql 数据库版本:8.0.32
官网创建 Query Builder
的方式有5种, 选择自己合适的就行; 我个人偏向于使用 基于 repository
的 创建query builder
实战案例
以下都是js版本的实现,TypeScript版本的差不多
表达 is not null
场景: 我想查询一个表里面,某个字段不为null的数据
SELECT * FROM repository as r where r.service_name IS NOT NULL
使用 typeorm 实现
/entity/repository.js
实体
import { EntitySchema } from 'typeorm'
export default new EntitySchema({
name: 'Repository',
columns: {
id: {
primary: true,
type: 'int',
generated: true,
comment: '仓库id'
},
name: {
type: 'varchar',
length: 100
},
description: {
type: 'varchar',
length: 255,
comment: '项目描述',
// 可为空
nullable: true
},
web_url: {
type: 'varchar',
comment: 'web地址',
nullable: true,
// 唯一
unique: true
},
ssh_url_to_repo: {
type: 'varchar',
comment: 'SSH地址',
nullable: true,
// 唯一
unique: true
},
readme_url: {
type: 'varchar',
comment: 'README路径',
nullable: true
},
last_activity_at: {
type: 'datetime',
comment: '最后更新时间'
},
created_at: {
type: 'datetime',
comment: '创建时间'
},
default_branch: {
type: 'varchar',
length: 50,
comment: '默认分支',
// 新仓库可能是没有分支的
nullable: true
},
group_name: {
type: 'varchar',
comment: '项目组名称',
nullable: true
},
group_web_url: {
type: 'varchar',
comment: '项目组地址',
nullable: true
},
group_id: {
type: 'int',
comment: '项目组id',
nullable: true
},
service_name: {
type: 'varchar',
nullable: true,
comment: 'jenkins服务名',
length: 60
}
}
})
service.js
import Repository from '../entity/repository.js'
import { Not, IsNull } from 'typeorm'
const repositoryEntity = dataSource.getRepository(Repository)
const fn = async() => {
await repositoryEntity
.createQueryBuilder('repo')
.select([
'repo.id',
'repo.ssh_url_to_repo',
'repo.name',
'repo.service_name',
'repo.description'
])
.where({
service_name: Not(IsNull())
})
.getMany()
}
fn()
注意: 这里使用了
select
一定要使用上面的别名repo
, 否则getMany()
查询到的数据是 空数组
以下为错误写法
await repositoryEntity
.createQueryBuilder('repo')
.select([
'repo.id',
'repo.ssh_url_to_repo',
'repo.name',
'repo.service_name',
'repo.description'
])
// 想表达 is not null 场景,这里的语法不能这么写,会报语法异常
// Cannot convert a Symbol value to a string
.where('repo.service_name = :service_name', {
service_name: Not(IsNull())
})
.getMany()
上述不能通过 参数来转义数据
的方式来实现 where 条件查询, 按理说,应该可以的, 应该是 typeorm 没有实现, 毕竟使用参数转义,是 typeorm 推荐的写法,防止sql 注入
表达 is null
场景: 查询表 (repository)里面, 分类(repository_category) 字段的值为空的数据
sql 写法
select * from repository r WHERE r.repository_category IS NULL
js代码
const findUniAppRepoList = await repositoryEntity.createQueryBuilder('repo')
.select(['repo.id'])
.where({
repository_category: IsNull()
})
.getMany()
IN , Between, Like
场景: 前端传来了一个分类,数组类型, 需要查询 查询分类在某个范围的数据
sql写法
select * from repository where repository_category IN (1,2)
js代码
import { Like, Between, In } from 'typeorm'
const repositoryEntity = dataSource.getRepository(Repository)
export const getProjects = async (req, res) => {
let { currentPage, pageSize, endUpdateTime, startUpdateTime, ...rest } = req.body
currentPage = currentPage || 1
pageSize = pageSize || 20
let query = {}
if (rest.id) {
query.id = rest.id
}
// 使用模糊匹配
if (rest.name) {
query.name = Like(`%${rest.name}%`)
}
if (rest.description) {
query.description = Like(`%${rest.description}%`)
}
if (startUpdateTime && endUpdateTime) {
query['last_activity_at'] = Between(startUpdateTime, endUpdateTime)
}
// in的场景
if (rest.repository_category && rest.repository_category.length) {
query.repository_category = In(rest.repository_category)
}
const result = await repositoryEntity.findAndCount({
skip: (currentPage - 1) * pageSize,
take: pageSize,
where: query,
order: {
last_activity_at: 'DESC'
}
})
const list = result[0] || []
res.json({
code: 1000,
data: {
currentPage: currentPage,
pageSize: pageSize,
list,
total: result[1]
},
message: '响应成功'
})
})
上述代码已经实现了,基础的 分页
+ 多条件组合查询
In createQueryBuilder方式调用 案例
2023-11-1 记录
入参
rest.repository_category = [1,2]
使用 In函数包装后
In(rest.repository_category)
// 打印的结果如下
FindOperator {
'@instanceof': Symbol(FindOperator),
_type: 'in',
_value: [ 1, 2 ],
_useParameter: true,
_multipleParameters: true,
_getSql: undefined,
_objectLiteralParameters: undefined
}
上面使用 In操作符是没有问题的, 如果我们使用 createQueryBuilder
的方式使用 In
, 出现报错,请排查语句是否存在语法问题
Cannot convert a Symbol value to a string
at RegExp.exec (<anonymous>)
at escapeString (D:\owner\node-base-example\jwt\node_modules\.pnpm\sqlstring@2.3.3\node_modules\sqlstring\lib\SqlString.js:201:39)
at Object.escape (D:\owner\node-base-example\jwt\node_modules\.pnpm\sqlstring@2.3.3\node_modules\sqlstring\lib\SqlString.js:56:21)
at Object.objectToValues (D:\owner\node-base-example\jwt\node_modules\.pnpm\sqlstring@2.3.3\node_modules\sqlstring\lib\SqlString.js:180:89)
at Object.escape (D:\owner\node-base-example\jwt\node_modules\.pnpm\sqlstring@2.3.3\node_modules\sqlstring\lib\SqlString.js:54:26)
at Object.format (D:\owner\node-base-example\jwt\node_modules\.pnpm\sqlstring@2.3.3\node_modules\sqlstring\lib\SqlString.js:100:19)
at PoolConnection.format (D:\owner\node-base-example\jwt\node_modules\.pnpm\mysql2@3.6.2\node_modules\mysql2\lib\connection.js:525:22)
at PoolConnection.query (D:\owner\node-base-example\jwt\node_modules\.pnpm\mysql2@3.6.2\node_modules\mysql2\lib\connection.js:570:25)
at D:\owner\node-base-example\jwt\node_modules\.pnpm\typeorm@0.3.17_mysql2@3.6.2\node_modules\typeorm\driver\mysql\MysqlQueryRunner.js:148:36
at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
let updateIds = [6]
// 查询出用户信息
let userList = await userRepository.createQueryBuilder('user')
.where('user.userId In(:...list)', {
list: updateIds
})
.getMany()
list
字段是占位符
或者直接写 sql
let updateIds = [6]
let userList = await userRepository.createQueryBuilder('user')
.where(`user.userId In(${updateIds})`)
.getMany()
读取到的数据
[
{
userId: 6,
name: '张三',
password: '$2b$10$jG1mAa273qEHCAEafvXMteH5x7MSV8YKFEFUa1WgArV2eRex0SlYK',
phone: '',
status: 0,
nickname: null,
email: null,
avatar: null,
createTime: 2023-08-29T01:47:38.000Z,
updateTime: 2023-08-29T01:47:38.000Z
}
]
错误用法1
let userList = await userRepository.createQueryBuilder('user')
.where(`user.userId = :userId`, {
userId: In(updateIds)
})
.getMany()
这种写法其实是等于的意思, 我们知道 In是表示一个范围, :userId 这样写就不对, 这种写法就会出现上面的报错信息
错误用法2
let userList = await userRepository.createQueryBuilder('user')
.where(`user.userId = :userId`, {
userId: updateIds
})
.getMany()
这种其实是拿 updateIds 作为一个字符串 使用,其实这个类型是 数组类型,也不对
Like
当我们的入参中存在特殊字符,发现上面的方式并不行,执行查询语句会报错
2023-10-26 记录
例如:
parentIds: '[0],[11]'
parentIds: '[0],[11],[13]'
... 等等
我希望查询出,以xxx开头的数据
let item.parentIds = '[0],[11]'
let children = await menuRepository
.createQueryBuilder('menu')
.select(['menu.menuId', 'menu.parentIds', 'menu.isDelete', 'menu.menuName'])
.where('menu.parentIds = :parentIds', {
parentIds: Like(`${item.parentIds},%`)
})
.getMany()
执行会报错
\node_modules\.pnpm\sqlstring@2.3.3\node_modules\sqlstring\lib\SqlString.js:201
while ((match = CHARS_GLOBAL_REGEXP.exec(val))) {
^
TypeError: Cannot convert a Symbol value to a string
应该是字符串被 typeorm
转义了,导致存在问题
改为如下方式,使用原始 sql 可行
let children = await menuRepository
.createQueryBuilder('menu')
.select(['menu.menuId', 'menu.parentIds', 'menu.isDelete', 'menu.menuName'])
.where(`menu.parentIds Like('${item.parentIds},%')`)
.getMany()
排序 order by
场景: 现在有一个用户表,表里面有一些用户的状态(state
)是 启用(active
), 已停用(blocked
), 我想根据 用户状态做排序
sql
select * from user order by state, id ASC
js代码
import User from '../entity/user.js'
const userRepositoryEntity = dataSource.getRepository(User)
export const getUsers = async (req, res) => {
let query = {}
// ... 业务场景的 查询条件
try {
let result = await userRepositoryEntity.createQueryBuilder('user')
.where(query)
.skip((currentPage - 1) * pageSize)
.take(pageSize)
.orderBy("user.state", 'ASC')
.addOrderBy('user.id', "ASC")
.getManyAndCount()
if (result.length) {
res.json({
code: 1000,
data: {
list: result[0],
currentPage: currentPage,
pageSize: pageSize,
total: result[1]
},
message: '操作成功'
})
} else {
throw new Error('查询失败')
}
} catch (error) {
res.json({
code: 1,
data: [],
message: '操作失败' + error.message
})
}
})
注意:
这里做排序,必须加上主键 id, 否则在第二页,可能还会出现 第一页的数据, 如果没有 id作为 order by 的条件, 只能保证当前页是排序的
replace 批量更新
场景: 现在数据库中的表,我需要使用 replace
去批量替换一些数据,假设 包版本号 有 ^
, 现在我需要去掉, 方便其他接口做数据统计
import { dataSource } from '../config/db.js'
import PackageRepository from '../entity/package.js'
const packageEntity = dataSource.getRepository(PackageRepository)
async function run() {
const list = await packageEntity.find({
// 这里只需要 包 的table 这个表里面的id字段即可
select: ['id']
})
// 更新数据
await packageEntity
.createQueryBuilder()
.update(PackageRepository)
.set(
{
package_version: () => `REPLACE(package.package_version, '^', '')`
}
)
.where("id IN (:...ids)", {
ids: t.map(p => p.id)
})
.execute()
}
run()
join 连表(级联)查询
2023-06-07
新增
场景:希望将2个表做连表查询, 返回2个表中的内容, 例如: 我要查询出 package表中对应的在 仓库表中的 仓库名是什么
分析: 一个项目仓库 repository, 会对应多个 依赖(package), 所以是 1->N 的关系
src/entity/package.js
实体
import { EntitySchema} from 'typeorm'
import repositoryEntity from './repository.js'
export default new EntitySchema({
name: 'Package',
columns: {
id: {
type: 'int',
primary: true,
generated: true
},
project_id: {
type: 'int',
index: true,
comment: '项目仓库id'
},
package_name: {
type: 'varchar',
length: 100,
comment: 'npm 包名'
},
package_version: {
type: 'varchar',
length: 40,
comment: '当前包版本'
}
},
relations: {
// 与仓库关联 - 多个包 属于 一个仓库
repository: {
target: 'Repository',
// 固定的几个字符串
type: 'many-to-one',
// 不创建外键约束
createForeignKeyConstraints: false,
joinColumn: {
// 这里的关联字段是package实体的字段名
name: "project_id"
}
}
}
})
仓库表 src/entity/repository.js
import { EntitySchema } from 'typeorm'
export default new EntitySchema({
name: 'Repository',
columns: {
id: {
primary: true,
type: 'int',
generated: true,
comment: '仓库id'
},
name: {
type: 'varchar',
length: 100
},
description: {
type: 'varchar',
length: 255,
comment: '项目描述',
// 可为空
nullable: true
}
}
})
现在想要查询出package 表
中名对应的repository表
仓库名(name
)是什么
sql
select p.id, p.project_id ,p.package_version, r.description from test_db.package p join test_db.repository r on p.project_id = r.id;
结果
js 代码实现
import { dataSource } from '../config/db.js'
import PackageRepository from '../entity/package.js'
async function run() {
const packageEntity = dataSource.getRepository(PackageRepository)
const c1 = await packageEntity.createQueryBuilder("p")
.select([ 'p.id', 'p.project_id','p.package_name', 'p.package_version', 'p.repository'])
.innerJoinAndSelect("p.repository", 'r', 'p.project_id=r.id')
.getManyAndCount()
}
run()
拓展1: 加点条件判断进去
import { dataSource } from '../config/db.js'
import PackageRepository from '../entity/package.js'
async function run() {
const packageEntity = dataSource.getRepository(PackageRepository)
const c1 = await packageEntity.createQueryBuilder("p")
.select([ 'p.id', 'p.project_id','p.package_name', 'p.package_version', 'p.repository'])
.where({
package_name: 'vue',
package_version: Like('2.5%')
})
.innerJoinAndSelect("p.repository", 'r', 'p.project_id=r.id')
.getManyAndCount()
}
run()
翻译: 联表查询出 package 表和 repository表的内容, 且只查询出 包名为 vue, 包的版本在 2.5.x的数据
innerJoinAndSelect
是会返回所有的 关联表(respository
) 字段名
拓展2: repository返回的内容太多了, 我只想返回name字段
import { dataSource } from '../config/db.js'
import PackageRepository from '../entity/package.js'
async function run() {
const packageEntity = dataSource.getRepository(PackageRepository)
const c1 = await packageEntity.createQueryBuilder("p")
.select([ 'p.id', 'p.project_id','p.package_name', 'p.package_version', 'p.repository'])
.where({
package_name: 'vue',
package_version: Like('2.5%')
})
.innerJoin("p.repository", 'r','p.project_id=r.id')
.addSelect(['r.name', 'r.description'])
.getManyAndCount()
}
run()
也可以这样
const c1 = await packageEntity.createQueryBuilder("p")
.select([ 'p.id', 'p.project_id','p.package_name', 'p.package_version', 'r.name', 'r.description'])
.where({
package_name: 'vue',
package_version: Like('2.5%')
})
.innerJoin("p.repository", 'r','p.project_id=r.id')
.getManyAndCount()
返回部分字段改为 innerJoin
和 addSelect
添加要返回的字段名
关键点:
- 在 package.js 实体代码中加入
relations
, 并指定joinColumn
, 还需要指定是哪一种关系 innerJoinAndSelect
第一个参数是 属性, 第二个参数是表别名(alias), 第三个参数是条件, 这里的条件就是package 表
中的project_id
和 项目表(project
)中的id
是 一个意思
关系-js版本表达
typeorm中,案例大都是 基于typescript写的, typescript语法中,表达关系,使用的是装饰器
, 如果是js版本,怎么表达呢?
js版本
实体
import { EntitySchema} from 'typeorm'
export default new EntitySchema({
name: 'Package',
columns: {
... 表字段
},
// 关系
relations: {
target: '目标是谁-字符串类型',
// 关系的类型
type: ''
}
}
- 一对一
type: "one-to-one"
- 一对多
type: "one-to-many"
- 多对一
type: "many-to-one"
- 多对多
type: "many-to-many"
官网ts版本
import { Entity, PrimaryGeneratedColumn, Column, ManyToMany } from "typeorm";
import { Question } from "./Question";
@Entity()
export class Category {
@PrimaryGeneratedColumn()
id: number;
@Column()
name: string;
// 这里的type没啥意义
@ManyToMany(type => Question, question => question.categories)
questions: Question[];
}
使用原生sql
2023-10-16 新增
import { dataSource } from '../db/index.js'
const shopGoldRepository = dataSource.getRepository(ShopGoldEntiry)
// 最近15天
let startTime = dayjs().subtract(15, 'day').startOf('day').format('YYYY-MM-DD HH:mm:ss')
let endTime = dayjs().endOf('day').format('YYYY-MM-DD HH:mm:ss')
// 其他代码...
let type = list[0].type
let result = await shopGoldRepository.query(`select * from shop_gold where type="${type}" and createTime Between "${startTime}" and "${endTime}"`)
// result 返回的就是一个数组
该案例使用原生sql, sql解释: 查询最近15天,type =xxx的数据
transformer 使用
2023-10-20更新
/**
* 角色
*
* 一个权限可以给多个角色
* 一个角色可以拥有多个权限
*
* 所以 是N-N(多对多,ManyToMany)的关系
*/
import { EntitySchema } from 'typeorm'
import dayjs from 'dayjs'
export default new EntitySchema({
name: 'Role',
columns: {
roleId: {
type: 'int',
primary: true,
generated: 'increment',
comment: '角色Id'
},
roleName: {
type: 'varchar',
length: 40,
comment: '角色名称'
},
status: {
type: 'int',
default: 0,
comment: '角色状态 1-启用; 0-禁用'
},
createTime: {
type: 'datetime',
default: () => 'CURRENT_TIMESTAMP',
transformer: {
from (value) {
if (value) {
return dayjs(value).format('YYYY-MM-DD HH:mm:ss')
}
return
},
to(val) {
console.log('创建日期');
console.log(val);
}
}
},
updateTime: {
type: 'datetime',
default: () => 'CURRENT_TIMESTAMP',
transformer: {
from (value) {
if (value) {
return dayjs(value).format('YYYY-MM-DD HH:mm:ss')
}
return
},
// 需要加上这个, 否则, 在更新 role表,会报错,TypeError: transformer.to is not a function
to(val) {
return val
}
}
}
},
relations: {
// 最终在数据库会自动创建 role_permissions_permission 表
permissions: {
target: 'Permission',
type: 'many-to-many',
joinTable: true,
cascade: true
},
// 角色表和菜单表 多对多
menus: {
target: 'Menu',
type: 'many-to-many',
joinTable: true
}
}
})
注意: 如果上面没有提供
transformer
的to
函数, 在执行下面的 save方法 会报错 (TypeError: transformer.to is not a function),typeorm@0.3.7
文档没有对这个进行描述,没有说明 to函数是必传。 比较难意识到 实体类 需要加上这个,所以,最好做数据转换不要在 实体里面处理
更新 role 表里面的部分字段。
import { datasource } from '../../db/index.js'
const roleRepository = datasource.getRepository(roleEntity)
export const updateRoleStatus = async(req, res) => {
const {id, status} = req.body
try {
let row = await roleRepository.findOne({
where: {
roleId: id
}
})
if (!row) {
res.json({
code: -1,
data: null,
messaeg: '角色不存在'
})
return
}
// 如果更新时间没有生效,需要检查 Model中 transformer中的to函数是否有返回值,如果什么都没有返回,则updateTime字段是不会更新的
row.updateTime = dayjs().format('YYYY-MM-DD HH:mm:ss')
row.status = Number(status)
await roleRepository.save(row)
res.json({
code: 1000,
data: null,
message: '操作成功'
})
} catch (error) {
console.log(error);
res.json({
code: -1,
data: [],
message: '操作失败'+error.message
})
}
updateTime字段不更新?
逻辑层确定是写了 row.updateTime = dayjs().format('YYYY-MM-DD HH:mm:ss')
, 打印日志row也是对的。 数据库中 updateTime
字段还是没有更新, 需要检查 表实体
中 transformer
中的to函数是否有返回值
如果没有返回值, 是不会更新的
updateTime: {
type: 'datetime',
default: () => 'CURRENT_TIMESTAMP',
transformer: {
from (value) {
if (value) {
return dayjs(value).format('YYYY-MM-DD HH:mm:ss')
}
return
},
// 需要加上这个, 否则, 在更新 role表,会报错,TypeError: transformer.to is not a function
to(val) {
return val
}
}
不等于
2023-11-1 记录
示例:
mysql 查询 user表, 条件是, name = '张三' 且 status 不为0 的数据 。
mysql
select * from `user` u where name = '张三' AND u.status <> 1
使用代码实现, 下面使用 userId 也是一样的意思。
let userId = 9
let row = await userRepository.createQueryBuilder('user')
.where('user.userId =:userId', {
userId
})
.andWhere('user.status <> :userId', {
status: 1
})
.getOne()
上面使用 <>
和 mysql
语法一样的, 使用 :userId
作为占位符, 可以防止SQL注入
, typeorm 对传递的参数进行了转义处理。
结果
{
userId: 9,
name: '张三',
password: '$2b$10$2RKl8XcwYovku/CMX4DZM.iNzf.0/8Zq2cekJwUuogbF8QNHrLYHS',
phone: 'xxxxxx',
status: 0,
nickname: null,
email: null,
avatar: null,
createTime: 2023-11-01T05:50:36.000Z,
updateTime: 2023-11-01T05:50:36.000Z
}
使用 find 查找
import { Not } from 'typeorm'
let userId = 9
let row = await userRepository.find({
where: {
userId,
status: Not(1)
}
})
这里find 找到后,返回的结果是一个 数组对象