Phoenix_5_DeeperEcto

更深入了解 Ecto

Generators

瞭解如何定義 table 的關聯，User 會選擇 video，video裡有多個 comments(annotations)，comments 是由 User 建立的。

可使用 generators 產生 skeleton: 包含 migration, controllers, templates

video 相關的欄位為

1. an associated User
2. A creation time for the video
3. A URL of the video location
4. A title
5. type of the video

使用 phoenix.gen.html Mix task

mix phoenix.gen.html Video videos user_id:references:users url:string title:string description:text

$ mix phoenix.gen.html Video videos user_id:references:users url:string title:string description:text
* creating web/controllers/video_controller.ex
* creating web/templates/video/edit.html.eex
* creating web/templates/video/form.html.eex
* creating web/templates/video/index.html.eex
* creating web/templates/video/new.html.eex
* creating web/templates/video/show.html.eex
* creating web/views/video_view.ex
* creating test/controllers/video_controller_test.exs
* creating web/models/video.ex
* creating test/models/video_test.exs
* creating priv/repo/migrations/20170905083531_create_video.exs

Add the resource to your browser scope in web/router.ex:

    resources "/videos", VideoController

Remember to update your repository by running migrations:

    $ mix ecto.migrate

執行以下這個指令，DB 就會產生 table: videos

mix ecto.migrate

執行後會得到

1. 定義 model 的 module name
2. model name
3. 每個欄位及 type information

---

如果要限制 /videos 只能讓已登入的使用者使用，前面已經寫過 authentication 的 functions

/web/controller/user_controller.ex

  defp authenticate(conn, _opts) do
    if conn.assigns.current_user do
      conn
    else
      conn
      |> put_flash(:error, "You must be logged in to access that page")
      |> redirect(to: page_path(conn, :index))
      |> halt()
    end
  end

把這個部分的 code 移到 /web/controllers/auth.ex

  import Phoenix.Controller
  alias Rumbl.Router.Helpers

  def authenticate_user(conn, _opts) do
    if conn.assigns.current_user do
      conn
    else
      conn
      |> put_flash(:error, "You must be logged in to access that page")
      |> redirect(to: Helpers.page_path(conn, :index))
      |> halt()
    end
  end

修改 /web/web.ex ，增加 import Rumbl.Auth, only: [authenticate_user: 2]，把 authenticate_user 提供給 controller 及 router 使用

  def controller do
    quote do
      use Phoenix.Controller

      alias Rumbl.Repo
      import Ecto
      import Ecto.Query

      import Rumbl.Router.Helpers
      import Rumbl.Gettext

      import Rumbl.Auth, only: [authenticate_user: 2] # New import
    end
  end
  
  def router do
    quote do
      use Phoenix.Router

      import Rumbl.Auth, only: [authenticate_user: 2] # New import
    end
  end

修改 /web/controllers/usercontroller.ex ，將 :authenticate plug 改成 :authenticateuser

plug :authenticate_user when action in [:index, :show]

回到 router，定義新 scope /manage 包含 /videos resources

  scope "/manage", Rumbl do
    pipe_through [:browser, :authenticate_user]

    resources "/videos", VideoController
  end

測試一下 new, update, delete, read video 資料，全部都有了

---

VideoController 也有 pipeline，可使用 scrub_params

plug :scrub_params, "video" when action in [:create, :update]

因為 HTML form 沒有 nil 的概念，所以 blank input 都會被轉成 empty string，scrub_params 可將 form 參數裡面的 empty string 轉換為 nil

---

產生 DB Migrations

打開 /priv/repo/migrations/20170905083531_create_video.exs，增加 down function 處理 DB rollback

defmodule Rumbl.Repo.Migrations.CreateVideo do
  use Ecto.Migration

  def change do
    create table(:videos) do
      add :url, :string
      add :title, :string
      add :description, :text
      add :user_id, references(:users, on_delete: :nothing)

      timestamps()
    end
    create index(:videos, [:user_id])

  end

  def down do
#    drop constraint(:videos, "videos_user_id_fkey")
    execute "ALTER TABLE videos DROP FOREIGN KEY videos_user_id_fkey"
    alter table(:videos) do
      modify :user_id, references(:users, on_delete: :nothing)
    end

    drop_if_exists table("videos")
  end
end

可將 ecto videos table rollback 回去

$ mix ecto.rollback
[info] == Running Rumbl.Repo.Migrations.CreateVideo.down/0 forward
[info] execute "ALTER TABLE videos DROP FOREIGN KEY videos_user_id_fkey"
[info] alter table videos
[info] drop table if exists videos
[info] == Migrated in 0.0s


$ mix ecto.migrate
[info] == Running Rumbl.Repo.Migrations.CreateVideo.change/0 forward
[info] create table videos
[info] create index videos_user_id_index
[info] == Migrated in 0.0s

Building Relationships

/web/models/video.ex

defmodule Rumbl.Video do
  use Rumbl.Web, :model

  schema "videos" do
    field :url, :string
    field :title, :string
    field :description, :string

    # 定義 :user_id 欄位, 為 User.id 的 foreign key
    belongs_to :user, Rumbl.User

    timestamps()
  end

  @required_fields ~w(url title description)
  @optional_fields ~w()

  def changeset(model, params \\ %{}) do
    model
    |> cast(params, @required_fields, @optional_fields)
  end
end

/web/models/user.ex 加上 has_many :videos, Rumbl.Video

  use Rumbl.Web, :model
  schema "users" do
    field :name, :string
    field :username, :string
    field :password, :string, virtual: true
    field :password_hash, :string
    has_many :videos, Rumbl.Video

    timestamps()
  end

測試

$  iex -S mix

iex(1)> alias Rumbl.Repo
Rumbl.Repo
iex(2)> alias Rumbl.User
Rumbl.User
iex(3)> import Ecto.Query
Ecto.Query

iex(4)> user = Repo.get_by!(User, username: "josie")
[debug] QUERY OK source="users" db=4.2ms decode=2.6ms
SELECT u0.`id`, u0.`name`, u0.`username`, u0.`password_hash`, u0.`inserted_at`, u0.`updated_at` FROM `users` AS u0 WHERE (u0.`username` = ?) ["josie"]
%Rumbl.User{__meta__: #Ecto.Schema.Metadata<:loaded, "users">, id: 1,
 inserted_at: ~N[2017-09-03 13:09:55.000000], name: "Jose", password: nil,
 password_hash: "$2b$12$whoMkt3Va91Mk6yAmaM0sO/3dgh3nhNCem0M6xMB5UIMXo7vERw6O",
 updated_at: ~N[2017-09-05 01:22:52.000000], username: "josie",
 videos: #Ecto.Association.NotLoaded<association :videos is not loaded>}
 
iex(5)> user.videos
#Ecto.Association.NotLoaded<association :videos is not loaded>


# Repo.preload 接受 one / collection of names，取得所有相關的資料。
iex(6)> user = Repo.preload(user, :videos)
[debug] QUERY OK source="videos" db=1.6ms
SELECT v0.`id`, v0.`url`, v0.`title`, v0.`description`, v0.`user_id`, v0.`inserted_at`, v0.`updated_at`, v0.`user_id` FROM `videos` AS v0 WHERE (v0.`user_id` = ?) ORDER BY v0.`user_id` [1]
%Rumbl.User{__meta__: #Ecto.Schema.Metadata<:loaded, "users">, id: 1,
 inserted_at: ~N[2017-09-03 13:09:55.000000], name: "Jose", password: nil,
 password_hash: "$2b$12$whoMkt3Va91Mk6yAmaM0sO/3dgh3nhNCem0M6xMB5UIMXo7vERw6O",
 updated_at: ~N[2017-09-05 01:22:52.000000], username: "josie", videos: []}

iex(7)> user.videos
[]

iex(14)> user = Repo.get_by!(User, username: "josie")
[debug] QUERY OK source="users" db=0.8ms
SELECT u0.`id`, u0.`name`, u0.`username`, u0.`password_hash`, u0.`inserted_at`, u0.`updated_at` FROM `users` AS u0 WHERE (u0.`username` = ?) ["josie"]
%Rumbl.User{__meta__: #Ecto.Schema.Metadata<:loaded, "users">, id: 1,
 inserted_at: ~N[2017-09-03 13:09:55.000000], name: "Jose", password: nil,
 password_hash: "$2b$12$whoMkt3Va91Mk6yAmaM0sO/3dgh3nhNCem0M6xMB5UIMXo7vERw6O",
 updated_at: ~N[2017-09-05 01:22:52.000000], username: "josie",
 videos: #Ecto.Association.NotLoaded<association :videos is not loaded>}
 
 
iex(15)> attrs = %{title: "hi", description: "says hi", url: "example.com"}
%{description: "says hi", title: "hi", url: "example.com"}


iex(16)>  video = Ecto.build_assoc(user, :videos, attrs)
%Rumbl.Video{__meta__: #Ecto.Schema.Metadata<:built, "videos">,
 description: "says hi", id: nil, inserted_at: nil, title: "hi",
 updated_at: nil, url: "example.com",
 user: #Ecto.Association.NotLoaded<association :user is not loaded>, user_id: 1}
 
 
iex(17)> video = Repo.insert!(video)
[debug] QUERY OK db=0.2ms
begin []
[debug] QUERY OK db=6.2ms
INSERT INTO `videos` (`description`,`title`,`url`,`user_id`,`inserted_at`,`updated_at`) VALUES (?,?,?,?,?,?) ["says hi", "hi", "example.com", 1, {{2017, 9, 5}, {13, 37, 58, 291187}}, {{2017, 9, 5}, {13, 37, 58, 291202}}]
[debug] QUERY OK db=1.0ms
commit []
%Rumbl.Video{__meta__: #Ecto.Schema.Metadata<:loaded, "videos">,
 description: "says hi", id: 1, inserted_at: ~N[2017-09-05 13:37:58.291187],
 title: "hi", updated_at: ~N[2017-09-05 13:37:58.291202], url: "example.com",
 user: #Ecto.Association.NotLoaded<association :user is not loaded>, user_id: 1}

Ecto.buildassoc 可產生 struct，並有所有有關聯的欄位，呼叫 buildassoc 等同於呼叫

%Rumbl.Video{user_id: user.id, title: "hi", description: "says hi", url: "example.com"}

再重新測試一次

iex(1)> alias Rumbl.Repo
Rumbl.Repo
iex(2)> alias Rumbl.User
Rumbl.User
iex(3)> import Ecto.Query
Ecto.Query

iex(4)> user = Repo.get_by!(User, username: "josie")
[debug] QUERY OK source="users" db=2.2ms decode=2.5ms queue=0.1ms
SELECT u0.`id`, u0.`name`, u0.`username`, u0.`password_hash`, u0.`inserted_at`, u0.`updated_at` FROM `users` AS u0 WHERE (u0.`username` = ?) ["josie"]
%Rumbl.User{__meta__: #Ecto.Schema.Metadata<:loaded, "users">, id: 1,
 inserted_at: ~N[2017-09-03 13:09:55.000000], name: "Jose", password: nil,
 password_hash: "$2b$12$whoMkt3Va91Mk6yAmaM0sO/3dgh3nhNCem0M6xMB5UIMXo7vERw6O",
 updated_at: ~N[2017-09-05 01:22:52.000000], username: "josie",
 videos: #Ecto.Association.NotLoaded<association :videos is not loaded>}
 
 
iex(5)> user = Repo.preload(user, :videos)
[debug] QUERY OK source="videos" db=0.7ms
SELECT v0.`id`, v0.`url`, v0.`title`, v0.`description`, v0.`user_id`, v0.`inserted_at`, v0.`updated_at`, v0.`user_id` FROM `videos` AS v0 WHERE (v0.`user_id` = ?) ORDER BY v0.`user_id` [1]
%Rumbl.User{__meta__: #Ecto.Schema.Metadata<:loaded, "users">, id: 1,
 inserted_at: ~N[2017-09-03 13:09:55.000000], name: "Jose", password: nil,
 password_hash: "$2b$12$whoMkt3Va91Mk6yAmaM0sO/3dgh3nhNCem0M6xMB5UIMXo7vERw6O",
 updated_at: ~N[2017-09-05 01:22:52.000000], username: "josie",
 videos: [%Rumbl.Video{__meta__: #Ecto.Schema.Metadata<:loaded, "videos">,
   description: "says hi", id: 1, inserted_at: ~N[2017-09-05 13:37:58.000000],
   title: "hi", updated_at: ~N[2017-09-05 13:37:58.000000], url: "example.com",
   user: #Ecto.Association.NotLoaded<association :user is not loaded>,
   user_id: 1}]}

iex(6)> user.videos
[%Rumbl.Video{__meta__: #Ecto.Schema.Metadata<:loaded, "videos">,
  description: "says hi", id: 1, inserted_at: ~N[2017-09-05 13:37:58.000000],
  title: "hi", updated_at: ~N[2017-09-05 13:37:58.000000], url: "example.com",
  user: #Ecto.Association.NotLoaded<association :user is not loaded>,
  user_id: 1}]
  
  
  
iex(7)> query = Ecto.assoc(user, :videos)
#Ecto.Query<from v in Rumbl.Video, where: v.user_id == ^1>

iex(8)> Repo.all(query)
[debug] QUERY OK source="videos" db=0.7ms
SELECT v0.`id`, v0.`url`, v0.`title`, v0.`description`, v0.`user_id`, v0.`inserted_at`, v0.`updated_at` FROM `videos` AS v0 WHERE (v0.`user_id` = ?) [1]
[%Rumbl.Video{__meta__: #Ecto.Schema.Metadata<:loaded, "videos">,
  description: "says hi", id: 1, inserted_at: ~N[2017-09-05 13:37:58.000000],
  title: "hi", updated_at: ~N[2017-09-05 13:37:58.000000], url: "example.com",
  user: #Ecto.Association.NotLoaded<association :user is not loaded>,
  user_id: 1}]

Managing Related Data

video controller 可對 videos 進行 CRUD，但我們要將 videos 跟 users 連結在一起

可修改 /web/controllers/video_controller.ex

  # 跟現在登入的 user 連結在一起，用該 user create video
  def new(conn, _params) do
    changeset =
      conn.assigns.current_user
      |> build_assoc(:videos)
      |> Video.changeset()

    render(conn, "new.html", changeset: changeset)
  end

更進一步用另一種寫法，在 /web/controllers/video_controller.ex 加上 action(conn, _) ，同時修改 new, create 加上 user 參數

  def action(conn, _) do
    apply(__MODULE__, action_name(conn),
      [conn, conn.params, conn.assigns.current_user])
  end
  
  def new(conn, _params, user) do
    changeset =
      user
      |> build_assoc(:videos)
      |> Video.changeset()

    render(conn, "new.html", changeset: changeset)
  end

  def create(conn, %{"video" => video_params}, user) do
    changeset =
      user
      |> build_assoc(:videos)
      |> Video.changeset(video_params)

    case Repo.insert(changeset) do
      {:ok, _video} ->
        conn
        |> put_flash(:info, "Video created successfully.")
        |> redirect(to: video_path(conn, :index))
      {:error, changeset} ->
        render(conn, "new.html", changeset: changeset)
    end
  end

另外再增加 user_videos，取得某個 user 所有 videos

  defp user_videos(user) do
    assoc(user, :videos)
  end

再修改 index, show

  def index(conn, _params, user) do
    videos = Repo.all(user_videos(user))
    render(conn, "index.html", videos: videos)
  end
  
  def show(conn, %{"id" => id}, user) do
    video = Repo.get!(user_videos(user), id)
    render(conn, "show.html", video: video)
  end

update, edit, delete 也加上 user 參數

def edit(conn, %{"id" => id}, user) do
    video = Repo.get!(user_videos(user), id)
    changeset = Video.changeset(video)
    render(conn, "edit.html", video: video, changeset: changeset)
  end

  def update(conn, %{"id" => id, "video" => video_params}, user) do
    video = Repo.get!(user_videos(user), id)
    changeset = Video.changeset(video, video_params)

    case Repo.update(changeset) do
      {:ok, video} ->
        conn
        |> put_flash(:info, "Video updated successfully.")
        |> redirect(to: video_path(conn, :show, video))
      {:error, changeset} ->
        render(conn, "edit.html", video: video, changeset: changeset)
    end
  end

  def delete(conn, %{"id" => id}, user) do
    video = Repo.get!(user_videos(user), id)
    Repo.delete!(video)

    conn
    |> put_flash(:info, "Video deleted successfully.")
    |> redirect(to: video_path(conn, :index))
  end

---

Adding Categories

$ mix phoenix.gen.model Category categories name:string* creating web/models/category.ex
* creating test/models/category_test.exs
* creating priv/repo/migrations/20170905140814_create_category.exs

Remember to update your repository by running migrations:

    $ mix ecto.migrate

修改 migration，加上 null: false，以及 index

defmodule Rumbl.Repo.Migrations.CreateCategory do
  use Ecto.Migration

  def change do
    create table(:categories) do
      add :name, :string, null: false

      timestamps()
    end

    create unique_index(:categories, [:name])

  end
end

修改 /web/models/video.ex belongs_to :category, Rumbl.Category

  schema "videos" do
    field :url, :string
    field :title, :string
    field :description, :string

    # 定義 :user_id 欄位, 為 User.id 的 foreign key
    belongs_to :user, Rumbl.User
    belongs_to :category, Rumbl.Category

    timestamps()
  end

adds category_id to video

$ mix ecto.gen.migration add_category_id_to_video
Compiling 14 files (.ex)
warning: function authenticate/2 is unused
  web/controllers/user_controller.ex:40

Generated rumbl app
* creating priv/repo/migrations
* creating priv/repo/migrations/20170905141843_add_category_id_to_video.exs

修改 priv/repo/migrations/20170905141843addcategoryidto_video.exs

defmodule Rumbl.Repo.Migrations.AddCategoryIdToVideo do
  use Ecto.Migration

  def change do
     ## enforce a constraint between
videos and categories
    alter table(:videos) do
      add :category_id, references(:categories)
    end
  end
end

migrate database

$ mix ecto.migrate
[info] == Running Rumbl.Repo.Migrations.CreateCategory.change/0 forward
[info] create table categories
[info] create index categories_name_index
[info] == Migrated in 0.0s
[info] == Running Rumbl.Repo.Migrations.AddCategoryIdToVideo.change/0 forward
[info] alter table videos
[info] == Migrated in 0.0s

---

Setup Category Seed Data

如果要讓 categories 固定，但不想產生 contoller, view, templates，只需要在啟動時，在 database 塞入資料。

Phoenix 已經有個 seeding data 的功能，在 /priv/repo/seeds.exs，填寫以下 code

alias Rumbl.Repo
alias Rumbl.Category

for category <- ~w(Action Drama Romance Comedy Sci-fi) do
  Repo.insert!(%Category{name: category})
end

$ mix run priv/repo/seeds.exs
[debug] QUERY OK db=5.1ms
INSERT INTO `categories` (`name`,`inserted_at`,`updated_at`) VALUES (?,?,?) ["Action", {{2017, 9, 5}, {14, 24, 45, 713209}}, {{2017, 9, 5}, {14, 24, 45, 715805}}]
[debug] QUERY OK db=2.2ms queue=0.1ms
INSERT INTO `categories` (`name`,`inserted_at`,`updated_at`) VALUES (?,?,?) ["Drama", {{2017, 9, 5}, {14, 24, 45, 749923}}, {{2017, 9, 5}, {14, 24, 45, 749940}}]
[debug] QUERY OK db=1.8ms queue=0.1ms
INSERT INTO `categories` (`name`,`inserted_at`,`updated_at`) VALUES (?,?,?) ["Romance", {{2017, 9, 5}, {14, 24, 45, 752539}}, {{2017, 9, 5}, {14, 24, 45, 752546}}]
[debug] QUERY OK db=0.6ms queue=0.1ms
INSERT INTO `categories` (`name`,`inserted_at`,`updated_at`) VALUES (?,?,?) ["Comedy", {{2017, 9, 5}, {14, 24, 45, 754714}}, {{2017, 9, 5}, {14, 24, 45, 754719}}]
[debug] QUERY OK db=0.7ms
INSERT INTO `categories` (`name`,`inserted_at`,`updated_at`) VALUES (?,?,?) ["Sci-fi", {{2017, 9, 5}, {14, 24, 45, 755721}}, {{2017, 9, 5}, {14, 24, 45, 755730}}]

如果執行兩次會發生 error，因為 DB 有設定 unique index 的關係

$ mix run priv/repo/seeds.exs
[debug] QUERY ERROR db=6.1ms
INSERT INTO `categories` (`name`,`inserted_at`,`updated_at`) VALUES (?,?,?) ["Action", {{2017, 9, 5}, {14, 25, 5, 952360}}, {{2017, 9, 5}, {14, 25, 5, 954959}}]
** (Ecto.ConstraintError) constraint error when attempting to insert struct:

    * unique: categories_name_index

再修改 seeds.exs，這樣就不會出現 error

alias Rumbl.Repo
alias Rumbl.Category

for category <- ~w(Action Drama Romance Comedy Sci-fi) do
  Repo.get_by(Category, name: category) ||
    Repo.insert!(%Category{name: category})
end

---

Associating Videos and Categories

1. 由 DB 取得所有 categories names and IDs
2. 以 name 排序
3. 傳入 view 並放在 select input 裡面

首先測試 Query 功能

$ iex -S mix
Erlang/OTP 20 [erts-9.0] [source] [64-bit] [smp:4:4] [ds:4:4:10] [async-threads:10] [hipe] [kernel-poll:false]

Interactive Elixir (1.5.1) - press Ctrl+C to exit (type h() ENTER for help)
iex(1)> import Ecto.Query
Ecto.Query
iex(2)> alias Rumbl.
Auth                 Category             Endpoint
ErrorHelpers         ErrorView            Gettext
LayoutView           Mixfile              PageController
PageView             Repo                 Router
SessionController    SessionView          User
UserController       UserSocket           UserView
Video                VideoController      VideoView
Web                  config_change/3      start/2

iex(2)> alias Rumbl.Repo
Rumbl.Repo
iex(3)> alias Rumbl.Category
Rumbl.Category

iex(4)> Repo.all from c in Category, select: c.name
[debug] QUERY OK source="categories" db=2.3ms
SELECT c0.`name` FROM `categories` AS c0 []
["Action", "Comedy", "Drama", "Romance", "Sci-fi"]

• Repo.all 回傳所有 rows
• from 是產生 query 的 macro
• c in Category 表示取得 category 所有 rows
• select: c.name 表示只需要 name 欄位
• order_by 是排序

iex(6)> Repo.all from c in Category, order_by: c.name, select: {c.name, c.id}
[debug] QUERY OK source="categories" db=0.6ms
SELECT c0.`name`, c0.`id` FROM `categories` AS c0 ORDER BY c0.`name` []
[{"Action", 1}, {"Comedy", 4}, {"Drama", 2}, {"Romance", 3}, {"Sci-fi", 5}]

Ecto 的 query 是 composable

Ecto 定義了 Ecto.Queryable queryable protocol， from 會接受 queryable，可使用任何一個 queryable 當作新的 queryable 的基礎

iex(8)> query = Category
Rumbl.Category
iex(9)> query = from c in query, order_by: c.name
#Ecto.Query<from c in Rumbl.Category, order_by: [asc: c.name]>
iex(10)> query = from c in query, select: {c.name, c.id}
#Ecto.Query<from c in Rumbl.Category, order_by: [asc: c.name],
 select: {c.name, c.id}>
iex(11)> Repo.all query
[debug] QUERY OK source="categories" db=0.6ms
SELECT c0.`name`, c0.`id` FROM `categories` AS c0 ORDER BY c0.`name` []
[{"Action", 1}, {"Comedy", 4}, {"Drama", 2}, {"Romance", 3}, {"Sci-fi", 5}]

在 /web/models/category.ex 新增兩個 function

  # 以 queryable 為參數，且 return queryable
  def alphabetical(query) do
    from c in query, order_by: c.name
  end

  def names_and_ids(query) do
    from c in query, select: {c.name, c.id}
  end

/web/controllers/video_controller.ex 新增

  alias Rumbl.Category

  plug :load_categories when action in [:new, :create, :edit, :update]

  defp load_categories(conn, _) do
    query =
      Category
      |> Category.alphabetical
      |> Category.names_and_ids
    categories = Repo.all query
    assign(conn, :categories, categories)
  end

/web/templates/video/form.html.eex 新增


<div class="form-group">
<%= label f, :category_id, "Category", class: "control-label" %>
<%= select f, :category_id, @categories, class: "form-control",
prompt: "Choose a category" %>
</div>


修改 new.html.eex

<h2>New video</h2>

<%= render "form.html", changeset: @changeset, categories: @categories,
                        action: video_path(@conn, :create) %>

<%= link "Back", to: video_path(@conn, :index) %>

edit.html.eex

<h2>Edit video</h2>

<%= render "form.html", changeset: @changeset, categories: @categories,
                        action: video_path(@conn, :update, @video) %>

<%= link "Back", to: video_path(@conn, :index) %>

Deeper into Ecto Queries

$ iex -S mix
iex(1)> import Ecto.Query
Ecto.Query
iex(2)> alias Rumbl.Repo
Rumbl.Repo
iex(3)> alias Rumbl.User
Rumbl.User

iex(4)> username = "josie"
"josie"

iex(5)> Repo.one(from u in User, where: u.username == ^username)
[debug] QUERY OK source="users" db=2.2ms decode=2.7ms
SELECT u0.`id`, u0.`name`, u0.`username`, u0.`password_hash`, u0.`inserted_at`, u0.`updated_at` FROM `users` AS u0 WHERE (u0.`username` = ?) ["josie"]
%Rumbl.User{__meta__: #Ecto.Schema.Metadata<:loaded, "users">, id: 1,
 inserted_at: ~N[2017-09-03 13:09:55.000000], name: "Jose", password: nil,
 password_hash: "$2b$12$DtmZgYW2J.jxy3I0kNDAI.ooOsdcOXc6bezZuBzaTwaYSxbF1oBIS",
 updated_at: ~N[2017-09-05 01:22:52.000000], username: "josie",
 videos: #Ecto.Association.NotLoaded<association :videos is not loaded>}

• Repo.one 只要回傳 one row，並不表示只有一個結果，而是只需要一個結果
• from u in User 表示要讀取 User schema
• where: u.username == ^username ，使用 ^ 可保證 username 永遠不會異動，^ 也可保證不會發生 sql injection
• select 可省略，表示回傳整個 struct
• 型別錯誤會發生 error

iex(7)> username = 123
123
iex(8)> Repo.one(from u in User, where: u.username == ^username)
** (Ecto.Query.CastError) iex:8: value `123` in `where` cannot be cast to type :string in query:

from u in Rumbl.User,
  where: u.username == ^123,
  select: u

---

Phoenix 的 MVC 如下

1. controller 由 socket 讀取資料
2. parse 資料 into data structures (ex: params map)
3. 傳送到 Model，轉換參數為 changesets/queries
4. Elixir structs, changesets, queries 都只是資料，可以在 function 中一個傳給一個，一步一步修改資料
5. 透過 Repo 修改到 DB/發送 email 給系統負責人
6. 最後呼叫 view，將 model 轉成 view data (JSON/HTML)

---

Query API

可在 query 中使用的

1. Comparison operators: ==, !=, <=, >=, <, >
2. Boolean operators: and, or, not
3. Inclusion operator: in
4. Search functions: like and ilike
5. Null check functions: is_nil
6. Aggregates: count, avg, sum, min, max
7. Date/time intervals: datetimeadd, dateadd
8. General: fragment, field, and type

可使用 keyword syntax 或是 pipe syntax

---

以 Keyword syntax 撰寫 Queries

Repo.one from u in User,
    select: count(u.id),
    where: like(u.username, ^"j%")
        or like(u.username, ^"c%")

iex(9)> Repo.one from u in User, select: count(u.id), where: like(u.username, ^"j%") or like(u.username, ^"c%")
[debug] QUERY OK source="users" db=6.2ms
SELECT count(u0.`id`) FROM `users` AS u0 WHERE ((u0.`username` LIKE ?) OR (u0.`username` LIKE ?)) ["j%", "c%"]
2

iex(12)> users_count = from u in User, select: count(u.id)
#Ecto.Query<from u in Rumbl.User, select: count(u.id)>

iex(13)> j_users = from u in users_count, where: like(u.username, ^"%j%")
#Ecto.Query<from u in Rumbl.User, where: like(u.username, ^"%j%"),
 select: count(u.id)>

iex(14)> j_users = from q in users_count, where: like(q.username, ^"%j%")
#Ecto.Query<from u in Rumbl.User, where: like(u.username, ^"%j%"),
 select: count(u.id)>

---

以 Pipe syntax 撰寫 Queries

User |>
select([u], count(u.id)) |>
where([u], like(u.username, ^"j%") or like(u.username, ^"c%")) |>
Repo.one()


iex(17)> User |>
...(17)> select([u], count(u.id)) |>
...(17)> where([u], like(u.username, ^"j%") or like(u.username, ^"c%")) |>
...(17)> Repo.one()
[debug] QUERY OK source="users" db=0.8ms
SELECT count(u0.`id`) FROM `users` AS u0 WHERE ((u0.`username` LIKE ?) OR (u0.`username` LIKE ?)) ["j%", "c%"]
2

Fragments

Ecto query fragment 會發送 DB 部分的 query，並能以更安全的方式產生 query string

from(u in User,
    where: fragment("lower(username) = ?",
    ^String.downcase(uname)))

Ecto 也可以直接執行 SQL

Ecto.Adapters.SQL.query(Rumbl.Repo, "SELECT power(?, ?)", [2, 10])

# note: PostgreSQL 要改為
Ecto.Adapters.SQL.query(Rumbl.Repo, "SELECT power($1, $2)", [2, 10])

iex(24)> Ecto.Adapters.SQL.query(Rumbl.Repo, "SELECT power(?, ?)", [2, 10])
[debug] QUERY OK db=6.4ms
SELECT power(?, ?) [2, 10]
{:ok,
 %Mariaex.Result{columns: ["power(?, ?)"], connection_id: nil,
  last_insert_id: nil, num_rows: 1, rows: [[1024.0]]}}

Querying Relationships

Ecto 支援 association relationship，可用 Repo.preload 取得 associated data

iex(1)> import Ecto.Query
Ecto.Query
iex(2)> alias Rumbl.Repo
Rumbl.Repo
iex(3)> alias Rumbl.User
Rumbl.User

# 取得一個 user
iex(4)> user = Repo.one from(u in User, limit: 1)
[debug] QUERY OK source="users" db=3.4ms decode=5.8ms
SELECT u0.`id`, u0.`name`, u0.`username`, u0.`password_hash`, u0.`inserted_at`, u0.`updated_at` FROM `users` AS u0 LIMIT 1 []
%Rumbl.User{__meta__: #Ecto.Schema.Metadata<:loaded, "users">, id: 1,
 inserted_at: ~N[2017-09-03 13:09:55.000000], name: "Jose", password: nil,
 password_hash: "$2b$12$DtmZgYW2J.jxy3I0kNDAI.ooOsdcOXc6bezZuBzaTwaYSxbF1oBIS",
 updated_at: ~N[2017-09-05 01:22:52.000000], username: "josie",
 videos: #Ecto.Association.NotLoaded<association :videos is not loaded>}
 
# user.videos 尚未 preload
iex(5)> user.videos
#Ecto.Association.NotLoaded<association :videos is not loaded>

# preload :videos
iex(6)> user = Repo.preload(user, :videos)
[debug] QUERY OK source="videos" db=0.8ms
SELECT v0.`id`, v0.`url`, v0.`title`, v0.`description`, v0.`user_id`, v0.`category_id`, v0.`inserted_at`, v0.`updated_at`, v0.`user_id` FROM `videos` AS v0 WHERE (v0.`user_id` = ?) ORDER BY v0.`user_id` [1]
%Rumbl.User{__meta__: #Ecto.Schema.Metadata<:loaded, "users">, id: 1,
 inserted_at: ~N[2017-09-03 13:09:55.000000], name: "Jose", password: nil,
 password_hash: "$2b$12$DtmZgYW2J.jxy3I0kNDAI.ooOsdcOXc6bezZuBzaTwaYSxbF1oBIS",
 updated_at: ~N[2017-09-05 01:22:52.000000], username: "josie",
 videos: [%Rumbl.Video{__meta__: #Ecto.Schema.Metadata<:loaded, "videos">,
   category: #Ecto.Association.NotLoaded<association :category is not loaded>,
   category_id: nil, description: "test", id: 2,
   inserted_at: ~N[2017-09-05 14:06:33.000000], title: "test",
   updated_at: ~N[2017-09-05 14:06:33.000000], url: "test",
   user: #Ecto.Association.NotLoaded<association :user is not loaded>,
   user_id: 1},
  %Rumbl.Video{__meta__: #Ecto.Schema.Metadata<:loaded, "videos">,
   category: #Ecto.Association.NotLoaded<association :category is not loaded>,
   category_id: nil, description: "t2", id: 3,
   inserted_at: ~N[2017-09-05 16:11:55.000000], title: "t2",
   updated_at: ~N[2017-09-05 16:11:55.000000], url: "t2",
   user: #Ecto.Association.NotLoaded<association :user is not loaded>,
   user_id: 1}]}

iex(7)> user.videos

# 也可以直接在 query 時 preload
iex(8)> user = Repo.one from(u in User, limit: 1, preload: [:videos])
[debug] QUERY OK source="users" db=0.9ms
SELECT u0.`id`, u0.`name`, u0.`username`, u0.`password_hash`, u0.`inserted_at`, u0.`updated_at` FROM `users` AS u0 LIMIT 1 []
[debug] QUERY OK source="videos" db=0.8ms
SELECT v0.`id`, v0.`url`, v0.`title`, v0.`description`, v0.`user_id`, v0.`category_id`, v0.`inserted_at`, v0.`updated_at`, v0.`user_id` FROM `videos` AS v0 WHERE (v0.`user_id` = ?) ORDER BY v0.`user_id` [1]
%Rumbl.User{__meta__: #Ecto.Schema.Metadata<:loaded, "users">, id: 1,
 inserted_at: ~N[2017-09-03 13:09:55.000000], name: "Jose", password: nil,
 password_hash: "$2b$12$DtmZgYW2J.jxy3I0kNDAI.ooOsdcOXc6bezZuBzaTwaYSxbF1oBIS",
 updated_at: ~N[2017-09-05 01:22:52.000000], username: "josie",
 videos: [%Rumbl.Video{__meta__: #Ecto.Schema.Metadata<:loaded, "videos">,
   category: #Ecto.Association.NotLoaded<association :category is not loaded>,
   category_id: nil, description: "test", id: 2,
   inserted_at: ~N[2017-09-05 14:06:33.000000], title: "test",
   updated_at: ~N[2017-09-05 14:06:33.000000], url: "test",
   user: #Ecto.Association.NotLoaded<association :user is not loaded>,
   user_id: 1},
  %Rumbl.Video{__meta__: #Ecto.Schema.Metadata<:loaded, "videos">,
   category: #Ecto.Association.NotLoaded<association :category is not loaded>,
   category_id: nil, description: "t2", id: 3,
   inserted_at: ~N[2017-09-05 16:11:55.000000], title: "t2",
   updated_at: ~N[2017-09-05 16:11:55.000000], url: "t2",
   user: #Ecto.Association.NotLoaded<association :user is not loaded>,
   user_id: 1}]}

加上過濾條件

Repo.all from u in User,
join: v in assoc(u, :videos),
join: c in assoc(v, :category),
where: c.name == "Comedy",
select: {u, v}

iex(9)> Repo.all from u in User,
...(9)> join: v in assoc(u, :videos),
...(9)> join: c in assoc(v, :category),
...(9)> where: c.name == "Comedy",
...(9)> select: {u, v}
[debug] QUERY OK source="users" db=5.4ms
SELECT u0.`id`, u0.`name`, u0.`username`, u0.`password_hash`, u0.`inserted_at`, u0.`updated_at`, v1.`id`, v1.`url`, v1.`title`, v1.`description`, v1.`user_id`, v1.`category_id`, v1.`inserted_at`, v1.`updated_at` FROM `users` AS u0 INNER JOIN `videos` AS v1 ON v1.`user_id` = u0.`id` INNER JOIN `categories` AS c2 ON c2.`id` = v1.`category_id` WHERE (c2.`name` = 'Comedy') []
[]

Constraints

• constraint

  
  

  explicit database constraint 可能是 unique constraint on an index，或是 integrity constant between primary and foreign keys

• constraint error

  
  

  Ecto.ConstraintError (如果 add 一樣的 category 兩次)

• changeset constraint

  
  

  附加到 changeset 的 a constraint annotation，可讓 Ecto 轉換 constrant error 為 changeset error messages

• changeset error message

---

Validating Unique Data (Table Primary Key)

在 DB migration 中加上 unique index

create unique_index(:users, [:username])

/web/models/user.ex，changeset 加上 unique_constraint

  def changeset(model, params \\ %{}) do
    model
    |> cast(params, ~w(name username), [])
    |> validate_length(:username, min: 1, max: 20)
    |> unique_constraint(:username)
  end

---

Validating Foreign Keys

在 /web/models/video.ex 加上 category constraint

notes: 書本是寫成 @optionalfields ~w(categoryid)，但是把 categoryid 放在 requiredfields，changeset 資料才會處理 category_id 欄位，這裡是比較奇怪的地方...

  @required_fields ~w(url title description category_id)
  @optional_fields ~w()

  def changeset(model, params \\ %{}) do
    Logger.debug "params: #{inspect(params)}"
    model
    |> cast(params, @required_fields, @optional_fields)
    |> assoc_constraint(:category)
  end

$ iex -S mix

iex(1)> alias Rumbl.Category
Rumbl.Category
iex(2)> alias Rumbl.Video
Rumbl.Video
iex(3)> alias Rumbl.Repo
Rumbl.Repo
iex(4)> import Ecto.Query
Ecto.Query

# 取得 Drama category
iex(5)> category = Repo.get_by Category, name: "Drama"
[debug] QUERY OK source="categories" db=2.9ms
SELECT c0.`id`, c0.`name`, c0.`inserted_at`, c0.`updated_at` FROM `categories` AS c0 WHERE (c0.`name` = ?) ["Drama"]
%Rumbl.Category{__meta__: #Ecto.Schema.Metadata<:loaded, "categories">, id: 2,
 inserted_at: ~N[2017-09-05 14:24:45.000000], name: "Drama",
 updated_at: ~N[2017-09-05 14:24:45.000000]}

# 取得一個video
iex(8)> video = Repo.one(from v in Video, limit: 1)
[debug] QUERY OK source="videos" db=0.5ms
SELECT v0.`id`, v0.`url`, v0.`title`, v0.`description`, v0.`user_id`, v0.`category_id`, v0.`inserted_at`, v0.`updated_at` FROM `videos` AS v0 LIMIT 1 []
%Rumbl.Video{__meta__: #Ecto.Schema.Metadata<:loaded, "videos">,
 category: #Ecto.Association.NotLoaded<association :category is not loaded>,
 category_id: 2, description: "test", id: 2,
 inserted_at: ~N[2017-09-05 14:06:33.000000], title: "test",
 updated_at: ~N[2017-09-06 02:09:40.000000], url: "test",
 user: #Ecto.Association.NotLoaded<association :user is not loaded>, user_id: 1}


# 修改 category_id
iex(10)> changeset = Video.changeset(video, %{category_id: category.id})
[debug] params: %{category_id: 2}
#Ecto.Changeset<action: nil, changes: %{}, errors: [], data: #Rumbl.Video<>,
 valid?: true>

# 寫入 DB
iex(12)> Repo.update(changeset)
{:ok,
 %Rumbl.Video{__meta__: #Ecto.Schema.Metadata<:loaded, "videos">,
  category: #Ecto.Association.NotLoaded<association :category is not loaded>,
  category_id: 2, description: "test", id: 2,
  inserted_at: ~N[2017-09-05 14:06:33.000000], title: "test",
  updated_at: ~N[2017-09-06 02:09:40.000000], url: "test",
  user: #Ecto.Association.NotLoaded<association :user is not loaded>,
  user_id: 1}}
  
# 將 category_id 設定為 12345
iex(13)> changeset = Video.changeset(video, %{category_id: 12345})
[debug] params: %{category_id: 12345}
#Ecto.Changeset<action: nil, changes: %{category_id: 12345}, errors: [],
 data: #Rumbl.Video<>, valid?: true>
 
# update 時發生 error
iex(14)> Repo.update(changeset)
[debug] QUERY OK db=0.1ms
begin []
[debug] QUERY ERROR db=201.6ms
UPDATE `videos` SET `category_id` = ?, `updated_at` = ? WHERE `id` = ? [12345, {{2017, 9, 6}, {2, 12, 11, 984209}}, 2]
[debug] QUERY OK db=0.4ms
rollback []
{:error,
 #Ecto.Changeset<action: :update, changes: %{category_id: 12345},
  errors: [category: {"does not exist", []}], data: #Rumbl.Video<>,
  valid?: false>}

---

Delete

$ iex -S mix
iex(1)> alias Rumbl.Repo
Rumbl.Repo
iex(2)> category = Repo.get_by Rumbl.Category, name: "Drama"
[debug] QUERY OK source="categories" db=2.5ms decode=3.3ms
SELECT c0.`id`, c0.`name`, c0.`inserted_at`, c0.`updated_at` FROM `categories` AS c0 WHERE (c0.`name` = ?) ["Drama"]
%Rumbl.Category{__meta__: #Ecto.Schema.Metadata<:loaded, "categories">, id: 2,
 inserted_at: ~N[2017-09-05 14:24:45.000000], name: "Drama",
 updated_at: ~N[2017-09-05 14:24:45.000000]}
iex(3)> Repo.delete category
[debug] QUERY ERROR db=1.9ms
DELETE FROM `categories` WHERE `id` = ? [2]
** (Ecto.ConstraintError) constraint error when attempting to delete struct:

    * foreign_key: videos_category_id_fkey

If you would like to convert this constraint into an error, please
call foreign_key_constraint/3 in your changeset and define the proper
constraint name. The changeset has not defined any constraint.

    (ecto) lib/ecto/repo/schema.ex:570: anonymous fn/4 in Ecto.Repo.Schema.constraints_to_errors/3
    (elixir) lib/enum.ex:1255: Enum."-map/2-lists^map/1-0-"/2
    (ecto) lib/ecto/repo/schema.ex:555: Ecto.Repo.Schema.constraints_to_errors/3
    (ecto) lib/ecto/repo/schema.ex:382: anonymous fn/9 in Ecto.Repo.Schema.do_delete/4

delete 也可以用 changeset 當參數

iex(3)> import Ecto.Changeset
Ecto.Changeset
iex(4)> changeset = Ecto.Changeset.change(category)
#Ecto.Changeset<action: nil, changes: %{}, errors: [], data: #Rumbl.Category<>,
 valid?: true>
iex(5)> changeset = foreign_key_constraint(changeset, :videos, name: :videos_category_id_fkey, message: "still exist")
#Ecto.Changeset<action: nil, changes: %{}, errors: [], data: #Rumbl.Category<>,
 valid?: true>

iex(6)> Repo.delete changeset
[debug] QUERY ERROR db=5.1ms
DELETE FROM `categories` WHERE `id` = ? [2]
{:error,
 #Ecto.Changeset<action: :delete, changes: %{},
  errors: [videos: {"still exist", []}], data: #Rumbl.Category<>,
  valid?: false>}

在 DB migration 裡，references 裡面可加上 :on_delete option

• :nothing

  
  

  預設值

• :delete_all

  
  

  刪除某個 category 時，所有這個 category 的 videos 都會被刪除

• :nilify_all

  
  

  刪除某個 cateogry 時，所有這個 category 的 videos 的 category_id 會被設定為 null

ex:

add :category_id, references(:categories, on_delete: :nothing)

References

Programming Phoenix