sql jazz

FacilityInfo = Struct.new(:facility, :pallet_quantity, :case_quantity, :item_quantity)

def index 
products = Product.by_company(current_user.company.slug).includes(:facilities, :total_stocks).distinct(:sku)
sql = "SELECT DISTINCT ON (product_id) *  FROM facility_stocks WHERE product_id IN (#{@products.pluck(:id).join(', ')}) ORDER BY product_id, created_at DESC"
facility_stocks = ActiveRecord::Base.connection.execute(sql)
facilities = Facility.all 


@facility_collection_for_view = facilities.map do |facility|
  pallet_quantity = facility_stocks.select { |h| h['facility_id' ] == facility.id}.map { |h| h['pallet_quantity'] }.sum
  case_quantity = facility_stocks.select { |h| h['facility_id' ] == facility.id}.map { |h| h['case_quantity'] }.sum
  item_quantity = facility_stocks.select { |h| h['facility_id' ] == facility.id}.map { |h| h['item_quantity'] }.sum
  FacilityInfo.new(facility: facility, pallet_quantity: pallet_quantity, case_quantity: case_quantity, item_quantity: item_quantity)
end
end 


#in view 

<% @facility_collection_for_view.each do |facility_info| %>
    <td><%= facility_info.pallet_quantity %></td>
    <td><%= facility_info.case_quantity %></td>
    <td><%= facility_info.item_quantity %></td>
<% end %>