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)