Get 4 ( top ) products per taxon

feed_taxon_product.rb

module Feed
  class TaxonProduct
    attr_accessor :taxon
    LIMIT = 4

    def initialize(taxon:, products:)
      @taxon    = taxon
      @products = products
    end

    def products
      @products.blank? ? [] : @products
    end

    def self.call(taxon_ids, options={} )
      limit = options[:limit] || LIMIT

      return [] if taxon_ids.blank?

      taxons = Spree::Taxon.includes(:translations).where(id: taxon_ids).order(:lft)

      sub_query = query_product_in_taxon(taxon_ids).to_sql

      taxon_products = Spree::Product.select("spree_products.*, spree_rank_taxons_products.taxon_id as taxon_id")
                                .joins("INNER JOIN ( #{sub_query} ) AS spree_rank_taxons_products ON spree_products.id = spree_rank_taxons_products.product_id")
                                .where(["spree_rank_taxons_products.product_top_rank <= ?", limit])
                                .includes(:translations, master: :prices)

      taxon_products = taxon_products
      taxons.map do |taxon|
        # taxon_product contain attribute taxon_id get from the query above
        products = taxon_products.select { |taxon_product| taxon_product.taxon_id == taxon.id }
        Feed::TaxonProduct.new(taxon: taxon, products: products)
      end

    end

    def self.query_product_in_taxon(taxon_ids)
      select_stm = "spree_products_taxons.*, DENSE_RANK() OVER( PARTITION BY taxon_id ORDER BY position ASC ) AS product_top_rank"
      Spree::Classification.select(select_stm)
                    .where(taxon_id: taxon_ids) # classifications table is spree_products_taxons
                    .where("product_id in (#{Feed::query_active_products.select('id').to_sql})")
    end
  end
end

#module Feed
#  def self.query_active_products
#    Spree::Product.not_discontinued.where("spree_products.available_on <= ?", Time.current )
#  end
#end

# Usage:
# taxon_ids = [308,302]
# Feed::TaxonProduct.call(taxon_ids, limit: 4).each do |tp|
# p tp.taxon
# p tp.products
# end