Taolin · TypeORM查询

TypeORM 是 node.js 现有社区最成熟的对象关系映射器（ORM ）

初始化

CodeBlock Loading...

TypeORM 查询操作

查询所有 find()

CodeBlock Loading...

查询指定字段 select

CodeBlock Loading...

条件查询 where

CodeBlock Loading...

条件查询 and关系 where 对象写法

CodeBlock Loading...

条件查询 o r关系 where 数组写法

CodeBlock Loading...

排序

CodeBlock Loading...

分页

CodeBlock Loading...

relations关系查询(前提是要先有外键关联关系)

CodeBlock Loading...

join关系查询

CodeBlock Loading...

排序

CodeBlock Loading...

分页查询

skip偏移(表示从哪里开始) take查询多少条数据

CodeBlock Loading...

Not

CodeBlock Loading...

LessThan小于,一般用于数字
LessThanOrEqual小于或者等于
MoreThan大于
MoreThanOrEqual大于等于
Equal等于
Like模糊查询 xx: LIKE(’%yy%’)
Between两个范围之间xx: Between(1,10)
In在什么里面xx: In([’’,’’])

find其他方法：

1、find查找返回一个数组 2、findOne查询返回一个对象,直接传递一个数字进去,会根据id去查询 3、findAndCount查询返回数量[data, count] 4、findByIds([])根据id数组查询

使用 Query Builder 查询

QueryBuilder是 TypeORM 最强大的功能之一，它允许你使用优雅便捷的语法构建 SQL 查询，执行并获得自动转换的实体。

条件查询

CodeBlock Loading...

计算所有人年龄

CodeBlock Loading...

删除操作

CodeBlock Loading...

添加操作

CodeBlock Loading...

表关系

一对一

@JoinColumn装饰器，它指示关系的这一侧将拥有该关系。关系可以是单向或双向的。关系只有一方可以拥有。

CodeBlock Loading...

一对多

参考

https://typeorm.biunav.com/zh/#%E5%AE%89%E8%A3%85

TypeORM 是 node.js 现有社区最成熟的对象关系映射器（ORM ）

初始化

yarn init -y

yarn add typeorm

npx typeorm init

CodeBlock Loading...

TypeORM 查询操作

查询所有 find()

const userRepository = connection.getRepository(User);
const result = await userRepository.find();
console.log(result);
==
select * from user;

CodeBlock Loading...

查询指定字段 select

const userRepository = connection.getRepository(User);
const result = await userRepository.find({ select: ['id', 'username'] });
console.log(result);
==
select id, username from user;

CodeBlock Loading...

条件查询 where

const userRepository = connection.getRepository(User);
const result = await userRepository.find({ where: { id: 1 } });
console.log(result);
==
select * from user where user.id = 1;

CodeBlock Loading...

条件查询 and关系 where 对象写法

const userRepository = connection.getRepository(User);
const result = await userRepository.find({ where: { id: 1, username: 'xx' } });
console.log(result);
==
select * from user where user.id = 1 and user.username = 'xx';

CodeBlock Loading...

条件查询 o r关系 where 数组写法

const userRepository = connection.getRepository(User);
const result = await userRepository.find({ where: [{ id: 1 }, { username: 'xx' }] });
console.log(result);
==
select * from user where user.id = 1 or user.username = 'xx';

CodeBlock Loading...

排序

const userRepository = await connection.getRepository(User);
const result = await userRepository.find({
  order: {
    'id': 'DESC', // ASC(升序) || DESC(降序)
  }
});
query: SELECT * FROM `user` ORDER BY `User`.`id` DESC

CodeBlock Loading...

分页

userRepository.find({
    skip: 5 // skip - 偏移（分页）
});

userRepository.find({
    take: 10 // take - limit (分页) - 得到的最大实体数
});

CodeBlock Loading...

relations关系查询(前提是要先有外键关联关系)

const userRepository = connection.getRepository(User);
const result = await userRepository.find({ relations: ['userDetail'] });
console.log(result);

==
  
select a.*,b.* from user as a left join user_extend as b on a.id = b.userId;

CodeBlock Loading...

join关系查询

const userRepository = connection.getRepository(User);
const result = await userRepository.find({
    join: {
        alias: 'user',
        leftJoinAndSelect: {
            detail: 'user.userDetail',
            posts: 'user.posts'
        }
    }
});
console.log(JSON.stringify(result));

CodeBlock Loading...

排序

const userRepository = connection.getRepository(User);
const result = await userRepository.find({
    order: {
        id: 'DESC',
        username: 'ASC'
    }
});
console.log(result);

== 
  
select * from user order by username asc, id desc;

CodeBlock Loading...

分页查询

skip偏移(表示从哪里开始) take查询多少条数据

const userRepository = connection.getRepository(User);
const result = await userRepository.find({
    skip: 0,
    take: 10,
})
console.log(result);

==
  
select * from user limit 0,10;

CodeBlock Loading...

Not

const userRepository = connection.getRepository(User);
const result = await userRepository.find({
    username: Not('王五')
});
console.log(result);

==
  
select * from user where username != '王五';

CodeBlock Loading...

LessThan小于,一般用于数字
LessThanOrEqual小于或者等于
MoreThan大于
MoreThanOrEqual大于等于
Equal等于
Like模糊查询 xx: LIKE(’%yy%’)
Between两个范围之间xx: Between(1,10)
In在什么里面xx: In([’’,’’])

find其他方法：

使用 Query Builder 查询

QueryBuilder是 TypeORM 最强大的功能之一，它允许你使用优雅便捷的语法构建 SQL 查询，执行并获得自动转换的实体。

条件查询

import "reflect-metadata";
import {createConnection} from "typeorm";
import { Photo } from "./entity/Photo";
import {User} from "./entity/User";

createConnection().then(async connection => {
    console.log('================================');
    const users = await connection
        .createQueryBuilder(User, 'user')
        .where({ // 条件查询
            firstName: '1'
        })
        .orderBy('user.id', 'DESC') // 排序
        .getMany();
    console.log(users);
}).catch(error => console.log(error));

==
  
SELECT * FROM `user` `user` WHERE `user`.`firstName` = `1` ORDER BY `user`.`id` DESC

CodeBlock Loading...

计算所有人年龄

import "reflect-metadata";
import {createConnection} from "typeorm";
import { Photo } from "./entity/Photo";
import {User} from "./entity/User";

createConnection().then(async connection => {
    console.log('================================');
    const users = await connection
        .createQueryBuilder(User, 'user')
        .where({ // 条件查询
            firstName: '1'
        })
        .orderBy('user.id', 'DESC') // 排序
        .select("SUM(user.age)", "totalAge")
        .getRawOne();
    console.log(users);
}).catch(error => console.log(error));

==
  
SELECT SUM(`user`.`age`) FROM `user` `user` WHERE `user`.`firstName` = 1 ORDER BY `user`.`id` DESC

CodeBlock Loading...

const userTotalCash = await getConnection()
      .createQueryBuilder(YkMemberMoneyLog, 'log')
      .where('log.type = 2 AND log.status = 1')
      .select('SUM(log.money)', 'sum')
      .getRawOne();

==
  
SELECT SUM(`log`.`money`) AS `sum` FROM `yk_member_money_log` `log` WHERE `log`.`type` = 2 AND `log`.`status` = 1

CodeBlock Loading...

删除操作

await connection.manager.remove(user);

CodeBlock Loading...

添加操作

import "reflect-metadata";
import {createConnection} from "typeorm";
import {User} from "./entity/User";

createConnection().then(async connection => {
    console.log('================================');
    let user = new User();
    user.firstName = "Jony"
    user.lastName = "HH"
    user.age = 22
    const userRepository = await connection.manager.save(user);
    console.log(userRepository)
}).catch(error => console.log(error));

CodeBlock Loading...

表关系

一对一

@JoinColumn装饰器，它指示关系的这一侧将拥有该关系。关系可以是单向或双向的。关系只有一方可以拥有。

import "reflect-metadata";
import {createConnection} from "typeorm";
import { Photo } from "./entity/Photo";
import {User} from "./entity/User";

createConnection().then(async connection => {
    console.log('================================');
    const userRepository = await connection.getRepository(User);
    const user = new User();
    const photo = new Photo();
    photo.name = '测试';
    user.firstName = '1'
    user.lastName = '2'
    user.photo = photo;
    user.age = 12
    await connection.manager.save(photo);
    await connection.manager.save(user);
}).catch(error => console.log(error));

CodeBlock Loading...

一对多

参考

https://typeorm.biunav.com/zh/#%E5%AE%89%E8%A3%85

TypeORM查询

关键洞察

TypeORM查询

初始化

TypeORM 查询操作

find其他方法：

使用 Query Builder 查询

删除操作

添加操作

表关系

一对一

一对多

参考

初始化

TypeORM 查询操作

find其他方法：

使用 Query Builder 查询

删除操作

添加操作

表关系

一对一

一对多

参考