3
\$\begingroup\$

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")
\$\endgroup\$
1
\$\begingroup\$

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
\$\endgroup\$
  • \$\begingroup\$ Can I achieve this any cleaner but using associations through the models? \$\endgroup\$ – Boss Nass Jan 7 '16 at 11:55
  • \$\begingroup\$ 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. \$\endgroup\$ – Greg Burghardt Jan 7 '16 at 13:40
  • \$\begingroup\$ 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" \$\endgroup\$ – Boss Nass Jan 10 '16 at 11:01
  • \$\begingroup\$ Could you comment on how you are calling the method? \$\endgroup\$ – Greg Burghardt Jan 10 '16 at 21:03
  • 1
    \$\begingroup\$ 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. \$\endgroup\$ – Greg Burghardt Jan 11 '16 at 13:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.