Rails 中的 find_by_sql，访问结果数组 [英] find_by_sql in Rails, accessing the resulting array

问题描述

我试图在 Rails 中以一种非常快速和肮脏的方式运行查询，而没有将模型的其余部分放在适当的位置.我知道这是不好的做法，但我只需要在紧迫的时间内快速得出结果，直到我制定出完整的解决方案.

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.

我有一些商品会根据重量确定运费.重量存储在物品中，价格存储在表shipping_zone_prices中，我目前所做的就是查找与重量比待售物品重的第一行相关的价格:

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

这种作品.SQL 可以完成这项工作，但是当插入应用程序时，如下所示:

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

 <%= @item.shipping_price %> Shipping

我得到以下显示:

[#<ShippingZonePrice price: 12>] Shipping

在这个例子中，'12' 是从数据库中提取的价格，是正确的[e-mail]_price.class 返回数组".尝试使用 [0](或任何其他整数)访问数组会返回空白.

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?

推荐答案

既然你定义了一个实例方法，我认为它应该返回 price 如果它存在或 nil>

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

尝试这样的事情:

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

那么这应该适合你:

@item.shipping_price

first.try(:price) 部分是必需的，因为 find_by_sql 可能返回一个空数组.如果您尝试在空数组上执行类似 first.price 的操作，您将收到类似 NoMethodError: undefined method 'price' for nil:NilClass 的异常.

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.

这篇关于Rails 中的 find_by_sql，访问结果数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！