[typeorm] createQueryBuilder 를 이용한 MYSQL FULLTEXT SEARCH 검색 테스트 예제

 

intest 라는 Entity는 아래와 같이 정의 되어 있습니다 .

  @Entity({ name : 'INTEST'})
  export class InTest {
  
      @PrimaryColumn({name : "intestgroupid" , nullable : false })
      intestgroupid : string;  

      @ManyToOne(() => TestDataDetail , (testdatadetail) => testdatadetail.intestgroupid)
      @JoinColumn({ name : 'intestgroupid' , referencedColumnName : 'intestgroupid'})
      testdatadetail : TestDataDetail;

  }

 

 

검색 할 키워드가 있을때만 join 과 WHERE 조건에 MATCH , AGAINST 키워드를 사용 하여 검색 하게 하였습니다 

(예제는 content 컬럼이 FULLTEXT 인덱스가 설정되어 있습니다)

  async getIntestList(input : IntestRequestDto, userToken : TokenPayload ) : Promise<IntestListResponseDto> {

    const query = this.intestRepository.createQueryBuilder('i')
                .select([ 
                  'i.intestgroupid as intestgroupid',
                  'i.intestid as intestid',
                  'i.title as title',
                ])
                .where('i.regdt >= :startdt' , { startdt : `${input.startDt.replace(/-/g,"")}000000` })
                .andWhere('i.regdt <= :enddt' , { enddt : `${input.endDt.replace(/-/g,"")}235959` })
                .andWhere('i.id = :id' , { ownerid : `${userToken.userid}` })
                .andWhere(`i.deleteyn = 'N'`);

    this.logger.log(`${this.getIntestList.name} input.searchWord=${input.searchWord}`);
	
    // MYSQL FULLTEXT SEARCH 이용 MATCH , AGAINST 사용 
    if(input.searchWord !== undefined && input.searchWord !== null && input.searchWord !== "") {

      query.innerJoin('i.testdatadetail', 'testdatadetail' )
      .andWhere('testdatadetail.proctype = "ABC"')
      .andWhere(`MATCH(testdatadetail.content) AGAINST('${input.searchWord}*' IN BOOLEAN MODE) `)
      .groupBy('i.intestgroupid');

    }
    query.orderBy('i.regdt','DESC');


    if(input.offSet !== 0 || input.limit !== 0) {
      console.log(`${this.getIntestList.name} paging input.offSet=${input.offSet} , input.limit=${input.limit}`);
      // 페이징 처리 진행 
      query.offset(input.offSet).limit(input.limit); 
    }

    console.log(`${this.getIntestList.name} query=${query.getSql()}`);
    query.printSql();
    const result = await query.getRawMany();
    
    return {
      total : result.length,
      intest : result.map(a => IntestResponseDto.FromEntity(a) )
    };
  }