PostgreSQL+MikroORM中Left Join加DISTINCT ON返回重复行问题

核心问题分析 #

你遇到的矛盾本质是PostgreSQL的DISTINCT ON特性导致的：

• 当DISTINCT ON包含sector.id时，每条记录会以「地址+单个行业」为唯一标识，同一个地址会因关联多个行业被重复返回，同时limit会被这些重复条目占用，最终实际返回的唯一地址数量不足。
• 去掉sector.id后，DISTINCT ON只以地址为唯一标识，但PostgreSQL仅会保留每个地址匹配到的第一条行业数据，导致关联的Sectors不全。

下面提供两种实用的解决方案：

方案1：分两步查询（推荐，符合MikroORM实体操作习惯） #

先查询符合距离条件的唯一地址，再批量加载这些地址关联的所有Sectors：

步骤1：获取唯一地址列表 #

const addresses = await orm.em
  .createQueryBuilder(Address, 'a')
  // 选择地址字段和距离计算结果
  .select(['a.*', 'ST_Distance(a.geom, ST_SetSRID(ST_MakePoint(:lng, :lat), 4326)) AS distance'])
  // 按距离过滤
  .where('ST_DWithin(a.geom, ST_SetSRID(ST_MakePoint(:lng, :lat), 4326), :radius)')
  // 按距离升序排序
  .orderBy({ distance: 'ASC' })
  // 仅以地址ID去重，保证地址唯一
  .distinctOn(['a.id'])
  // 控制返回数量和分页
  .limit(limit)
  .offset(offset)
  .setParams({ lng: 0, lat: 0, radius: 10000 })
  .getResult();

步骤2：批量加载关联的Sectors #

用MikroORM的populate方法批量加载所有地址关联的Business及下属Sectors：

await orm.em.populate(addresses, ['business.sectors']);

这样既保证了地址的唯一性，又能完整加载每个地址对应的所有Sectors，同时limit和offset能精准控制返回的地址数量。

方案2：单查询聚合Sectors（适合需要一次查询完成的场景） #

利用PostgreSQL的json_agg聚合函数，将每个地址关联的所有Sectors聚合为JSON数组，再手动映射回实体：

const rawResults = await orm.em
  .createQueryBuilder(Address, 'a')
  .select([
    'a.*',
    'ST_Distance(a.geom, ST_SetSRID(ST_MakePoint(:lng, :lat), 4326)) AS distance',
    // 聚合所有关联的Sectors为JSON数组
    'json_agg(s.*) AS sectors'
  ])
  .leftJoin('a.business', 'b')
  .leftJoin('b.sectors', 's')
  .where('ST_DWithin(a.geom, ST_SetSRID(ST_MakePoint(:lng, :lat), 4326), :radius)')
  // 按地址ID分组，保证地址唯一
  .groupBy('a.id')
  .orderBy({ distance: 'ASC' })
  .limit(limit)
  .offset(offset)
  .setParams({ lng: 0, lat: 0, radius: 10000 })
  .getRawResult();

// 将聚合的JSON数组映射回Sector实体
const addresses = rawResults.map(row => {
  const address = orm.em.map(Address, row);
  address.business.sectors = row.sectors.map(sectorData => orm.em.map(Sector, sectorData));
  return address;
});

这种方法通过GROUP BY a.id确保地址唯一，同时用json_agg完整保留所有关联的Sectors数据，最后手动映射为实体对象。

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