ruby-on-rails - Rails ActiveRecord 插入/更新范围类型 (Postgresql)

Question

我的数据库中有一个 height_ranges 表，它存储数值范围。

迁移文件

class CreateHeightRanges < ActiveRecord::Migration
  def change
    create_table :height_ranges do |t|
      t.references :classification, index: true, foreign_key: true
      t.references :tree, index: true, foreign_key: true
      t.numrange :h_range
      t.integer :diameter

      t.timestamps null: false
    end
  end
end

我可以通过原始 sql 查询更新表，

UPDATE height_ranges SET h_range = numrange(5.6, 9.8, '[]') WHERE id = 1;

但是当涉及到 ActiveRecord 时，它出人意料地省略了下限。

[10] pry(main)> hr = HeightRange.first;
  HeightRange Load (0.5ms)  SELECT  "height_ranges".* FROM "height_ranges"  ORDER BY "height_ranges"."id" ASC LIMIT 1
[11] pry(main)> hr.h_range = "numrange(6.2, 9.8, '[]')"
=> "numrange(6.2, 9.8, '[]')"
[12] pry(main)> hr.save
   (0.2ms)  BEGIN
  SQL (1.9ms)  UPDATE "height_ranges" SET "h_range" = $1, "updated_at" = $2 WHERE "height_ranges"."id" = $3  [["h_range", "[0.0,9.8)"], ["updated_at", "2017-10-09 10:18:03.178971"], ["id", 1]]
   (13.6ms)  COMMIT
=> true
[13] pry(main)>

另一个查询，

[13] pry(main)> d = {"classification_id"=>"2", "tree_id"=>"4", "diameter"=>"16", "h_range"=>"numrange(5.3, 7.5, '[]')"}
=> {"classification_id"=>"2", "tree_id"=>"4", "diameter"=>"16", "h_range"=>"numrange(5.3, 7.5, '[]')"}
[14] pry(main)> hr.update_attributes(d)
   (0.2ms)  BEGIN
  SQL (0.3ms)  UPDATE "height_ranges" SET "h_range" = $1, "updated_at" = $2 WHERE "height_ranges"."id" = $3  [["h_range", "[0.0,7.5)"], ["updated_at", "2017-10-09 10:20:30.638967"], ["id", 1]]
   (21.4ms)  COMMIT
=> true

一个有效的半 AR 查询是，

HeightRange.where(id: hr.id).update_all("h_range = numrange(5.5, 9.4, '[]')")

作为最后的手段，我可​​以使用第一个(原始)和最后一个(将原始查询传递给 ActiveRecord 方法)但是属性设置方法有什么问题？

我当前的实现在模型中有那些辅助方法:

def unusual_update dict    
  HeightRange.where(id: id).update_all(attrs_to_sql_set_stmt(dict))    
end

def attrs_to_sql_set_stmt dct
  dct.map{|k,v| "#{k} = #{v}" }.join(", ")
end

在 Controller 中，我按如下方式触发更新:

def update
  r_min = params[:range_min].to_f
  r_max = params[:range_max].to_f
  h_range = "numrange(#{r_min}, #{r_max}, '[]')"

  height_range_params =  pre_params.merge({h_range: h_range})

  if @height_range.unusual_update(height_range_params)
    redirect_to admin_height_ranges_path, notice: 'Height range was successfully updated.'
  else
    render :edit
  end
end

Answer 1

PostgreSQL 范围应该是 fully supported通过 Rails。

我设法重现你的情况如下:

迁移:

#db/migrate/20171009152602_create_test_table.rb
class CreateTestTable < ActiveRecord::Migration[5.0]
  def up
    create_table :test_tables do |t|
      t.numrange :h_range
    end
  end
  def down
    drop_table :test_tables
  end
end

型号:

# app/models/test_table.rb
class TestTable < ActiveRecord::Base
end

numrange 在类型支持方面存在一些挑战:

$ rails c
Loading development environment (Rails 5.0.5)
2.4.0 :001 > c = TestTable.new
 => #<TestTable id: nil, h_range: nil> 
# Inserting Integer range
2.4.0 :002 > c.update(h_range:(3..5))
   (0.3ms)  BEGIN
  SQL (0.5ms)  INSERT INTO "test_tables" ("h_range") VALUES ($1) RETURNING "id"  [["h_range", "[3,5]"]]
   (16.7ms)  COMMIT
 => true 
2.4.0 :003 > c.h_range
 => 0.3e1..0.5e1 
2.4.0 :004 > c.h_range.to_a
TypeError: can't iterate from BigDecimal
2.4.0 :005 > c.h_range.step
 => #<Enumerator: 0.3e1..0.5e1:step(1)> 
2.4.0 :006 > c.h_range.step.to_a
 => [0.3e1, 0.4e1, 0.5e1] 

# Inserting Float range    
2.4.0 :007 > c.update(h_range:(3.4..5.8))
   (0.3ms)  BEGIN
  SQL (0.8ms)  UPDATE "test_tables" SET "h_range" = $1 WHERE "test_tables"."id" = $2  [["h_range", "[3.4,5.8]"], ["id", 1]]
   (20.7ms)  COMMIT
 => true 
2.4.0 :008 > c.h_range.step.to_a
 => [0.34e1, 0.44e1, 0.54e1] 
2.4.0 :009 > c.h_range.step(0.1).to_a
 => [3.4, 3.5, 3.6, 3.7, 3.8, 3.9, 4.0, 4.1, 4.2, 4.3, 4.4, 4.5, 4.6, 4.7, 4.8, 4.9, 5.0, 5.1, 5.2, 5.3, 5.4, 5.5, 5.6, 5.7, 5.8]

我建议您在模型中使用 ActiveRecord 实例 update 方法而不是原始 sql。

您最终可以添加一个实例方法，在其中您在 h_range 字段上执行 .step。

更新

在您通过 pastebin 共享代码之后，您应该能够在您的 Controller 中按如下方式更新值:

def update
  hr_params = params.require(:height_range).permit(:classification_id, :tree_id, :diameter, :range_min, :range_max)

  r_min = hr_params[:range_min].to_f
  r_max = hr_params[:range_max].to_f

  @hr = HeighRange.find(params[:id])
  hr_options = {
    classification_id: hr_params[:classification_id],
    tree_id: hr_params[:tree_id],
    diameter: hr_params[:diameter],
    h_range: (r_min..r_max)
  }

  @hr.update(hr_options)
  if @hr.valid?
    @hr.save!
    redirect_to admin_height_ranges_path, notice: 'Height range was successfully updated.
  else
    render action: :edit, params: { id: @hr.id }
  end
end