I have an list with maps like this:
[
%{"AM" => "01", "D9" => ""},
%{"AM" => "02", "D8" => ""}
]
And in database an table with columns AM01 and AM02.
I want to save this maps like this:
____________________________________________________________
|___________________________Table X__________________________|
|_________Column_AM01__________|_________Column_AM02_________|
| %{"AM" => "01", "D9" => ""} | %{"AM" => "02", "D8" => ""} |
|______________________________|_____________________________|
How can I save this maps in your respective table?
Here's the relevant code full file(s) below:
def insert_list([]), do: :ok
def insert_list( [ %{"AM" => value}=whole_map | tail ] ) do
column_name = "AM#{value}"
changeset = Foo.Item.changeset(
%Foo.Item{},
%{column_name => whole_map}
)
IO.inspect changeset
{:ok, _item} = @repo.insert(changeset)
insert_list(tail)
end
def go do
maps = [
%{"AM" => "01", "D9" => ""},
%{"AM" => "02", "D8" => ""}
]
insert_list(maps)
end
In iex:
~/phoenix_apps/foo$ iex -S mix
Erlang/OTP 20 [erts-9.3] [source] [64-bit] [smp:4:4] [ds:4:4:10] [async-threads:10] [hipe] [kernel-poll:false]
Compiling 1 file (.ex)
Interactive Elixir (1.8.2) - press Ctrl+C to exit (type h() ENTER for help)
iex(1)> Foo.go
#Ecto.Changeset<
action: nil,
changes: %{AM01: %{"AM" => "01", "D9" => ""}},
errors: [],
data: #Foo.Item<>,
valid?: true
>
#Ecto.Changeset<
action: nil,
changes: %{AM02: %{"AM" => "02", "D8" => ""}},
errors: [],
data: #Foo.Item<>,
valid?: true
>
[debug] QUERY OK db=11.9ms decode=2.4ms queue=2.7ms
INSERT INTO "items" ("AM01","inserted_at","updated_at") VALUES ($1,$2,$3) RETURNING "id" [%{"AM" => "01", "D9" => ""}, ~N[2019-07-06 17:16:23], ~N[2019-07-06 17:16:23]]
[debug] QUERY OK db=2.7ms queue=0.4ms
INSERT INTO "items" ("AM02","inserted_at","updated_at") VALUES ($1,$2,$3) RETURNING "id" [%{"AM" => "02", "D8" => ""}, ~N[2019-07-06 17:16:23], ~N[2019-07-06 17:16:23]]
:ok
iex(2)> Foo.list_items
[debug] QUERY OK source="items" db=6.8ms queue=0.5ms
SELECT i0."id", i0."title", i0."AM01", i0."AM02", i0."inserted_at", i0."updated_at" FROM "items" AS i0 []
[
%Foo.Item{
AM01: %{"AM" => "01", "D9" => ""},
AM02: nil,
__meta__: #Ecto.Schema.Metadata<:loaded, "items">,
id: 1,
inserted_at: ~N[2019-07-06 17:16:23],
title: nil,
updated_at: ~N[2019-07-06 17:16:23]
},
%Foo.Item{
AM01: nil,
AM02: %{"AM" => "02", "D8" => ""},
__meta__: #Ecto.Schema.Metadata<:loaded, "items">,
id: 2,
inserted_at: ~N[2019-07-06 17:16:23],
title: nil,
updated_at: ~N[2019-07-06 17:16:23]
}
]
Note that if your list looks like this:
[
%{"AM" => "01", "D9" => ""},
%{"AM" => "03", "D8" => ""},
%{"AM" => "02", "D8" => ""}
]
...then with the changeset validations specified below, you will still get an insert for the 2nd map, and the columns AM01 and AM02 will be nil. You can prevent the second map from triggering an insert like this:
if changeset.changes == %{} do
insert_list(tail)
else
case @repo.insert(changeset) do
{:ok, _item} -> :ok
{:error, error} -> IO.puts "Insert error: #{error}"
end
insert_list(tail)
end
because the changeset for the second map will look like this:
#Ecto.Changeset<action: nil,
changes: %{},
errors: [],
data: #Foo.Item<>, valid?: true>
A changeset for the first map looks like this:
#Ecto.Changeset<action: nil,
changes: %{AM01: %{"AM" => "01", "D9" => ""}},
errors: [],
data: #Foo.Item<>, valid?: true>
Or, you can add additional validations depending on your requirements. For instance, if a value for one of the AM** columns must be present, here's how you can prevent an insert if both keys are missing in the attrs map (the second argument to the changeset() function):
def changeset(%Foo.Item{}=item, attrs \\ %{}) do
item
|> cast(attrs, [:title, :AM01, :AM02])
|> validate_required_either_AM_column()
# |> validate_required([:title])
# |> validate_length(:title, min: 3)
end
defp validate_required_either_AM_column(%Ecto.Changeset{} = changeset) do
IO.puts "inside validate_required: changeset=#{inspect changeset}"
am01 = get_change(changeset, :AM01)
am02 = get_change(changeset, :AM02)
case am01 || am02 do
nil -> # The || operator will return nil when both am01 and am02 are nil
add_error(changeset, :AM01, "either this column or :AM02 must be present")
|> add_error(:AM02, "either this column or :AM01 must be present")
_ ->
changeset
end
The second map will produce this error output:
***Insert error: #Ecto.Changeset<action: :insert,
changes: %{},
errors: [AM02: {"either this column or :AM01 must be present", []},
AM01: {"either this column or :AM02 must be present", []}],
data: #Foo.Item<>, valid?: false>
foo/lib/foo.ex:
defmodule Foo do
alias Foo.Item
@repo Foo.Repo
def list_items do
@repo.all(Item)
end
def insert_item(attrs) do
Foo.Item
|> struct(attrs)
|> @repo.insert()
end
def delete_item(%Foo.Item{} = item) do
@repo.delete(item)
end
def insert_list([]), do: :ok
def insert_list( [ %{"AM" => value}=whole_map | tail ] ) do
column_name = "AM#{value}"
changeset = Foo.Item.changeset(
%Foo.Item{},
%{column_name => whole_map}
)
IO.inspect changeset
{:ok, _item} = @repo.insert(changeset)
insert_list(tail)
end
def go do
maps = [
%{"AM" => "01", "D9" => ""},
%{"AM" => "02", "D8" => ""}
]
insert_list(maps)
end
end
foo/lib/foo/repo.ex:
defmodule Foo.Repo do
use Ecto.Repo,
otp_app: :foo,
adapter: Ecto.Adapters.Postgres
end
foo/lib/foo/item.ex:
defmodule Foo.Item do
use Ecto.Schema
import Ecto.Changeset
schema "items" do
field :title, :string
field :AM01, :map
field :AM02, :map
timestamps()
end
def changeset(%Foo.Item{}=item, attrs \\ %{}) do
item
|> cast(attrs, [:title, :AM01, :AM02])
# |> validate_required([:title])
# |> validate_length(:title, min: 3)
# ...
# ...
end
end
foo/priv/repo/migrations/...._create_items.exs:
defmodule Foo.Repo.Migrations.CreateItems do
use Ecto.Migration
def change do
create table("items") do
add :title, :string
add :AM01, :map
add :AM02, :map
timestamps()
end
end
end