Ruby on rails instance loops I am trying to use a custom sql query to display different attributes for a product i.e. Size and Price. The query I have when running in console displays as it should

SELECT products.id, products.name, variant_properties.description, LEFT(variant_properties.description,1) as short_desc, variants.price FROM products
  INNER JOIN product_properties ON product_properties.product_id = products.id
  INNER JOIN variant_properties on product_properties.property_id = variant_properties.property_id AND variant_properties."primary" = true
  INNER JOIN properties ON properties.id = product_properties.property_id AND properties.id = variant_properties.property_id AND properties.display_name = 'Size'
  INNER JOIN variants on variants.product_id = products.id AND variants.id = variant_properties.variant_id




In my HAML template I have done the following

- @products.each_with_index do |product, i|
  .product-list.grid-block
    .small-8.grid-content.text-center
      %h4= product.name.titlecase
      - @sizes.each do |size|
        = link_to size.short_desc, product, class: 'hollow button tiny'
        %small= size.price


and in the controller

products = Product.active
    # products = Product.active.includes(:variants)

    product_types = nil
    if params[:product_type_id].present? &amp;&amp; product_type = ProductType.find_by_id(params[:product_type_id])
      product_types = product_type.self_and_descendants.map(&amp;:id)
    end
    if product_types
      @products = products.where(product_type_id: product_types)
    else
      @products = products
    end

      @sizes = Product.find_by_sql("SELECT products.id, LEFT(variant_properties.description,1) as short_desc, variants.price FROM products
  INNER JOIN product_properties ON product_properties.product_id = products.id
  INNER JOIN  variant_properties on product_properties.property_id = variant_properties.property_id
  INNER JOIN properties ON properties.id = product_properties.property_id AND properties.id = variant_properties.property_id AND properties.display_name = 'Size'
  INNER JOIN  variants on variants.product_id = products.id AND variants.id = variant_properties.variant_id")
I'm not seeing anything wrong with your SQL or your view. The controller can be cleaned up a lot. In fact, you could reduce your controller method to two lines.


The Product.find_by_sql call should be encapsulated in the Product class as its own static method
The SQL should be stored in a constant
The logic for finding products by product type, or defaulting to active products should also be a static method on the Product class


Basically, you should be able to do this in your controller:

@products = Product.find_by_product_types params[:product_type_id]
@sizes = Product.sizes


The Product class could be amended like below:

class Product &lt; ActiveRecord::Base
  SQL_FIND_SIZES = "SELECT products.id,
                           LEFT(variant_properties.description,1) AS short_desc,
                           variants.price
                    FROM products
                    INNER JOIN product_properties ON product_properties.product_id = products.id
                    INNER JOIN variant_properties ON product_properties.property_id = variant_properties.property_id
                    INNER JOIN properties ON     properties.id           = product_properties.property_id
                                             AND properties.id           = variant_properties.property_id
                                             AND properties.display_name = 'Size'
                    INNER JOIN variants ON variants.product_id = products.id AND variants.id = variant_properties.variant_id"
  def self.sizes
    self.find_by_sql SQL_FIND_SIZES
  end

  def self.find_by_product_types(product_type_id)
    return [] if product_type_id.nil?

    product_type = ProductType.find_by_id product_type_id
    product_types = product_type.self_and_descendants.map(&amp;:id) if product_type

    return if product_types.nil?
             self.active
           else
             self.where product_type_id: product_types
           end
  end
end