代码之家 › 专栏 › 技术社区 › marmeladze

Rails ActiveRecord插入/更新范围类型(Postgresql)

activerecord postgresql ruby ruby-on-rails

marmeladze · 技术社区 · 8 年前

我有一个 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

在控制器中,我触发更新,如下所示:

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

1 回复 | 直到 8 年前

mabe02 8 年前

PostgreSQL范围应为 fully supported

我设法将您的情况再现如下:

迁移:

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

.step 在您的 h_range 领域

使现代化

在您通过共享的代码之后 糊状纸盒 ,在控制器中,您应该能够更新以下值:

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