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

Question

How to create a sequential unique id for a db field

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

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?

Thanks

UPDATE:

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
    end
  end

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
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('-')
end

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.

Update:

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('-')
end

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

private

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.check_and_generate_unique_slug
  else
    self.url = new_url
  end
end

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

EDIT

A Better way

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

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

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

Try this

before_save :generate_slug

private

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

Try this:

class Slug < ActiveRecord::Base     

  before_validation :set_slug, :on => :create

  protected 

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

end

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