I want to have a "Customer
" Model with a normal primary key and another column to store a custom "Customer Number". In addition, I want the db to handle default Customer Numbers. I think, defining a sequence is the best way to do that. I use PostgreSQL. Have a look at my migration:
class CreateAccountsCustomers < ActiveRecord::Migration
def up
say "Creating sequenze for customer number starting at 1002"
execute 'CREATE SEQUENCE customer_no_seq START 1002;'
create_table :accounts_customers do |t|
t.string :type
t.integer :customer_no, :unique => true
t.integer :salutation, :limit => 1
t.string :cp_name_1
t.string :cp_name_2
t.string :cp_name_3
t.string :cp_name_4
t.string :name_first, :limit => 55
t.string :name_last, :limit => 55
t.timestamps
end
say "Adding NEXTVAL('customer_no_seq') to column cust_id"
execute "ALTER TABLE accounts_customers ALTER COLUMN customer_no SET DEFAULT NEXTVAL('customer_no_seq');"
end
def down
drop_table :accounts_customers
execute 'DROP SEQUENCE IF EXISTS customer_no_seq;'
end
end
If you know a better "rails-like" approach to add sequences, would be awesome to let me know.
Now, if I do something like
cust = Accounts::Customer.new
cust.save
the field customer_no
is not pre filled with the next value of the sequence (should be 1002).
Do you know a good way to integrate sequences? Or is there a good plugin? Cheers to all answers!
I have no suggestions for a more 'rails way' of handling custom sequences, but I can tell you why the customer_no field appears not to be being populated after a save.
When ActiveRecord saves a new record, the SQL statement will only return the ID of the new record, not all of its fields, you can see where this happens in the current rails source here https://github.com/rails/rails/blob/cf013a62686b5156336d57d57cb12e9e17b5d462/activerecord/lib/active_record/persistence.rb#L313
In order to see the value you will need to reload the object...
cust = Accounts::Customer.new
cust.save
cust.reload
If you always want to do this, consider adding an after_create hook in to your model class...
class Accounts::Customer < ActiveRecord::Base
after_create :reload
end
I believe that roboles answer is not correct.
I tried to implement this on my application (exactly the same env: RoR+PostgreSQL), and I found out that when save
is issued on RoR with the object having empty attributes, it tries to perform an INSERT on the database mentioning that all VALUES shall be set to NULL. The problem is the way PostgreSQL handles NULLs: in this case, the new row will be created but with all values empty, i.e. the DEFAULT will be ignored. If save
only wrote on the INSERT statement attributes filled on RoR, this would work fine.
In other words, and focusing only on the type
and customer_no
attribute mentioned above, this is the way PostgreSQL behaves:
SITUATION 1:
INSERT INTO accounts_customers (type, customer_no) VALUES (NULL, NULL);
(this is how Rails' save
works)
Result: a new row with empty type
and empty customer_no
SITUATION 2:
INSERT INTO accounts_customers (type) VALUES (NULL);
Result: a new row with empty type
and customer_no
filled with the sequence's NEXTVAL
I have a thread going on about this, check it out at:
Ruby on Rails+PostgreSQL: usage of custom sequences
I faced a similar problem, but I also put :null => false
on the field hopping that it will be auto-populated with nextval.
Well, in my case AR was still trying to insert NULL
if no attribute was supplied in the request, and this resulted in an exception for not-null constraint violation.
Here's my workaround. I just deleted this attribute key from @attributes
and @changed_attributes
and in this case postgres correctly put the expected sequence nextval.
I've put this in the model:
before_save do
if (@attributes["customer_no"].nil? || @attributes["customer_no"].to_i == 0)
@attributes.delete("customer_no")
@changed_attributes.delete("customer_no")
end
end
Rails 3.2 / Postgres 9.1
If you're using PostgreSQL, check out the gem I wrote, pg_sequencer:
https://github.com/code42/pg_sequencer
It provides a DSL for creating, dropping and altering sequences in ActiveRecord migrations.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With