CODEWITHSUNDEEP
ruby-on-railsrubypostgresql
Selim Alawwa
Selim Alawwa

Reputation: 762

Rails Select calculated values from associations

I need to calculate the total sales of top 10 variants and return a hash of {variant_id, total_sales}. but total sales is a calculated attribute.

My models, order.rb

has_many :line_items

line_item.rb

has_one :variant

variant.rb

belongs_to :root_variant # ( a self_join assosciation )

MY query is as follow but I can't get it to return the calculated sales as it's not part of a model

Spree::Variant.joins(line_items: :order).where(spree_orders: {completed_at: params[:start_date]..params[:end_date]})
                                        .select('spree_variants.*, SUM(spree_line_items.quantity) as sales')
                                        .group("spree_variants.root_variant_id, spree_variants.id")
                                        .order('sales DESC').limit(10)

Upvotes: 0

Views: 221

Answers (1)

zauzaj
zauzaj

Reputation: 1226

You would be able to fetch it like: 

res = Spree::Variant.joins(line_items: :order).where(spree_orders: {completed_at: params[:start_date]..params[:end_date]})
                                        .select('spree_variants.root_variant_id as root_variant_id, SUM(spree_line_items.quantity) as sales')
                                        .group("spree_variants.root_variant_id")
                                        .order('sales DESC').limit(10)

then do 

  res.map{|m| { variant_id: m.root_variant_id, total_sales: m.sales } }

You suppose to get array of hashes.

Let me know if this does works for you.

Cheers

Upvotes: 1

Related Questions