TypeORM 多对多查询实现

首先定义2个实体 Article 和 Category是多对多的关系，一篇文章可以有多个分类，一个分类可以包含多篇文章

实体定义

代码语言：javascript复制

import { Entity, Column, PrimaryGeneratedColumn, ManyToMany, JoinTable, } from 'typeorm'
import { IsNotEmpty } from 'class-validator'
import { Category } from './category'

@Entity()
export class Article {
  @PrimaryGeneratedColumn()
  id: number
  
  @Column()
  @IsNotEmpty()
  title: string

  @Column({
    select: false,
    type: 'text',
  })
  content: string

  @ManyToMany((type) => Category, {
    cascade: true,
  })
  @JoinTable()
  categories: Category[]
}

代码语言：javascript复制

import { Entity, Column, PrimaryGeneratedColumn } from 'typeorm'
import { IsNotEmpty } from 'class-validator'

@Entity()
export class Category {
  @PrimaryGeneratedColumn()
  id: number

  @Column()
  @IsNotEmpty()
  name: string
}

表结构

根据上面2个实体会自动实现3张表，表结构如下：

表article

代码语言：javascript复制

 ------------- -------------- ------ ----- ------------------ ---------------- 
| Field       | Type         | Null | Key | Default          | Extra          |
 ------------- -------------- ------ ----- ------------------ ---------------- 
| id          | int          | NO   | PRI | NULL             | auto_increment |
| title       | varchar(255) | NO   |     | NULL             |                |
| content     | text         | NO   |     | NULL             |                |
 ------------- -------------- ------ ----- ------------------ ---------------- 

表category

代码语言：javascript复制

 ----------- -------------- ------ ----- ------------------ ---------------- 
| Field     | Type         | Null | Key | Default          | Extra          |
 ----------- -------------- ------ ----- ------------------ ---------------- 
| id        | int          | NO   | PRI | NULL             | auto_increment |
| name      | varchar(255) | NO   |     | NULL             |                |
 ----------- -------------- ------ ----- ------------------ ---------------- 

表article_categories_category

代码语言：javascript复制

 ------------ ------ ------ ----- --------- ------- 
| Field      | Type | Null | Key | Default | Extra |
 ------------ ------ ------ ----- --------- ------- 
| articleId  | int  | NO   | PRI | NULL    |       |
| categoryId | int  | NO   | PRI | NULL    |       |
 ------------ ------ ------ ----- --------- ------- 

查询分页

如果查询的字段都在 Article 表内，可以使用Repository API 查询

代码语言：javascript复制

import { getManager,  Repository, FindManyOptions, FindConditions, Like } from 'typeorm'
...
...
const { pageSize = 20, pageNum = 1, title, tag } = ctx.request.query
const articleRepository: Repository<Article> = getManager().getRepository(
    Article
)
    
const where: FindConditions<Article> | FindConditions<Article>[] = {}
if (title) {
    where.title = Like(`%${title}%`)
}

const options: FindManyOptions<Article> = {
    where,
    relations: ['categories'],
    skip: (pageNum - 1) * pageSize,
    take: pageSize,
}

const [data, total] = await articleRepository.findAndCount(options)

但是如果查询的字段需要连表查询，leftJoin、 innerJoin， 就需要用到typeORM 提供的QueryBuilder

代码语言：javascript复制

import { getManager,  Repository, FindManyOptions, FindConditions, Like } from 'typeorm'
...

...
const { pageSize = 20, pageNum = 1, title, tag } = ctx.request.query
const articleRepository: Repository<Article> = getManager().getRepository(Article)
const sql = articleRepository
    .createQueryBuilder('article')
    .innerJoinAndSelect('article.categories', 'category')
if (title) {
    sql.where('article.title like :title ', { title: `%${title}%` })
}
if (tag) {
    sql.andWhere('category.name like :tag ', { tag: `%${tag}%` })
}
sql
    .skip((pageNum - 1) * pageSize)
    .take(pageSize)

const [data, total] = await sql.getManyAndCount()

ctx.status = 200
ctx.body = { data, total }

api categories 分页

0 人点赞