How to process the "AS" of SQL?

Questions / Help
1

I want to backup a record of Users table to Users_History table. Users_History has an additional column: backup_reason, here is the function:

  def backup(u_name, reason) do
    query = from v in Users,
                 select: %{user_name: v.user_name, age: v.age, role: v.role, address: v.address, tel: v.tel, email: v.email, backup_reason: ^reason, inserted_at: v.inserted_at, updated_at: v.updated_at},
                 where: v.user_name == ^u_name

    Repo.insert_all(UsersHistory,
      query,
      on_conflict: :replace_all,
      conflict_target: [:user_name])
  end

This function works, but the select clause seems long, is there an easy way to write it? For example, can achieve a similar effect to this SQL:

 select u.*, reason as backup_reason from users u where u.user_name = "John"

Thanks

2 
def backup(u_name, reason) do
    query = from v in Users,
                 select: v, # actually the default, when querying for a schema, therefore optional
                 select_merge: %{backup_reason: ^reason},
                 where: v.user_name == ^u_name

    Repo.insert_all(UsersHistory,
      query,
      on_conflict: :replace_all,
      conflict_target: [:user_name])
  end
3 Likes
3

Actually, I have tried this code, but I got the error message:

iex(1)> Friends.User.backup("Joey", "slashed")
** (ArgumentError) cannot generate a fields list for insert_all from the given source query
because it does not have a select clause that uses a map:

  #Ecto.Query<from u0 in Friends.User, where: u0.user_name == ^..., select: merge(u0, %{backup_reason: ^...})>

Please add a select clause that selects into a map, like this:

  from x in Source,
    ...,
    select: %{
      field_a: x.bar,
      field_b: x.foo
    }

All keys must exist in the schema that is being inserted into

    (ecto 3.11.1) lib/ecto/repo/schema.ex:126: Ecto.Repo.Schema.extract_header_and_fields/8
    (ecto 3.11.1) lib/ecto/repo/schema.ex:48: Ecto.Repo.Schema.do_insert_all/7
    iex:1: (file)
4

Didn’t know that the implicit select doesn’t work with insert_all, but I guess it makes sense to be explicit for that usecase (writes, not reads).

You can also do the following, which is at least a bit shorter than your original and differenciates quite well which fields are taken as stored in the db and which ones are added.

…,
select: map(v, [:user_name, :age, :role, :address, :tel, :email, :inserted_at, :updated_at]),
select_merge: …
1 Like