TypeORM 多对多查询实现

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[]
}

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

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

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

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

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

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

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)

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 }