查询操作 #

import Ecto.Query

def list_users do
  Repo.all(User)
end

def list_users_query do
  query = from(u in User, select: u)
  Repo.all(query)
end

def list_user_emails do
  from(u in User, select: u.email)
  |> Repo.all()
end

def list_user_summaries do
  from(u in User, select: %{id: u.id, name: u.name, email: u.email})
  |> Repo.all()
end

def list_users do
  from(u in User)
  |> Repo.all()
end

def get_active_users do
  from(u in User, where: u.active == true)
  |> Repo.all()
end

def get_users_by_age(min_age, max_age) do
  from(u in User, where: u.age >= ^min_age and u.age <= ^max_age)
  |> Repo.all()
end

def search_users(opts) do
  query = from(u in User)

  query = if opts[:name] do
    from(u in query, where: ilike(u.name, ^"%#{opts[:name]}%"))
  else
    query
  end

  query = if opts[:active] do
    from(u in query, where: u.active == ^opts[:active])
  else
    query
  end

  Repo.all(query)
end

def get_users_by_ids(ids) do
  from(u in User, where: u.id in ^ids)
  |> Repo.all()
end

def get_users_without_email do
  from(u in User, where: is_nil(u.email))
  |> Repo.all()
end

def get_users_with_email do
  from(u in User, where: not is_nil(u.email))
  |> Repo.all()
end

def search_by_name(name) do
  from(u in User, where: ilike(u.name, ^"%#{name}%"))
  |> Repo.all()
end

def list_users_ordered do
  from(u in User, order_by: [desc: u.inserted_at])
  |> Repo.all()
end

def list_users_multi_order do
  from(u in User, order_by: [asc: u.name, desc: u.inserted_at])
  |> Repo.all()
end

def list_users(opts) do
  order = opts[:order] || :inserted_at
  direction = opts[:direction] || :desc

  from(u in User, order_by: [{^direction, ^order}])
  |> Repo.all()
end

def list_users_paginated(page, per_page) do
  offset = (page - 1) * per_page

  from(u in User,
    limit: ^per_page,
    offset: ^offset,
    order_by: [desc: u.inserted_at]
  )
  |> Repo.all()
end

def paginate(query, page, per_page) do
  offset = (page - 1) * per_page

  from(q in query,
    limit: ^per_page,
    offset: ^offset
  )
end

def list_users(page \\ 1, per_page \\ 10) do
  from(u in User, order_by: [desc: u.inserted_at])
  |> paginate(page, per_page)
  |> Repo.all()
end

def list_users_with_count(page \\ 1, per_page \\ 10) do
  query = from(u in User)

  total = from(q in query, select: count(q.id)) |> Repo.one()
  entries = query |> paginate(page, per_page) |> Repo.all()
  total_pages = ceil(total / per_page)

  %{
    entries: entries,
    total: total,
    page: page,
    per_page: per_page,
    total_pages: total_pages
  }
end

def count_users do
  from(u in User, select: count(u.id))
  |> Repo.one()
end

def count_active_users do
  from(u in User, where: u.active == true, select: count(u.id))
  |> Repo.one()
end

def user_stats do
  from(u in User,
    select: %{
      total: count(u.id),
      avg_age: avg(u.age),
      min_age: min(u.age),
      max_age: max(u.age)
    }
  )
  |> Repo.one()
end

def users_count_by_status do
  from(u in User,
    group_by: u.active,
    select: {u.active, count(u.id)}
  )
  |> Repo.all()
end

def posts_count_by_author do
  from(p in Post,
    group_by: p.author_id,
    select: {p.author_id, count(p.id)},
    order_by: [desc: count(p.id)]
  )
  |> Repo.all()
end

def authors_with_many_posts(min_posts) do
  from(p in Post,
    group_by: p.author_id,
    having: count(p.id) > ^min_posts,
    select: {p.author_id, count(p.id)}
  )
  |> Repo.all()
end

def posts_with_authors do
  from(p in Post,
    join: u in User,
    on: p.author_id == u.id,
    select: %{post: p, author: u}
  )
  |> Repo.all()
end

def comments_with_post_and_author do
  from(c in Comment,
    join: p in Post, on: c.post_id == p.id,
    join: u in User, on: p.author_id == u.id,
    select: %{comment: c, post: p, author: u}
  )
  |> Repo.all()
end

def posts_with_comments_count do
  from(p in Post,
    left_join: c in Comment,
    on: c.post_id == p.id,
    group_by: p.id,
    select: {p, count(c.id)}
  )
  |> Repo.all()
end

def list_posts_with_comments do
  from(p in Post, preload: [:comments])
  |> Repo.all()
end

def list_posts_with_author do
  from(p in Post, preload: [:author])
  |> Repo.all()
end

def list_posts_full do
  from(p in Post, preload: [:author, :comments, :tags])
  |> Repo.all()
end

def list_posts_with_comments_and_authors do
  from(p in Post, preload: [comments: :author])
  |> Repo.all()
end

def list_posts_with_recent_comments do
  from(p in Post,
    preload: [
      comments: from(c in Comment, where: c.inserted_at > ago(7, "day"))
    ]
  )
  |> Repo.all()
end

def users_with_posts do
  subquery = from(p in Post, select: p.author_id)

  from(u in User, where: u.id in subquery(subquery))
  |> Repo.all()
end

def users_with_published_posts do
  from(u in User,
    where: exists(from(p in Post, where: p.author_id == u.id and p.published == true))
  )
  |> Repo.all()
end

def search_by_full_name(name) do
  from(u in User,
    where: fragment("concat(?, ' ', ?) ilike ?", u.first_name, u.last_name, ^"%#{name}%")
  )
  |> Repo.all()
end

def execute_raw_sql do
  Ecto.Adapters.SQL.query!(Repo, "SELECT * FROM users WHERE age > $1", [18])
end

def search_users(filters) do
  query = from(u in User)

  query = Enum.reduce(filters, query, fn
    {:name, name}, q ->
      from(u in q, where: ilike(u.name, ^"%#{name}%"))

    {:email, email}, q ->
      from(u in q, where: ilike(u.email, ^"%#{email}%"))

    {:active, active}, q ->
      from(u in q, where: u.active == ^active)

    _, q -> q
  end)

  Repo.all(query)
end

def sort_by(query, field, direction) do
  from(q in query, order_by: [{^direction, ^field}])
end

def all_content do
  posts = from(p in Post, select: %{id: p.id, title: p.title, type: "post"})
  pages = from(p in Page, select: %{id: p.id, title: p.title, type: "page"})

  from(q in union(posts, ^pages))
  |> Repo.all()
end

def users_without_admins do
  all_users = from(u in User, select: u.id)
  admins = from(u in User, where: u.role == "admin", select: u.id)

  from(u in User, where: u.id in ^all_users and u.id not in ^admins)
  |> Repo.all()
end

from(u in User,
  where: u.active == true,
  select: u,
  order_by: [desc: u.inserted_at],
  limit: 10
)
|> Repo.all()