Rails ActiveRecord inserting/updating range type (Postgresql)

Question

I have a height_ranges table on my database, which stores numeric ranges.

migration file

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

I can update table via raw sql query,

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

But when it comes to ActiveRecord, it surprisely omits lower bound.

[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)>

another query,

[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

A semi AR query that works is,

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

As a last resort I can use the first (raw) and last (passing raw query to ActiveRecord method) but what is wrong with attribute setting approach?

My current implementation has those helper methods in the model:

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

In the controller, I trigger the update as follows:

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

mabe02 · Accepted Answer · 2017-10-10 08:21:33Z

1

PostgreSQL ranges should be fully supported by Rails.

I manage to reproduce your situation as follows:

Migration:

#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

Model:

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

The numrange has some challenges in terms of type support:

$ 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]

I would suggest you to prefer ActiveRecord instance update method than raw sql in your model.

You can eventually add an instance method where you perform .step on your h_range field.

Update

After the code you shared via pastebin, in your controller you should be able to update the values as follows:

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

edited Oct 10, 2017 at 8:21

answered Oct 9, 2017 at 15:34

mabe02

2,7642 gold badges23 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

marmeladze Over a year ago

i've ensapsulated all dirty work to a helper function, but it seems there are some issues with numranges. pastebin.com/hMTfmYQK

mabe02 Over a year ago

Did you try to update using Range data types (such as (2..3) )? The solution I posted it is working in the way I reproduce it. What have you put in your helper function? Can you update your question with that code and the error you receive?

marmeladze Over a year ago

this is my "not so rails way" solution -)) pastebin.com/nPYiHusg

marmeladze Over a year ago

btw, i don't recieve an error, activerecord just sets lower bound to zero.

Collectives™ on Stack Overflow

Rails ActiveRecord inserting/updating range type (Postgresql)

1 Answer 1

Update

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Update

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related