Spree::Variant.class_eval do
  scope :with_distinct_images, -> {
    ordered_ids = pluck("`spree_variants`.`id`")
    variant_assets_sql = "SELECT MD5(GROUP_CONCAT(`spree_assets`.`image_type`, `spree_assets`.`attachment_file_name`)) FROM `spree_assets`
      WHERE `spree_assets`.`type` IN ('Spree::Image') AND `spree_assets`.`viewable_id` = `spree_variants`.`id` AND `spree_assets`.`viewable_type` = 'Spree::Variant'
      ORDER BY `spree_assets`.`image_type` ASC, `spree_assets`.`attachment_file_name` ASC"
    variant_product_assets_sql = "SELECT MD5(GROUP_CONCAT(`spree_assets`.`image_type`, `spree_assets`.`attachment_file_name`)) FROM `spree_assets`
      INNER JOIN `spree_products` ON `spree_products`.`deleted_at` IS NULL
      INNER JOIN `spree_variants` `variants_including_masters_spree_products_join` ON `variants_including_masters_spree_products_join`.`product_id` = `spree_products`.`id` AND `variants_including_masters_spree_products_join`.`is_master` = 1 AND `variants_including_masters_spree_products_join`.`deleted_at` IS NULL
      WHERE `spree_products`.`id` = `spree_variants`.`product_id` AND `spree_assets`.`type` IN ('Spree::Image') AND `spree_assets`.`viewable_id` = `variants_including_masters_spree_products_join`.`id` AND `spree_assets`.`viewable_type` = 'Spree::Variant'
      ORDER BY `spree_assets`.`image_type` ASC, `spree_assets`.`attachment_file_name` ASC"

    sql = select("MAX(`spree_variants`.`id`) AS id, IFNULL((#{variant_assets_sql}), (#{variant_product_assets_sql})) AS md5")
      .group("md5").to_sql
    scoped = select("`spree_variants`.*, IFNULL((#{variant_assets_sql}), (#{variant_product_assets_sql})) AS md5")
      .joins("INNER JOIN (#{sql}) AS `spree_variants_md5` ON `spree_variants_md5`.`id` = `spree_variants`.`id` AND `spree_variants_md5`.`md5` = md5")
    scoped = scoped.order("FIELD(`spree_variants_md5`.`id`, #{ordered_ids.join(', ')})") unless ordered_ids.empty?
    scoped
  }
end