ElixirでMySQLを使う #3（Ecto/CRUD・クエリ作成）

# 概要

1. [ElixirでMySQLを使う #1（Ecto/MyXQLセットアップ〜DB作成）](https://qiita.com/im_miolab/items/d27a1231b80e5872117c)
1. [ElixirでMySQLを使う #2（Ecto/マイグレーション〜スキーマ作成〜カラム修正）](https://qiita.com/im_miolab/items/5a8d294e414c1c29d33e)

の続きです。

前回、`Ecto`マイグレーション〜スキーマ作成を行って、__Elixir__側からデータを__MySQL__に渡しました。
本記事では、ElixirでMySQLの__CRUD__操作（データ作成・読み出し・更新・削除）を実行していきます。

- 本記事シリーズにおける実行環境等については、シリーズ [#1](https://qiita.com/im_miolab/items/d27a1231b80e5872117c) の記事をご参照ください。

##（事前準備）データベース再作成

事前準備として、前回つくったデータベースを削除して、あたらしく作り直します。

  ```terminal:terminal
  $ mix ecto.drop
  Compiling 1 file (.ex)
  The database for Friendsmysql.Repo has been dropped
  ```

  ```terminal:terminal
  $ mix ecto.create
  The database for Friendsmysql.Repo has been created
  ```

  ```terminal:terminal
  $ mix ecto.migrate

  13:36:23.224 [info]  == Running 20200521003013 Friendsmysql.Repo.Migrations.CreatePeople.change/0 forward

  13:36:23.226 [info]  create table people

  13:36:23.241 [info]  == Migrated 20200521003013 in 0.0s

  13:36:23.265 [info]  == Running 20200522004701 Friendsmysql.Repo.Migrations.ApplyNotnullToNames.change/0 forward

  13:36:23.265 [info]  alter table people

  13:36:23.278 [info]  == Migrated 20200522004701 in 0.0s
  ```

- 結果確認（MySQL）

  ```mysql:terminal(MySQL)
  mysql> desc people;
  +------------+---------------------+------+-----+---------+----------------+
  | Field      | Type                | Null | Key | Default | Extra          |
  +------------+---------------------+------+-----+---------+----------------+
  | id         | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
  | first_name | varchar(255)        | NO   |     | NULL    |                |
  | last_name  | varchar(255)        | NO   |     | NULL    |                |
  | age        | int(11)             | YES  |     | NULL    |                |
  +------------+---------------------+------+-----+---------+----------------+
  4 rows in set (0.00 sec)

  mysql> select * from people;
  Empty set (0.00 sec)
  ```

  作りなおしたデータベースと、からっぽの`people`テーブルが準備できました。

# CRUD

それでは、__IEx__（`$ iex -S mix`）でCRUD操作を実行していきます。

## Create（データ作成）

- `Repo.insert()`

はじめに、__データの作成__（Create）です。

以下の計4レコードを用意します。

```elixir:terminal
iex(1)> people = [
...(1)> %Friendsmysql.Person{first_name: "Ryan", last_name: "Bigg", age: 28},
...(1)> %Friendsmysql.Person{first_name: "John", last_name: "Smith", age: 27},
...(1)> %Friendsmysql.Person{first_name: "Jane", last_name: "Smith", age: 26},
...(1)> %Friendsmysql.Person{first_name: "im", last_name: "miolab", age: 28},
...(1)> ]
[
  %Friendsmysql.Person{
    __meta__: #Ecto.Schema.Metadata<:built, "people">,
    age: 28,
    first_name: "Ryan",
    id: nil,
    last_name: "Bigg"
  },
  %Friendsmysql.Person{
    __meta__: #Ecto.Schema.Metadata<:built, "people">,
    age: 27,
    first_name: "John",
    id: nil,
    last_name: "Smith"
  },
  %Friendsmysql.Person{
    __meta__: #Ecto.Schema.Metadata<:built, "people">,
    age: 26,
    first_name: "Jane",
    id: nil,
    last_name: "Smith"
  },
  %Friendsmysql.Person{
    __meta__: #Ecto.Schema.Metadata<:built, "people">,
    age: 28,
    first_name: "im",
    id: nil,
    last_name: "miolab"
  }
]
```

`Enum.each`で`insert()`していきます。

```elixir:terminal
iex(2)> Enum.each(people, fn(p) -> Friendsmysql.Repo.insert(p) end)

13:45:40.520 [debug] QUERY OK db=5.9ms decode=1.3ms queue=7.0ms idle=1457.6ms
INSERT INTO `people` (`age`,`first_name`,`last_name`) VALUES (?,?,?) [28, "Ryan", "Bigg"]
 
13:45:40.528 [debug] QUERY OK db=1.3ms queue=4.8ms idle=1475.8ms
INSERT INTO `people` (`age`,`first_name`,`last_name`) VALUES (?,?,?) [27, "John", "Smith"]
 
13:45:40.531 [debug] QUERY OK db=0.4ms queue=1.8ms idle=1482.1ms
INSERT INTO `people` (`age`,`first_name`,`last_name`) VALUES (?,?,?) [26, "Jane", "Smith"]
:ok

13:45:40.534 [debug] QUERY OK db=2.3ms queue=1.0ms idle=1484.5ms
INSERT INTO `people` (`age`,`first_name`,`last_name`) VALUES (?,?,?) [28, "im", "miolab"]
```

`people`テーブルに`INSERT`できました。

ちなみに、前回の記事みたいに1レコードだけをシンプルに`insert()`実行するには、以下の通りです。

```elixir:terminal
iex(3)> iiii = %Friendsmysql.Person{first_name: "iiii", last_name: "mimimi", age: 18}
%Friendsmysql.Person{
  __meta__: #Ecto.Schema.Metadata<:built, "people">,
  age: 18,
  first_name: "iiii",
  id: nil,
  last_name: "mimimi"
}

iex(4)> Friendsmysql.Repo.insert(iiii)

13:46:54.648 [debug] QUERY OK db=2.5ms queue=3.0ms idle=1575.2ms
INSERT INTO `people` (`age`,`first_name`,`last_name`) VALUES (?,?,?) [18, "iiii", "mimimi"]
{:ok,
 %Friendsmysql.Person{
   __meta__: #Ecto.Schema.Metadata<:loaded, "people">,
   age: 18,
   first_name: "iiii",
   id: 5,
   last_name: "mimimi"
 }}
```

- 結果確認（MySQL）

  ```mysql:terminal(MySQL)
  mysql> select * from people;
  +----+------------+-----------+------+
  | id | first_name | last_name | age  |
  +----+------------+-----------+------+
  |  1 | Ryan       | Bigg      |   28 |
  |  2 | John       | Smith     |   27 |
  |  3 | Jane       | Smith     |   26 |
  |  4 | im         | miolab    |   28 |
  |  5 | iiii       | mimimi    |   18 |
  +----+------------+-----------+------+
  5 rows in set (0.02 sec)
  ```

`Create`操作ができました。

## Read（データ読み出し）

つづいて、登録__データの読み出し・抽出__（Read）を実行していきます。

### 全レコード 取得

- `Repo.all()`

  ```elixir:terminal
  iex(5)> Friendsmysql.Person |> Friendsmysql.Repo.all

  13:51:56.586 [debug] QUERY OK source="people" db=0.3ms queue=5.9ms idle=1513.0ms
  SELECT p0.`id`, p0.`first_name`, p0.`last_name`, p0.`age` FROM `people` AS p0 []
  [
    %Friendsmysql.Person{
      __meta__: #Ecto.Schema.Metadata<:loaded, "people">,
      age: 28,
      first_name: "Ryan",
      id: 1,
      last_name: "Bigg"
    },
    %Friendsmysql.Person{
      __meta__: #Ecto.Schema.Metadata<:loaded, "people">,
      age: 27,
      first_name: "John",
      id: 2,
      last_name: "Smith"
    },
    %Friendsmysql.Person{
      __meta__: #Ecto.Schema.Metadata<:loaded, "people">,
      age: 26,
      first_name: "Jane",
      id: 3,
      last_name: "Smith"
    },
    %Friendsmysql.Person{
      __meta__: #Ecto.Schema.Metadata<:loaded, "people">,
      age: 28,
      first_name: "im",
      id: 4,
      last_name: "miolab"
    },
    %Friendsmysql.Person{
      __meta__: #Ecto.Schema.Metadata<:loaded, "people">,
      age: 18,
      first_name: "iiii",
      id: 5,
      last_name: "mimimi"
    }
  ]
  ```

  レコード全件を読み出しました。
  （`SELECT * FROM table`相当）

### `id`を指定してレコード取得

- `Repo.get_by()`

  ```elixir:terminal
  iex(6)> Friendsmysql.Person |> Friendsmysql.Repo.get(3)

  13:53:31.176 [debug] QUERY OK source="people" db=6.2ms queue=0.3ms idle=1102.8ms
  SELECT p0.`id`, p0.`first_name`, p0.`last_name`, p0.`age` FROM `people` AS p0 WHERE (p0.`id` = ?) [3]
  %Friendsmysql.Person{
    __meta__: #Ecto.Schema.Metadata<:loaded, "people">,
    age: 26,
    first_name: "Jane",
    id: 3,
    last_name: "Smith"
  }
  ```

  `id: 3`に該当するレコードを抽出しました。

### カラム名とフィールドに基づいてレコードを取得

- `Repo.where()`

  ```elixir:terminal
  iex(7)> Friendsmysql.Person |> Friendsmysql.Repo.get_by(first_name: "im")

  13:54:58.257 [debug] QUERY OK source="people" db=1.8ms queue=2.0ms idle=1185.3ms
  SELECT p0.`id`, p0.`first_name`, p0.`last_name`, p0.`age` FROM `people` AS p0 WHERE (p0.`first_name` = ?) ["im"]
  %Friendsmysql.Person{
    __meta__: #Ecto.Schema.Metadata<:loaded, "people">,
    age: 28,
    first_name: "im",
    id: 4,
    last_name: "miolab"
  }

  iex(8)> Friendsmysql.Person |> Friendsmysql.Repo.get_by(age: 18)

  13:56:15.218 [debug] QUERY OK source="people" db=1.9ms queue=6.1ms idle=1269.7ms
  SELECT p0.`id`, p0.`first_name`, p0.`last_name`, p0.`age` FROM `people` AS p0 WHERE (p0.`age` = ?) [18]
  %Friendsmysql.Person{
    __meta__: #Ecto.Schema.Metadata<:loaded, "people">,
    age: 18,
    first_name: "iiii",
    id: 5,
    last_name: "mimimi"
  }
  ```

  - `first_name: im`に該当するレコードを1件
  - `age: 18`に該当するレコードを1件

それぞれ抽出しました。

### Ecto.Queryでフィルタリング抽出

もう少しつっこんだ条件抽出のクエリを書いてみます。

`require Ecto.Query`で事前準備をします。

  ```elixir:terminal
  iex(9)> require Ecto.Query
  Ecto.Query
  ```

#### カラム名とフィールドに基づいて重複レコードを取得

それでは、`Ecto.Query`でまず__重複するレコード__を抽出します。
具体例として、`last_name`が`Smith`さんが2人いるので、こちらのお二方を抽出することとします。

なお、先ほどの`get_by()`ではレコード1件の抽出しかできないため、以下のように`Ecto.Query`でのオペレーションが必要となります。

  ```elixir:terminal
  iex(10)> Friendsmysql.Person |> Ecto.Query.where(last_name: "Smith") |> Friendsmysql.Repo.all

  14:00:10.899 [debug] QUERY OK source="people" db=0.2ms queue=5.0ms idle=1827.0ms
  SELECT p0.`id`, p0.`first_name`, p0.`last_name`, p0.`age` FROM `people` AS p0 WHERE (p0.`last_name` = 'Smith') []
  [
    %Friendsmysql.Person{
      __meta__: #Ecto.Schema.Metadata<:loaded, "people">,
      age: 27,
      first_name: "John",
      id: 2,
      last_name: "Smith"
    },
    %Friendsmysql.Person{
      __meta__: #Ecto.Schema.Metadata<:loaded, "people">,
      age: 26,
      first_name: "Jane",
      id: 3,
      last_name: "Smith"
    }
  ]
  ```

もしくは、

  ```elixir:terminal
  iex(11)> Ecto.Query.from(p in Friendsmysql.Person, where: p.last_name == "Smith") |> Friendsmysql.Repo.all

  14:00:57.013 [debug] QUERY OK source="people" db=5.5ms idle=1940.3ms
  SELECT p0.`id`, p0.`first_name`, p0.`last_name`, p0.`age` FROM `people` AS p0 WHERE (p0.`last_name` = 'Smith') []
  [
    %Friendsmysql.Person{
      __meta__: #Ecto.Schema.Metadata<:loaded, "people">,
      age: 27,
      first_name: "John",
      id: 2,
      last_name: "Smith"
    },
    %Friendsmysql.Person{
      __meta__: #Ecto.Schema.Metadata<:loaded, "people">,
      age: 26,
      first_name: "Jane",
      id: 3,
      last_name: "Smith"
    }
  ]
  ```

  2人の_Smithさん_を抽出しました。

#### 絞り込み抽出

__複数の抽出条件を組み合わせ__実行します。
クエリをパイプライン演算子で繋いでいきます。

`last_name`条件抽出クエリに、`first_name`条件抽出クエリをつないで、`AND`検索っぽくします。

```elixir:terminal
iex(12)> Friendsmysql.Person |> Ecto.Query.where(last_name: "Smith") |> Ecto.Query.where(age: 27) |> Friendsmysql.Repo.all

14:02:44.259 [debug] QUERY OK source="people" db=0.6ms queue=3.9ms idle=1184.8ms
SELECT p0.`id`, p0.`first_name`, p0.`last_name`, p0.`age` FROM `people` AS p0 WHERE (p0.`last_name` = 'Smith') AND (p0.`age` = 27) []
[
  %Friendsmysql.Person{
    __meta__: #Ecto.Schema.Metadata<:loaded, "people">,
    age: 27,
    first_name: "John",
    id: 2,
    last_name: "Smith"
  }
]
```

`last_name`が_Smith_で、かつ`age`が_27_の人を抽出しました。

#### その他、抽出いろいろ

適当にいくつか例を実行します。
参考のため、それぞれに、相当するSQLを添えています。

- 事前準備

  ```elixir:terminal
  iex(13)> import Ecto.Query, only: [from: 2]
  Ecto.Query
  ```

- __全レコードの`age`平均値を抽出__  
（`SELECT AVG(age) FROM people`）

  ```elixir:terminal
  iex(14)> query_avg = from p in "people",
  ...(14)> select: avg(p.age)
  #Ecto.Query<from p0 in "people", select: avg(p0.age)>

  iex(15)> Friendsmysql.Repo.all(query_avg)

  14:14:19.725 [debug] QUERY OK source="people" db=18.2ms queue=1.8ms idle=1914.1ms
  SELECT avg(p0.`age`) FROM `people` AS p0 []
  [#Decimal<25.4000>]
  ```

- __`age > 20`の人の`first_name`を抽出__  
（`SELECT first_name FROM people WHERE age > 20`）

  ```elixir:terminal
  iex(16)> query_over_twenty = from p in "people",
  ...(16)> where: p.age > 20,
  ...(16)> select: p.first_name
  #Ecto.Query<from p0 in "people", where: p0.age > 20, select: p0.first_name>

  iex(17)> Friendsmysql.Repo.all(query_over_twenty)

  14:17:21.281 [debug] QUERY OK source="people" db=1.1ms queue=3.6ms idle=1491.4ms
  SELECT p0.`first_name` FROM `people` AS p0 WHERE (p0.`age` > 20) []
  ["Ryan", "John", "Jane", "im"]
  ```

- __`age > 26`の人を`age降順`で並びかえて抽出__  
（`SELECT id, first_name, age FROM people WHERE age > 26 ORDER BY age DESC`）

  ```elixir:terminal
  iex(18)> query_over_twentysix = from p in "people",
  ...(18)> where: p.age > 26,
  ...(18)> order_by: [desc: :age],
  ...(18)> select: [:id, :first_name, :age]
  #Ecto.Query<from p0 in "people", where: p0.age > 26, order_by: [desc: p0.age],
  select: [:id, :first_name, :age]>

  iex(19)> Friendsmysql.Repo.all(query_over_twentysix)

  14:19:08.225 [debug] QUERY OK source="people" db=4.1ms queue=3.9ms idle=1431.1ms
  SELECT p0.`id`, p0.`first_name`, p0.`age` FROM `people` AS p0 WHERE (p0.`age` > 26) ORDER BY p0.`age` DESC []
  [
    %{age: 28, first_name: "Ryan", id: 1},
    %{age: 28, first_name: "im", id: 4},
    %{age: 27, first_name: "John", id: 2}
  ]
  ```

ほんの一例ですが、いずれも問題なく抽出実行できました。

この他にも、以下__公式リファレンス__に`Ecto.Query`の抽出もろもろについて詳細がありますので、ご覧いただき試してみられると良いかとおもいます。

  - Hexdocs
    - [Ecto.Query](https://hexdocs.pm/ecto/Ecto.Query.html)
    - [Ecto.Query.API](https://hexdocs.pm/ecto/Ecto.Query.API.html)

### （補足）`Ecto.Query`内での変数使用の際は`^`が必要

変数をクエリ内で展開する際には、`^`（ピン演算子）が必要になります。

  ```elixir:terminal
  iex(20)> first_name_im = "im"
  "im"

  iex(21)> Friendsmysql.Person |> Ecto.Query.where(first_name: ^first_name_im) |> Friendsmysql.Repo.all

  14:24:47.240 [debug] QUERY OK source="people" db=2.0ms idle=1170.0ms
  SELECT p0.`id`, p0.`first_name`, p0.`last_name`, p0.`age` FROM `people` AS p0 WHERE (p0.`first_name` = ?) ["im"]
  [
    %Friendsmysql.Person{
      __meta__: #Ecto.Schema.Metadata<:loaded, "people">,
      age: 28,
      first_name: "im",
      id: 4,
      last_name: "miolab"
    }
  ]
  ```

  `first_name_im`で束縛した文字列`im`を`Ecto.Query`で展開する際、適用変数を`^first_name_im`の形にします。

## Update（データ更新）

`CRUD`のつづきで、次は__データの更新__を実行します。

- `Repo.update()`

  ```elixir:terminal
  iex(1)> require Ecto.Query
  Ecto.Query

  iex(2)> ryan = Friendsmysql.Person |> Ecto.Query.where(first_name: "Ryan") |> Friendsmysql.Repo.one

  14:28:40.395 [debug] QUERY OK source="people" db=0.3ms decode=1.0ms queue=2.7ms idle=1582.4ms
  SELECT p0.`id`, p0.`first_name`, p0.`last_name`, p0.`age` FROM `people` AS p0 WHERE (p0.`first_name` = 'Ryan') []
  %Friendsmysql.Person{
    __meta__: #Ecto.Schema.Metadata<:loaded, "people">,
    age: 28,
    first_name: "Ryan",
    id: 1,
    last_name: "Bigg"
  }
  ```

  上記で準備した_Ryanさん_のデータ`age: 28`を、`29`に更新してみます。

  ```elixir:terminal
  iex(3)> change_age_twentynine = Friendsmysql.Person.changeset(ryan, %{age: 29})
  #Ecto.Changeset<
    action: nil,
    changes: %{age: 29},
    errors: [],
    data: #Friendsmysql.Person<>,
    valid?: true
  >

  iex(4)> Friendsmysql.Repo.update(change_age_twentynine)

  14:31:55.745 [debug] QUERY OK db=7.2ms queue=4.1ms idle=1929.9ms
  UPDATE `people` SET `age` = ? WHERE `id` = ? [29, 1]
  {:ok,
  %Friendsmysql.Person{
    __meta__: #Ecto.Schema.Metadata<:loaded, "people">,
    age: 29,
    first_name: "Ryan",
    id: 1,
    last_name: "Bigg"
  }}
  ```

  結果を確認します。

  ```elixir:terminal
  iex(5)> Friendsmysql.Person |> Friendsmysql.Repo.get_by(first_name: "Ryan")

  14:32:16.250 [debug] QUERY OK source="people" db=0.5ms queue=0.8ms idle=1442.8ms
  SELECT p0.`id`, p0.`first_name`, p0.`last_name`, p0.`age` FROM `people` AS p0 WHERE (p0.`first_name` = ?) ["Ryan"]
  %Friendsmysql.Person{
    __meta__: #Ecto.Schema.Metadata<:loaded, "people">,
    age: 29,
    first_name: "Ryan",
    id: 1,
    last_name: "Bigg"
  }
  ```

  `age: 29`に更新がされています。

## Delete（データ削除）

`CRUD`の最後に、__データの削除__を実行します。

- `Repo.delete()`

先ほどの`IEx`で実行した`Update`のつづきに、コードを書いていきます。
_Ryanさん_ レコードを、ごめんなさいですが削除します。

  ```elixir:terminal
  iex(6)> Friendsmysql.Repo.delete(ryan)

  14:35:11.201 [debug] QUERY OK db=4.8ms queue=4.8ms idle=1386.5ms
  DELETE FROM `people` WHERE `id` = ? [1]
  {:ok,
  %Friendsmysql.Person{
    __meta__: #Ecto.Schema.Metadata<:deleted, "people">,
    age: 28,
    first_name: "Ryan",
    id: 1,
    last_name: "Bigg"
  }}
  ```

  削除できているか全件抽出して確認します。

  ```elixir:terminal
  iex(7)> Friendsmysql.Person |> Friendsmysql.Repo.all

  14:35:52.982 [debug] QUERY OK source="people" db=0.4ms queue=2.0ms idle=173.7ms
  SELECT p0.`id`, p0.`first_name`, p0.`last_name`, p0.`age` FROM `people` AS p0 []
  [
    %Friendsmysql.Person{
      __meta__: #Ecto.Schema.Metadata<:loaded, "people">,
      age: 27,
      first_name: "John",
      id: 2,
      last_name: "Smith"
    },
    %Friendsmysql.Person{
      __meta__: #Ecto.Schema.Metadata<:loaded, "people">,
      age: 26,
      first_name: "Jane",
      id: 3,
      last_name: "Smith"
    },
    %Friendsmysql.Person{
      __meta__: #Ecto.Schema.Metadata<:loaded, "people">,
      age: 28,
      first_name: "im",
      id: 4,
      last_name: "miolab"
    },
    %Friendsmysql.Person{
      __meta__: #Ecto.Schema.Metadata<:loaded, "people">,
      age: 18,
      first_name: "iiii",
      id: 5,
      last_name: "mimimi"
    }
  ]
  ```

  _Ryanさん_のレコードが削除されています。

# 終わり

`IEx`でElixir（Ecto）によるMySQLの__CRUD__操作（データ作成・読み出し・更新・削除）を実行しました。

- Ectoはドキュメントも詳しく、パイプラインでガシガシ書けて良い感じなので、個人的にも色々もっと調べてみようと思ってます。
- もしかしたら続きを何かしら書くかもしれません。