How to update field value dynamically in Ecto migration?

Question

I have a Users table like:

     email     | username
---------------+----------
 [email protected]   |
 [email protected]   |
 [email protected] |

and I want to update username field by email field, just slice the email before @.

     email     | username
---------------+----------
 [email protected]   | 123
 [email protected]   | 123
 [email protected] | haha

I have try to use the following migration:

defmodule MyApp.Repo.Migrations.AddDefaultUsernameForUsers do
  use Ecto.Migration
  import Ecto.Query

  def up do
      from(u in MyApp.User, update: [set: [username: String.split(u.email, "@") |> List.first ]])
        |> MyApp.Repo.update_all([])
  end

  def down do
      MyApp.Repo.update_all(MyApp.User, set: [username: nil])
  end
end

But when runing the migration, I got the following error:

$ mix ecto.migrate
** (Ecto.Query.CompileError) `List.first(String.split(u.email(), "@"))` is not a valid query expression

How can I solve this?

Answer 1

@Justin Wood has explained why you cannot use Elixir functions in update queries so I won't repeat that. In PostgreSQL, you can extract the text before @ using the substring function with a regular expression, which will work with update queries. This will be way faster than loading the records and then updating them one by one, but will not work with other database engines without tweaking the SQL fragment:

from(u in MyApp.User,
  update: [set: [username: fragment("substring(? from '^(.*?)@')", u.email)]])
|> MyApp.Repo.update_all([])

postgres=# select substring('[email protected]' from '^(.*?)@');
 substring
-----------
 123
(1 row)

Answer 2

You are going to want to make two separate queries. One query to grab the data, do whatever changes you want, then a second query to update that data. Something along the lines of

Repo.all(MyApp.User)
|> Enum.map(fn u ->
  username = 
    u.email
    |> String.split("@")
    |> List.first()

  Ecto.Changeset.cast(u, %{username: username})
end)
|> Repo.update_all()

There are a couple things going on as to why you cannot do what you were attempting to do.

When you want to use an Elixir function or value within an Ecto query, you would normally have to use the pin operator (^). So if you wanted to query for a specific ID, you could use from(u in MyApp.User, where: u.id == ^12). So your reaction may be to try and use ^List.first(String.split(u.email, "@")). However, this will not work because...

The u in from(u in MyApp.User) is the record as is in the database. You do not have access to it in your Elixir code. It may be possible to use a fragment/1 for what you are trying to do, but you cannot manipulate the value with regular Elixir functions until you actually pull it out of the database using something like my example above.