0

I have an Orders table in a SQL database (PostgreSQL if it matters, and Rails 4.04) and I want the column "number" in the orders table to kind of shadow the id column. In other words I want an automatic sequential field but I want it to be in the form:

Order::PRODUCT_NU_PREFIX + ((Order::STARTING_PRODUCT_NO + order.id).to_s)

So that if I set

PRODUCT_NU_PREFIX = 'PE' and 
STARTING_PRODUCT_NO = '11681'

Then the first order I create will have a product number:

KB11682 and then 
KB11683

SHould I do this purely in PostgreSQL or is there a good way to do it in Rails? Keep in mind I'll need to know what the latest Order.id is when an order comes in because when I save that new record I want that field to get saved correctly.

mu is too short
  • 426,620
  • 70
  • 833
  • 800
asolberg
  • 6,638
  • 9
  • 33
  • 46

1 Answers1

1

You're looking at uuid's (Universally Unique Identifiers)

These basically allow you to assign a special id to a record, giving you an instant reference throughout your app. Such use cases for this include the likes of order numbers, API keys and message ID's

We actually implement what you're asking quite regularly:

#app/models/order.rb
Class Order < ActiveRecord::Base
    before_create :set_num

    private
    def set_num
        unless self.num
            loop do
                token =  SecureRandom.hex(10)
                break token unless self.class.exists?(num: token)
            end
        end
    end

end

orders
id | num | other | attrs | created_at | updated_at

Rails has a series of built-in UUID-generators:

SecureRandom.uuid #=> "1ca71cd6-08c4-4855-9381-2f41aeffe59c"
SecureRandom.hex(10) # => "52750b30ffbc7de3b362"
SecureRandom.base64(10) # => "EcmTPZwWRAozdA=="
Community
  • 1
  • 1
Richard Peck
  • 76,116
  • 9
  • 93
  • 147
  • Can it also be used to generate a sequence of `employee_codes` like `RE1000`,`RE1001`,`RE1002`.. etc? – Pavan May 19 '14 at 08:17
  • Hi Pavan - I hope you're doing well - see you a lot on here! This wouldn't be good as an incremental storage system; by nature `uuid` is meant to be completely unique. What you're looking at would be best applied at the DB-level with `auto-increment` – Richard Peck May 19 '14 at 08:28
  • Having [looked this up](http://stackoverflow.com/questions/17893988/how-to-make-mysql-table-primary-key-auto-increment-with-some-suffix), it seems you're only able to use `INT` columns for `auto-increment`. I could write a simple algorithm for your `employee_codes` if you like; but it would need more than just `auto-increment I think`! – Richard Peck May 19 '14 at 08:29
  • Thanks! Right now we are doing like that! Just eager to know whether it work in my case.Anyways Your answer is great.I learned a new one today :) – Pavan May 19 '14 at 08:32
  • No problem - hope it helps you. In terms of your `employee_codes` thing, I would recommend asking a question about it, but in a general sense, you'll probably want to use a `before_create` callback like I've recommended here, with a method to determine the employee number, and then populate the attribute with it – Richard Peck May 19 '14 at 08:35
  • Hmm,surely will think about asking as a question in SO! Thanks again mate! – Pavan May 19 '14 at 08:37