Ecto Query DSL 与 SQL：能 query 就不要 raw

Ecto.Query 几乎能覆盖所有标准 SQL：join、聚合、窗口、CTE、FILTER、LEFT JOIN。raw SQL 应该留给"DSL 真的写不出来"的场景。

一、Query DSL 的最小例子

import Ecto.Query

query =
  from u in User,
    where: u.age >= 18,
    where: ilike(u.email, ^"%@example.com"),
    select: u

Repo.all(query)

关键语法：

• from u in User：绑定 schema 为查询主体。
• ^var：外部参数插值，永远是安全的，由适配器转义。
• where: 可重复，会被 AND。
• select: 决定返回结构，可以是 struct、map、字段组合。

二、JOIN：inner / left / assoc

from u in User,
  join: o in assoc(u, :orders),
  left_join: t in assoc(u, :team),
  where: o.total > ^1000,
  select: %{user: u.email, order_total: o.total, team: t.name}

Repo.all(query) |> Enum.map(& IO.inspect(&1))

JOIN 三种写法：

• join: o in Order, on: o.user_id == u.id：手写 ON。
• join: o in assoc(u, :orders)：用 schema 上定义的关联。
• left_join:：左连接，DSL 自动产出 LEFT OUTER JOIN。

三、聚合与 GROUP BY

from o in Order,
  group_by: o.user_id,
  select: %{
    user_id: o.user_id,
    cnt: count(o.id),
    sum: sum(o.total),
    avg: avg(o.total)
  }

常用的聚合函数：count/1、sum/1、avg/1、min/1、max/1。DSL 不支持 FILTER (WHERE ...) 内嵌聚合时，需要回退到 fragment。

四、FILTER：PostgreSQL 9.4+ 的杀手锏

PostgreSQL 允许聚合函数带 FILTER (WHERE ...) 子句，例如一次统计多个条件：

SQL 原生：
SELECT
  date_trunc('day', inserted_at) AS day,
  COUNT(*) AS total,
  COUNT(*) FILTER (WHERE status = 'paid')  AS paid_cnt,
  COUNT(*) FILTER (WHERE status = 'refund') AS refund_cnt
FROM orders
GROUP BY 1;

Ecto.Query 没有原生 FILTER，要靠 fragment/1：

from o in "orders",
  group_by: fragment("date_trunc('day', ?)", o.inserted_at),
  select: %{
    day: fragment("date_trunc('day', ?)", o.inserted_at),
    total: count(o.id),
    paid_cnt: fragment("COUNT(*) FILTER (WHERE ? = ?)", o.status, ^"paid"),
    refund_cnt: fragment("COUNT(*) FILTER (WHERE ? = ?)", o.status, ^"refund")
  }

注意：

• ? 是 fragment 占位符。
• 外部值用 ^var，不要直接拼接字符串到 fragment，避免注入。

五、CTE：with recursive

CTE 是分层、递归、组织长查询的关键工具。Ecto 通过 CTE 模块支持：

def monthly_active_users(year) do
  months = 1..12

  base =
    from u in User,
      select: %{id: u.id, m: fragment("EXTRACT(MONTH FROM ?)::int", u.last_seen_at)}

  cte = Ecto.Query.CTE.base(base, "mau")

  Ecto.Query.with_cte({"months_cte", months |> Enum.map(&%{m: &1})})

  from m in "months_cte",
    left_join: u in subquery(cte),
    on: u.m == m.m,
    select: %{month: m.m, users: count(u.id)}
end

真实生产中更常见的写法是 显式：

import Ecto.Query

def top_referrers(depth) do
  recursive = fn recursive, depth ->
    base = from u in User, where: u.inviter_id == ^0, select: %{id: u.id, level: 0}

    union =
      from u in User,
        join: p in subquery(recursive.(recursive, depth - 1)),
        on: u.inviter_id == p.id,
        select: %{id: u.id, level: p.level + 1}

    union_all(base, union)
  end

  from r in subquery(recursive.(recursive, depth)),
    join: u in User, on: u.id == r.id,
    group_by: u.id,
    select: %{user: u.email, depth: max(r.level)}
end

六、LEFT JOIN 的 N+1 陷阱

LEFT JOIN 完事后，统计每个用户的订单数，最容易写错：

# 错误：在应用层用 Enum.count
users = Repo.all(from u in User, left_join: o in assoc(u, :orders), select: %{u | order_count: count(o.id)})

# 正确：让数据库聚合
from u in User,
  left_join: o in assoc(u, :orders),
  group_by: u.id,
  select: %{u | order_cnt: count(o.id)}

或者用 preload 一次拉齐：

users = Repo.all(User) |> Repo.preload(orders: [:items])

七、什么时候必须 raw SQL

经验法则：DSL 能写就 DSL，下面情况回退 raw SQL：

1. 窗口函数 + FILTER 同时存在（DSL 不支持窗口）。
2. PostgreSQL 特有语法：RETURNING、ON CONFLICT DO UPDATE SET ...、JSONB 操作符链。
3. EXPLAIN / COPY：管理性命令无 DSL。
4. 复杂报表：超过 10 个 JOIN、跨库、跨 schema。

Repo.query!(
  """
  INSERT INTO user_segments (user_id, segment, day)
    VALUES ($1, $2, $3)
    ON CONFLICT (user_id, day) DO UPDATE SET segment = EXCLUDED.segment
  """,
  [user.id, "active", day]
)

八、查询调试三板斧

1. Repo.to_sql(:all, query)：把 query 转成 SQL 字符串，立刻看到发生了什么。
2. Ecto.Adapters.SQL.to_sql/3：更底层，会输出参数列表。
3. PostgreSQL EXPLAIN ANALYZE：把上面的 SQL 贴到 psql，看真实计划。

iex> q = from u in User, where: u.age > 18, select: u.email
iex> Ecto.Adapters.SQL.to_sql(:all, MyApp.Repo, q)
{"SELECT u0.\"email\" FROM \"users\" AS u0 WHERE (u0.\"age\" > 18)", []}

九、记忆口诀

能用 from 就用 from；能用 assoc 就用 assoc；fragment 只补 DSL 不支持的部分；外部值永远 ^var。

十、测验

**下一步：**Query 调通后，下一课看 Plug.Router——Ecto.Query 经常出现在 controller 里，路由设计决定了它什么时候被执行。