Question: How to create a sequential unique id for a db field


How to create a sequential unique id for a db field

Answers 5
Added at 2017-01-05 07:01

I have the table Slug with the field url which is unique.

If I create @slug = Slug.url = "foo"

When I go to save, if Slug.url of "foo" already exists, I would like to then try for a Slug.url of "foo-1" if that also exists, try "foo-2" foo-3, foo-4, etc... until a value is found that doesn't exist and can be created in the db... What would be the right way to go about this in my rails model?



My latest code looks like this:

  def set_url
    self.url = self.title.parameterize
    # Ensure the url is available
    qUrl = self.url.split('-').shift
    slugs = Slug.where("url like '#{qUrl}%'")
    if slugs.exists?
      c = slugs.count + 1
      self.url = self.url + "-" + c.to_s

The problem with this code is the qUrl is picking up false positives, any time a title starts with the word "why" slugs are being found. Would love some help here to come up with something more reliable and elegant. Thanks

Answers to

How to create a sequential unique id for a db field

nr: #1 dodano: 2017-01-05 08:01

You may do something like this in a before_create(if you just want to assign this on create) or before_save(if this can be updated while create or update both) callback:

slug = 'foo-1'
while Slug.where(field: slug).exists?
  slug_array = slug.split('-')
  slug_array << (slug_array.pop.to_i + 1)
  slug = slug_array.join('-')

This will loop till the slug is found in database and will update the slug accordingly. But this believes that the number will always be in the end after a dash(-)

This may or may not be a right way to do it because this task can have many ways to perform it.


You can also do it like:

slug = 'foo'
last_slug = Slug.where("field like '#{slug}%'").last
if last_slug.present?
  slug_array = slug.split('-')
  slug_array << (slug_array.pop.to_i + 1)
  slug = slug_array.join('-')

You may use gsub or regex of any other string method to do the same.

nr: #2 dodano: 2017-01-05 10:01

I'd suggest using the friendly_id gem for this. If you look at the documentation in the slugged object, you'll see that it has a mechanism to handle uniqueness by appending a uuid to the slug name.

Alternatively, there are a number of other slug generating gems that might work better for your environment.

nr: #3 dodano: 2017-01-06 07:01

What I was suggesting is to use a before_save callback (I haven't tried the code, but answering conceptually):

Slug Model

before_save :check_and_generate_unique_slug


def check_and_generate_unique_slug
  is_unique = Slug.where(url: self.url).count > 0 ? false : true

  unless is_unique
    #if say previous url was foo-1, self.url.last.to_i gives 1
    new_url = "foo-#{self.url.last.to_i + 1}" #here it's foo-2
    self.url = new_url
    self.url = new_url

NOTE Even if initially url is "foo", "foo".last.to_i will give 0, and next url would be foo-#{0 + 1} = foo-1


A Better way

def check_and_generate_unique_slug
  urls = Slug.pluck(:url).uniq
  #=> ["foo", "foo-1", "foo-3", "foo-2"]

  latest_url_code ={|a| a.last.to_i}.uniq.sort.last
  #=>{|a| a.last.to_i}.uniq.sort  gives  [0, 1, 2, 3]
  #=> last will give 3

  self.url = "foo-#{latest_url_code + 1}"
nr: #4 dodano: 2017-01-10 08:01

Try this

before_save :generate_slug


def genareate_slug
  url =
  count = Slug.where("url LIKE '#{url}'").count
  self.url = count.positive? ? "#{url}-#{count + 1}" : url
nr: #5 dodano: 2017-01-10 17:01

Try this:

class Slug < ActiveRecord::Base     

  before_validation :set_slug, :on => :create


  def set_slug
    tmp = "#{ title.downcase.gsub(/[^a-z0-9]+/i, '-') }"
    i = 0
    self.url = loop do
      tmp.concat("-#{ i }") if i > 0
      i += 1
      break tmp unless Slug.exists?(url: tmp)


try it in console.

tmp1 = Slug.create!(title: 'hello world')
tmp1.url #=> "hello-world"

tmp2 = Slug.create!(title: 'Hello World')
tmp2.url #=> "hello-world-1"
Source Show
◀ Wstecz