Geek asks and answers

Find_by_sql in Rails, access to the resulting array

I try to execute a query very quickly and dirty in Rails, without putting the rest of the model in place. I know that this is bad practice, but I need a quick result in a short time, until I have the whole solution.

I have items that have a shipping price based on weight. The weight is stored in the item, the price is stored in the shipping_zone_prices table, and all I am doing now is the price pertaining 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

Such work. SQL does the job, but when connected to the application it looks like this:

 <%= @item.shipping_price %> Shipping

I get the following:

[#<ShippingZonePrice price: 12>] Shipping

"12" - , db, . @item.shipping_price.class "". [0] ( ) .

, - ?

+5
5

, , price, , 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.

+6

, find_by_sql , . , , - :

ShippingZonePrice.connection.select_value(query)

, connection, , , . ActiveRecord::ConnectionAdapters::DatabaseStatements.

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
+5
@item.shipping_price.first.price


@item.shipping_price[0].price

Atastor , !

AS price find_by_sql, price .

+3

, [0], ,

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

...

+1

So, I had a hacker solution for this, but it works fine. Create a table that has the same result as your function and references it, and then just call the function that does find_by_sql to populate the model.

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 an 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 fill it out:

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

Then you can see that it is filled 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
}
]
0
source

More articles:


All Articles