如何将复杂SQL语句转换为TypeORM查询语句，正确实现WHERE IN子句

修正后的查询代码 #

import { In } from 'typeorm'; // 仅使用数组形式IN时需要导入

const queryResult = await this.createQueryBuilder('mytable')
  .select('mytable.councillor', 'councillor')
  .addSelect('join_time.first_join_time', 'first_join_time')
  .addSelect('state.latest_state', 'latest_state')
  .addSelect('member_term.member_term_count', 'member_term_count')
  .addSelect('proposal.proposal_count', 'proposal_count')
  .addSelect('approve.approved_count', 'approved_count')
  // 关联首次加入时间子查询
  .leftJoin(
    subQuery => subQuery
      .select('councillor')
      .addSelect('timestamp', 'first_join_time')
      .from(CouncilCouncillor, 'cc')
      .where('cc.timestamp IN (SELECT MIN(timestamp) FROM mytable GROUP BY councillor)'),
    'join_time',
    'join_time.councillor = mytable.councillor'
  )
  // 关联最新状态子查询
  .leftJoin(
    subQuery => subQuery
      .select('councillor')
      .addSelect('state', 'latest_state')
      .from(CouncilCouncillor, 'cc')
      .where('cc.timestamp IN (SELECT MAX(timestamp) FROM mytable GROUP BY councillor)'),
    'state',
    'state.councillor = mytable.councillor'
  )
  // 关联Member状态任期统计
  .leftJoin(
    subQuery => subQuery
      .select('councillor')
      .addSelect('COUNT(*)', 'member_term_count')
      .from(CouncilCouncillor, 'cc')
      .where('cc.state = :state', { state: 'Member' })
      .groupBy('cc.councillor'),
    'member_term',
    'member_term.councillor = mytable.councillor'
  )
  // 关联提案数量统计
  .leftJoin(
    subQuery => subQuery
      .select('proposer')
      .addSelect('COUNT(*)', 'proposal_count')
      .from(CouncilProposal, 'cp')
      .groupBy('cp.proposer'),
    'proposal',
    'proposal.proposer = mytable.councillor'
  )
  // 关联赞同票数量统计
  .leftJoin(
    subQuery => subQuery
      .select('account')
      .addSelect('COUNT(*)', 'approved_count')
      .from(CouncilProposalVote, 'cpv')
      .where('cpv.approve = :approve', { approve: true })
      .groupBy('cpv.account'),
    'approve',
    'approve.account = mytable.councillor'
  )
  .groupBy('mytable.councillor')
  .addGroupBy('join_time.first_join_time')
  .addGroupBy('state.latest_state')
  .addGroupBy('member_term.member_term_count')
  .addGroupBy('proposal.proposal_count')
  .addGroupBy('approve.approved_count')
  // 仅需要指定字段用getRawMany，需要返回完整实体+统计字段用getManyAndCount
  .getRawMany();

你遇到的两个问题的原因及解决 #

• 字段过滤不生效：你使用了leftJoinAndMapOne方法，该方法会把关联查询的结果映射到原实体的属性上，而getManyAndCount返回的是完整的实体实例，默认会包含实体定义的所有字段。如果只需要你声明的几个统计字段，把返回方法改成getRawMany即可；如果需要保留实体结构，只需调整leftJoinAndMapOne的第一个映射属性参数，不要重复映射到mytable.councillor覆盖原有字段。
• WHERE IN语法错误：你误用了whereInIds方法，该方法仅用于匹配表的主键ID字段。TypeORM中IN子句有两种常用写法：
  1. 子查询形式：直接在where中写入IN逻辑即可，比如where('字段名 IN (子查询语句)')
  2. 固定值数组形式：导入TypeORM内置的In方法，写法为where({ 字段名: In([值1, 值2, 值3]) })

内容的提问来源于stack exchange，提问作者99kies