PostgreSQL搭配knex如何实现关联查询聚合图片名称为数组

原生PostgreSQL查询调整方案 #

你可以用PostgreSQL自带的array_agg聚合函数实现同房屋下图片名的数组聚合，搭配GROUP BY对房屋字段分组即可，具体语句如下：

SELECT 
  houses.id,
  houses.title,
  houses.city,
  houses.country,
  array_agg(images.name) AS "imagesNames",
  images.house_id
FROM houses 
INNER JOIN images ON houses.id = images.house_id
GROUP BY houses.id, images.house_id;

补充说明：

• array_agg(images.name)会把分组后同一组的所有name字段值聚合为数组
• 由于houses.id是Houses表的主键，PostgreSQL支持仅对houses.id分组即可覆盖所有Houses表的查询字段，不需要额外把title、city、country都加入GROUP BY子句也可正常执行，如有兼容性要求也可手动添加。

Knex实现方案 #

该需求完全可以通过knex实现，对应写法如下：

const result = await knex('houses')
  .join('images', 'houses.id', '=', 'images.house_id')
  .select(
    'houses.id',
    'houses.title',
    'houses.city',
    'houses.country',
    knex.raw('array_agg(images.name) as ??', ['imagesNames']),
    'images.house_id'
  )
  .groupBy('houses.id', 'images.house_id');

如果需要兼容严格SQL模式配置，可把所有非聚合字段都加入groupBy：

.groupBy('houses.id', 'houses.title', 'houses.city', 'houses.country', 'images.house_id')

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