find_by_sql in Rails, accessing the resulting array

Question

I'm trying to run a query in a very quick and dirty way in Rails, without putting the rest of the model in place. I know this is bad practice but I just need a quick result in a tight timeframe until I've got the whole solution in place.

I've got items that have a shipping price, based on weight. The weight is stored in the item, the price is stored in the table shipping_zone_prices, and all I currently do is look for the price relating to the first row where the weight is heavier than the item for sale:

class Item < ActiveRecord::Base
  def shipping_price
    item_id = self.id
    shipping_price = ShippingZonePrice.find_by_sql(
      "SELECT z.price as price
       FROM shipping_zone_prices z, items i
       WHERE i.id = '#{item_id}'
       AND z.weight_g > d.weight
       ORDER BY z.weight_g asc limit 1")    
  end
end

This sort of works. The SQL does the job, but when plugged into the app as follows:

 <%= @item.shipping_price %> Shipping

I get the following displayed:

[#<ShippingZonePrice price: 12>] Shipping

In this example, '12' is the price that is being pulled from the db, and is correct. @item.shipping_price.class returns 'Array'. Trying to access the array using [0] (or any other integer) returns a blank.

Is there another way to access this, or am I missing something fundamental?

Answer 1

Since you are defining an instance method, I think it should return the price if it exists or nil

Try something like this:

def shipping_price
  ShippingZonePrice.find_by_sql(
    "SELECT z.price as price
     FROM shipping_zone_prices z, items i
     WHERE i.id = '#{self.id}'
     AND z.weight_g > d.weight
     ORDER BY z.weight_g asc limit 1").first.try(:price)
end

Then this should work for you:

@item.shipping_price

The first.try(:price) part is needed because find_by_sql may return an empty array. If you tried to do something like first.price on an empty array, you would get an exception along the lines of NoMethodError: undefined method 'price' for nil:NilClass.

Answer 2

This is because find_by_sql returns a model, not data. If you want to do a direct fetch of the data in question, use something like this:

ShippingZonePrice.connection.select_value(query)

There are a number of direct-access utility methods available through connection that can fetch single values, a singular array, rows of arrays, or rows of hashes. Look at the documentation for ActiveRecord::ConnectionAdapters::DatabaseStatements.

As when writing an SQL directly, you should be very careful to not create SQL injection bugs. This is why it is usually best to encapsulate this method somewhere safe. Example:

class ShippingZonePrice < ActiveRecord::Base
  def self.price_for_item(item)
    self.connection.select_value(
      self.sanitize_sql(
        %Q[
          SELECT z.price as price
            FROM shipping_zone_prices z, items i
            WHERE i.id=?
              AND z.weight_g > d.weight
            ORDER BY z.weight_g asc limit 1
        ],
        item.id
      )
    )
  end
end

Answer 3

@item.shipping_price.first.price

or

@item.shipping_price[0].price

Thanks Atastor for pointing that out!

When you use AS price in find_by_sql, price becomes a property of the result.