我得到重复的，有时无序的结果与做左外加入。
我认为问题在于有许多artwork_colors可以匹配WHERE条件。但是我只需要artwork的artwork_color具有最高的pixel_percent（也匹配WHERE子句），并使用该值对artwork进行排序。当然，每件作品在结果中只出现一次。
如何重写ActiveRecord查询以正确排序结果，同时避免重复？（这个查询必须使用ActiveRecord进行，这样我就可以将其插入到现有的分页系统中，等等）
针对此问题简化的查询：

# color_ids is an group of ids of colors that are similar to the colors being searched for
# for example if you search for FF0000 it will include similar shades of red

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

CREATE TABLE public.artwork_colors (
  id bigint NOT NULL,
  pixel_percent double precision, # this is the desired sort column
  artwork_id bigint,
  color_id bigint
);

class Color < ApplicationRecord
  # These colors were extracted from the Artwork images by Amazon Rekognition, an image analysis tool
  has_many :artwork_colors, dependent: :destroy
  has_many :artworks, through: :artwork_colors
end

# h s l are hue, saturation, lightness (the color value)
CREATE TABLE public.colors (
  id bigint NOT NULL,
  h double precision,
  s double precision,
  l double precision
);