20220930-備註更新 此篇文章為 0.2.x
// 範例
const firstUser = await connection
.getRepository(User)
.createQueryBuilder("user")
.where("user.id = :id", { id: 1 })
.getOne();
/**
* MySQL 執行方式
* SELECT
* user.id as userId,
* user.firstName as userFirstName,
* user.lastName as userLastName
* FROM users user
* WHERE user.id = 1
*/
❗ 警告 這種狀況將不會作用
// user.linkedSheep = :id 與 user.linkedCow = :id 都叫一樣的 :id
const result = await getConnection()
.createQueryBuilder('user')
.leftJoinAndSelect('user.linkedSheep', 'linkedSheep')
.leftJoinAndSelect('user.linkedCow', 'linkedCow')
.where('user.linkedSheep = :id', { id: sheepId })
.andWhere('user.linkedCow = :id', { id: cowId });
// ERROR
// 應將修正為這種分開對應相對應的參數
const result = await getConnection()
.createQueryBuilder('user')
.leftJoinAndSelect('user.linkedSheep', 'linkedSheep')
.leftJoinAndSelect('user.linkedCow', 'linkedCow')
.where('user.linkedSheep = :sheepId', { sheepId: sheepId })
.andWhere('user.linkedCow = :cowId', { cowId: cowId });
// SUCCESS
Select by QueryBuilder
WHERE
createQueryBuilder("user")
.where("user.name = :name", { name: "Timber" })
/**
* MySQL Query
* SELECT ... FROM users user WHERE user.name = 'Timber'
*/
aliases 別名
createQueryBuilder()
.select("user")
.from(User, "user") // 別名
/**
* MySQL Query
* SELECT ... FROM users user
*/
Joining
Inner and leftJoin (交集 和 左外部連結)
有這幾種innerJoin()、leftJoin()、innerJoinAndSelect()、leftJoinAndSelect()
innerJoin()、leftJoin()這兩種是只有兼任判斷並沒有做選取包含的行為innerJoinAndSelect()、leftJoinAndSelect()這兩種是只有兼任判斷並有做選取包含的行為
groupBy 群組
createQueryBuilder("user").groupBy("user.id")
/**
* MySQL Query
* SELECT ... FROM users user GROUP BY user.id
*/
Insert by QueryBuilder
⚠️ 建議不用使用 QueryBuilder 的 Insert
await getConnection()
.createQueryBuilder()
.insert()
.into(User)
.values([
{ firstName: "Timber", lastName: "Saw" },
{ firstName: "Phantom", lastName: "Lancer" }
])
.execute();
可直接使用 repository.save()，方便簡潔
const userRepository = getRepository(User);
user = { firstName: "Phantom", lastName: "Lancer" };
await userRepository.save(user);
Update by QueryBuilder
⚠️ 建議不用使用 QueryBuilder 的 Update
import {getConnection} from "typeorm";
await getConnection()
.createQueryBuilder()
.update(User)
.set({ firstName: "Timber", lastName: "Saw" })
.where("id = :id", { id: 1 })
.execute();
可直接使用 repository.update()，方便簡潔
const userRepository = getRepository(User);
user = { firstName: "Timber", lastName: "Saw" };
await userRepository.update({ id: 1 }, user);
Delete by QueryBuilder (小心使用)
import {getConnection} from "typeorm";
await getConnection()
.createQueryBuilder()
.delete()
.from(User)
.where("id = :id", { id: 1 })
.execute();