Ruby on rails instance loops

Question

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

enter image description here

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? && product_type = ProductType.find_by_id(params[:product_type_id])
 product_types = product_type.self_and_descendants.map(&: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")

Answer 1

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 < 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(&:id) if product_type
 return if product_types.nil?
 self.active
 else
 self.where product_type_id: product_types
 end
 end
end

• Boss Nass: Can I achieve this any cleaner but using associations through the models?
• Greg Burghardt: Not that I'm aware of. You are returning all products by type (recursively) or returning active product types. The or condition is what breaks your ability to use associations. That is logic not codified as tables and foreign key relationships in the database.
• Boss Nass: this above code works, but I'm still confused why rails gives me no method for prod_name "undefined method `prod_name' for [1, 1, "Bubble Tea"]:Array"
• Greg Burghardt: Could you comment on how you are calling the method?
• Greg Burghardt: I wonder if the return [] if product_type_id.nil? is causing the error, since it is returning an Array instead of an ActiveRecord query builder object.