Rails中的find_by_sql，访问生成的数组

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.

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

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:

我有基于重量的运费价格的物品。重量存储在商品中，价格存储在表shipping_zone_prices中，我目前所做的只是查找与重量比销售商品重的第一行相关的价格：

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:

这种作品。 SQL完成了这项工作，但是当插入应用程序时，如下所示：

 <%= @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.

在此示例中，“12”是从数据库中提取的价格，并且是正确的。 @ item.shipping_price.class返回'Array'。尝试使用[0]（或任何其他整数）访问数组会返回空白。

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

有没有其他方法来访问它，或者我错过了一些基本的东西？

5 个解决方案

#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.

需要first.try（：price）部分，因为find_by_sql可能返回一个空数组。如果您尝试在空数组上执行类似first.price的操作，则会出现NoMethodError的异常：nil：NilClass的未定义方法'price'。

#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:

这是因为find_by_sql返回模型，而不是数据。如果您想直接获取有问题的数据，请使用以下内容：

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.

通过连接可以使用许多直接访问实用程序方法，这些方法可以获取单个值，单个数组，数组行或散列行。查看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:

与直接编写SQL时一样，您应该非常小心，不要创建SQL注入错误。这就是为什么通常最好将此方法封装在安全的地方。例：

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

#3

@item.shipping_price.first.price

要么

@item.shipping_price[0].price

Thanks Atastor for pointing that out!

感谢Atastor指出这一点！

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

在find_by_sql中使用AS价格时，price将成为结果的属性。

#4

If not for you saying that you tried and failed accessing [0] i'ld say you want to put

如果不是因为你说你试过并且访问失败[0]我会说你要放

@item.shipping_price.first.price # I guess BSeven just forgot the .first. in his solution

into the view...strange

进入视图......奇怪

#5

So, I had a hacky solution for this, but it works great. Create a table that has the same output as your function and reference it, then just call a function that does a find_by_sql to populate the model.

所以，我有一个hacky解决方案，但它很好。创建一个与函数具有相同输出并引用它的表，然后只调用一个执行find_by_sql的函数来填充模型。

Create a dummy table:

创建一个虚拟表：

CREATE TABLE report.compliance_year (
 id BIGSERIAL,
 year TIMESTAMP,
 compliance NUMERIC(20,2),
 fund_id INT);

Then, create a model that uses the empty table:

然后，创建一个使用空表的模型：

class Visualization::ComplianceByYear < ActiveRecord::Base
    self.table_name = 'report.compliance_year'
    def compliance_by_year(fund_id)
        Visualization::ComplianceByYear.find_by_sql(["
            SELECT year, compliance, fund_id
              FROM report.usp_compliance_year(ARRAY[?])", fund_id])
    end
end

In your controller, you can populate it:

在您的控制器中，您可以填充它：

def visualizations
  @compliancebyyear = Visualization::ComplianceByYear.new()
  @compliancefunds = @compliancebyyear.compliance_by_year(current_group.id)
  binding.pry
end

Then, you can see it populate with what you need:

然后，您可以看到它填充您需要的内容：

[1] pry(#<Thing::ThingCustomController>)> @compliancefunds
[
[0] #<Visualization::ComplianceByYear:0x00000008f78458> {
          :year => Mon, 31 Dec 2012 19:00:00 EST -05:00,
    :compliance => 0.93,
       :fund_id => 1
},
[1] #<Visualization::ComplianceByYear:0x0000000a616a70> {
          :year => Tue, 31 Dec 2013 19:00:00 EST -05:00,
    :compliance => 0.93,
       :fund_id => 4129
},
[2] #<Visualization::ComplianceByYear:0x0000000a6162c8> {
          :year => Wed, 31 Dec 2014 19:00:00 EST -05:00,
    :compliance => 0.93,
       :fund_id => 4129
}
]

#1