更深入了解 Ecto
Generators
瞭解如何定義 table 的關聯，User 會選擇 video，video裡有多個 comments(annotations)，comments 是由 User 建立的。
可使用 generators 產生 skeleton: 包含 migration, controllers, templates
video 相關的欄位為
- an associated User
- A creation time for the video
- A URL of the video location
- A title
- 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執行後會得到
- 定義 model 的 module name
- model name
- 每個欄位及 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.0sBuilding 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)
endupdate, 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))
endAdding 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()
endadds 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
endmigrate 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.0sSetup 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})
endAssociating Videos and Categories
- 由 DB 取得所有 categories names and IDs
- 以 name 排序
- 傳入 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: uPhoenix 的 MVC 如下
- controller 由 socket 讀取資料
- parse 資料 into data structures (ex: params map)
- 傳送到 Model，轉換參數為 changesets/queries
- Elixir structs, changesets, queries 都只是資料，可以在 function 中一個傳給一個，一步一步修改資料
- 透過 Repo 修改到 DB/發送 email 給系統負責人
- 最後呼叫 view，將 model 轉成 view data (JSON/HTML)
Query API
可在 query 中使用的
- Comparison operators: ==, !=, <=, >=, <, >
- Boolean operators: and, or, not
- Inclusion operator: in
- Search functions: like and ilike
- Null check functions: is_nil
- Aggregates: count, avg, sum, min, max
- Date/time intervals: datetimeadd, dateadd
- 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%"]
2iex(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%"]
2Fragments
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)
endValidating 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/4delete 也可以用 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)
