Retry insertion with auto_incremented value when on_conflict occurs?

Questions / Help
1

I am trying to making a feature to autogenerate username based on user’s fullname,
but want to avoid duplication, by incrementing a number automatically.

for example, if there are two users named “Barack Obama”
my expected results are: “barackobama.1”, “barackobama.2”

I’ve achieved this by searching users ahead of every insertion.
but i still wonder, is there any way of doing this on changeset or database level.

is there anything i can do with unique_constraint? or on_conflict option?

  def create_user(attrs \\ %{}) do
    new_attrs = autogen_username(attrs)

    %User{}
    |> User.changeset(new_attrs)
    |> Repo.insert()
  end

  defp autogen_username(attrs) do
    username = attrs |> Map.get(:fullname) |> Slug.slugify(separator: "")
    matching = "#{username}.%"

    existing_user =
      User
      |> where([u], ilike(u.username, ^matching))
      |> order_by([u], desc: u.username)
      |> limit(1)
      |> Repo.one()

    count =
      case existing_user do
        nil ->
          1
        user ->
          count =
            user
            |> Map.get(:username)
            |> String.split(".")
            |> List.last()
            |> String.to_integer()

          count + 1
      end

    Map.put(attrs, :username, "#{username}.#{count}")
  end
2

Just to be clear, because this is not completely clear from your question. You only want to update the user you are currently creating? Not the one that already exists?

If you only want to update this one it might be possible with an upsert query (https://hexdocs.pm/ecto/Ecto.Repo.html#c:insert/2-upserts) although I’m not sure you can add a count to the username in this query.

3

thanks for your reply.

to make my question clearer,

i don’t want to update existing one. (no need upsert)
i want to insert while keeping :username unique.

what i am trying to achieve is like below.

(1) insert user with username “obama”

id | username
------------
1  | obama

(2) insert another user with username “obama”
when conflict happens, resolve conflict by adding a number to username, and continue insertion.

id | username
------------
1  | obama
2  | obama2
4

Don’t do this!

Just tell your user that the username has been taken and that they shall try a different one.

If my usual nick is taken by someone else, I prefer to be known as NobbZ81 or NobbZ2k instead of NobbZ2, or whatever amount of users already exist there…

5 Likes
5

you could certainly use a subquery to get the max suffix and add 1

6

But isn’t this somewhat problematic?

Lets say someone already has an account Foo someone else has created Foo123 directly. The next user asking for Foo, what shall he be known as? Foo2? Foo124?

If someone comes and wants to register as Foo123, will he become Foo124 or Foo1232?

Just don’t do this.

1 Like
7

I totally agree on the part of just don’t do it. I was also doubting to post this in my original answer, but I didn’t for some reasons that might be wrong:

  1. It doesn’t answer the question about it it is possible and how it can be done.
  2. I hope the user who asks the question has a good use case.
  3. Maybe someone else will have a good use case for something like this search the forum and find just don’t do this. That’s not very helpful for the person.

On the other hand I also agree that explaining why it shouldn’t be don’t is good information as well

1 Like