elixir on phoenix ectoを使っていろいろなSELECT文

今回はMysqlで使用するサンプルで使用するmodelはこんな感じです

defmodule Sample.User do
  use Sample.Web, :model

  schema "users" do
    field :name, :string
    field :age, :integer

    timestamps()
  end
end

#基本

#全件取得
User |> Repo.all()
#SELECT u0.`id`, u0.`name`, u0.`age`, u0.`inserted_at`, u0.`updated_at` FROM `users` AS u0

#IDから取得
User |> Repo.get!(1)
#SELECT u0.`id`, u0.`name`, u0.`age`, u0.`inserted_at`, u0.`updated_at` FROM `users` AS u0 WHERE (u0.`id` = ?) 
#[1]

#条件指定

User
  |> select([u], [u.name, u.age])
  |> Repo.all()
#SELECT u0.`name`, u0.`age` FROM `users` AS u0

User
  |> select([u], count(u.id))
  |> Repo.all()
#SELECT count(u0.`id`) FROM `users` AS u0

  

# =
name = "TEST"
User
  |> where([u], u.name == ^name)
  |> Repo.all()
  
#SELECT u0.`id`, u0.`name`, u0.`age`, u0.`inserted_at`, u0.`updated_at` FROM `users` AS u0 WHERE (u0.`name` = ?) 
#["TEST"]


# !=
name = "TEST"
User
  |> where([u], u.name != ^name)
  |> Repo.all()
  
#SELECT u0.`id`, u0.`name`, u0.`age`, u0.`inserted_at`, u0.`updated_at` FROM `users` AS u0 WHERE (u0.`name` != ?) 
#["TEST"]

# >
age = 20

User
    |> where([u], u.age > ^age)
    |> Repo.all()
    
#SELECT u0.`id`, u0.`name`, u0.`age`, u0.`inserted_at`, u0.`updated_at` FROM `users` AS u0 WHERE (u0.`age` > ?)
#[20]

# >=
age = 20

User
    |> where([u], u.age >= ^age)
    |> Repo.all()
    
#SELECT u0.`id`, u0.`name`, u0.`age`, u0.`inserted_at`, u0.`updated_at` FROM `users` AS u0 WHERE (u0.`age` >= ?)
#[20]

# <
age = 20

User
    |> where([u], u.age < ^age)
    |> Repo.all()
    
#SELECT u0.`id`, u0.`name`, u0.`age`, u0.`inserted_at`, u0.`updated_at` FROM `users` AS u0 WHERE (u0.`age` < ?)
#[20]

# <=
age = 20

User
    |> where([u], u.age <= ^age)
    |> Repo.all()
    
#SELECT u0.`id`, u0.`name`, u0.`age`, u0.`inserted_at`, u0.`updated_at` FROM `users` AS u0 WHERE (u0.`age` <= ?)
#[20]

# IN
age = [20, 30]

User
    |> where([u], u.age in ^age)
    |> Repo.all()
    
#SELECT u0.`id`, u0.`name`, u0.`age`, u0.`inserted_at`, u0.`updated_at` FROM `users` AS u0 WHERE (u0.`age` IN (?,?))
#[20, 30]

# IS NULL
User
    |> where([u], is_nil(u.age))
    |> Repo.all()
    
#SLECT u0.`id`, u0.`name`, u0.`age`, u0.`inserted_at`, u0.`updated_at` FROM `users` AS u0 WHERE (u0.`age` IS NULL) 

# LIKE
name = "TEST"
User
    |> where([u], like(u.name, ^("#{name}%")))
    |> Repo.all()
    
#SELECT u0.`id`, u0.`name`, u0.`age`, u0.`inserted_at`, u0.`updated_at` FROM `users` AS u0 WHERE (u0.`name` LIKE ?) 
#["TEST%"]

# OR
name1 = "TEST1"
name2 = "TEST2"
name3 = "TEST3"

User
    |> where([u], like(u.name, ^("#{name1}%")) or like(u.name, ^("#{name2}%")))
    |> where([u], like(u.name, ^("#{name3}%")))
    |> Repo.all()
    
#SELECT u0.`id`, u0.`name`, u0.`age`, u0.`inserted_at`, u0.`updated_at` FROM `users` AS u0 WHERE ((u0.`name` LIKE ?) OR (u0.`name` LIKE ?)) AND (u0.`name` LIKE ?)
#["TEST1%", "TEST2%", "TEST3%"]

#その他

User
  |> distinct(true)
  |> Repo.all()
#SELECT DISTINCT u0.`id`, u0.`name`, u0.`age`, u0.`inserted_at`, u0.`updated_at` FROM `users` AS u0

User
  |> limit(10)
  |> offset(0)
  |> Repo.all()
#SELECT u0.`id`, u0.`name`, u0.`age`, u0.`inserted_at`, u0.`updated_at` FROM `users` AS u0 LIMIT 10 OFFSET 0

User
  |> group_by([u], u.age)
  |> Repo.all()
#SELECT u0.`id`, u0.`name`, u0.`age`, u0.`inserted_at`, u0.`updated_at` FROM `users` AS u0 GROUP BY u0.`age`

User
  |> order_by([u], [asc: u.id, desc: u.age])
  |> Repo.all()
#SELECT u0.`id`, u0.`name`, u0.`age`, u0.`inserted_at`, u0.`updated_at` FROM `users` AS u0 ORDER BY u0.`id`, u0.`age` DESC

User
  |> join(:inner, [u], p in User, u.id == p.id)
  |> select([u, p], [u.id, p.name])
  |> Repo.all()
#SELECT u0.`id`, u1.`name` FROM `users` AS u0 INNER JOIN `users` AS u1 ON u0.`id` = u1.`id`

#リンク

1.elixir on phoenix セットアップ
2.elixir on phoenix Mix Tasksについて
3.elixir on phoenix ectoを使っていろいろなSELECT文
4.elixir on phoenix ルーティングの書き方についてまとめてみた
5.elixir on phoenix changesetでのValidates