左外连接按关联值排序产生重复结果的ActiveRecord解决方法

问题 #

执行LEFT OUTER JOIN查询时得到重复且无序的结果，原因是一件艺术品可能对应多条符合WHERE条件的artwork_colors记录。需求如下：

• 每件艺术品仅在结果中出现一次
• 仅选取该艺术品中符合color_id条件且pixel_percent最高的artwork_color记录
• 以此最高pixel_percent值对艺术品进行排序
• 必须使用ActiveRecord编写查询，适配现有分页系统

原SQL查询：

# color_ids是与搜索颜色相似的颜色ID集合
# 例如搜索FF0000时，会包含相似的红色色调

SELECT DISTINCT
    "artwork_colors"."pixel_percent",
    artworks.*
FROM
    "artworks"
    LEFT OUTER JOIN "artwork_colors" ON "artwork_colors"."artwork_id" = "artworks"."id"
WHERE
    "artwork_colors"."color_id" IN(106, 108, 119, 120, 128, 133, 156, 160)
ORDER BY
    "artwork_colors"."pixel_percent" DESC
LIMIT 120 OFFSET 0;

原ActiveRecord查询：

artworks
  .includes(:artwork_colors)
  .where('artwork_colors.color_id': color_ids)
  .order(pixel_percent: :desc)
  .select('artworks.*', 'artwork_colors.pixel_percent')
  .distinct

相关模型与表结构：

class Artwork < ApplicationRecord
  has_many :artwork_colors, dependent: :destroy
  has_many :colors, through: :artwork_colors
end

class ArtworkColor < ApplicationRecord
  belongs_to :artwork
  belongs_to :color
end

class Color < ApplicationRecord
  # 这些颜色由图像分析工具Amazon Rekognition从艺术品图像中提取
  has_many :artwork_colors, dependent: :destroy
  has_many :artworks, through: :artwork_colors
end

CREATE TABLE public.artwork_colors (
  id bigint NOT NULL,
  pixel_percent double precision, # 这是期望的排序列
  artwork_id bigint,
  color_id bigint
);

# h s l分别为色相（hue）、饱和度（saturation）、亮度（lightness）（颜色值）
CREATE TABLE public.colors (
  id bigint NOT NULL,
  h double precision,
  s double precision,
  l double precision
);

解决方案 #

方法一：子查询筛选最高pixel_percent记录 #

先通过子查询获取每个符合条件的艺术品对应的最高pixel_percent值，再关联原表筛选目标记录，最后排序。

ActiveRecord代码：

# 子查询：获取每个符合color_id条件的艺术品的最高pixel_percent
max_percent_subquery = ArtworkColor
  .select('artwork_id, MAX(pixel_percent) as max_pixel_percent')
  .where(color_id: color_ids)
  .group(:artwork_id)

# 关联子查询，筛选出对应记录并排序
Artwork
  .joins(:artwork_colors)
  .joins("INNER JOIN (#{max_percent_subquery.to_sql}) AS max_pct ON artwork_colors.artwork_id = max_pct.artwork_id AND artwork_colors.pixel_percent = max_pct.max_pixel_percent")
  .where(artwork_colors: { color_id: color_ids })
  .order('max_pct.max_pixel_percent DESC')
  .select('artworks.*, max_pct.max_pixel_percent')
  .distinct

方法二：使用窗口函数（适用于PostgreSQL等支持窗口函数的数据库） #

用窗口函数为每个艺术品的符合条件的artwork_colors记录按pixel_percent降序排名，直接取排名第一的记录。

ActiveRecord代码：

# 子查询：为每个艺术品的符合条件的颜色记录排名
ranked_colors_subquery = ArtworkColor
  .select('*, ROW_NUMBER() OVER (PARTITION BY artwork_id ORDER BY pixel_percent DESC) as rn')
  .where(color_id: color_ids)
  .to_sql

# 关联子查询，筛选排名第一的记录并排序
Artwork
  .joins("INNER JOIN (#{ranked_colors_subquery}) AS ranked_acs ON artworks.id = ranked_acs.artwork_id AND ranked_acs.rn = 1")
  .order('ranked_acs.pixel_percent DESC')
  .select('artworks.*, ranked_acs.pixel_percent')

原查询问题说明 #

原查询的DISTINCT会把artworks.*和artwork_colors.pixel_percent的组合作为去重依据——如果一件艺术品有多条符合条件的artwork_colors记录，每个不同的pixel_percent都会保留，导致艺术品重复出现；同时多条记录的存在也会干扰排序逻辑，出现无序情况。

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