# Preload Patterns

Efficient strategies for loading associations in Ecto.

## Basic Preloading

### Single Association

```elixir
# In query
User
|> Repo.all()
|> Repo.preload(:posts)

# In query itself (single query with join)
from u in User,
  preload: [:posts]
|> Repo.all()
```

### Nested Associations

```elixir
# Preload nested: user -> posts -> comments
Repo.preload(user, posts: :comments)

# Multiple levels
Repo.preload(user, posts: [comments: :author])
```

### Multiple Associations

```elixir
# Multiple associations at same level
Repo.preload(user, [:posts, :comments, :profile])

# Mixed nesting
Repo.preload(user, [:profile, posts: :comments])
```

## Advanced Preloading

### Custom Query Preloads

```elixir
# Preload only active posts
active_posts_query = from p in Post, where: p.active == true

user
|> Repo.preload(posts: active_posts_query)
```

### Preload with Ordering

```elixir
ordered_posts = from p in Post, order_by: [desc: p.inserted_at]

user
|> Repo.preload(posts: ordered_posts)
```

### Preload with Limit

```elixir
# Get only latest 5 posts per user
recent_posts = from p in Post,
  order_by: [desc: p.inserted_at],
  limit: 5

users
|> Repo.preload(posts: recent_posts)
```

## Join Preloading

For filtering by associations, use joins:

```elixir
# Find users with published posts (efficient)
from u in User,
  join: p in assoc(u, :posts),
  where: p.published == true,
  preload: [posts: p]
|> Repo.all()
```

## Lateral Join for Top-N per Group

```elixir
# Get top 3 posts per user (PostgreSQL)
from u in User,
  inner_lateral_join: p in subquery(
    from p in Post,
      where: p.user_id == parent_as(:user).id,
      order_by: [desc: p.inserted_at],
      limit: 3
  ),
  as: :user,
  preload: [posts: p]
|> Repo.all()
```

## Context-Level Preloading

Always preload at the context boundary:

```elixir
# In context module
defmodule MyApp.Accounts do
  def get_user_with_posts!(id) do
    User
    |> Repo.get!(id)
    |> Repo.preload(:posts)
  end

  def list_users_with_profiles do
    User
    |> Repo.all()
    |> Repo.preload(:profile)
  end
end
```

## Preload in Pipelines

```elixir
def list_active_users_with_orders do
  User
  |> where([u], u.active == true)
  |> Repo.all()
  |> Repo.preload(orders: :line_items)
end
```

## Anti-Patterns to Avoid

### Preloading in Views

```elixir
# BAD: Preload in template
<%= for post <- Repo.preload(@user, :posts).posts do %>

# GOOD: Preload in controller/LiveView
assigns = %{user: Accounts.get_user_with_posts!(id)}
```

### Preloading Everything

```elixir
# BAD: Over-preloading
Repo.preload(user, [:posts, :comments, :likes, :followers, :following])

# GOOD: Preload only what's needed for the view
Repo.preload(user, [:profile, posts: :comments])
```

### Conditional Preloading

```elixir
# Use preload opts for conditional loading
def get_user(id, opts \\ []) do
  user = Repo.get!(User, id)

  if Keyword.get(opts, :with_posts, false) do
    Repo.preload(user, :posts)
  else
    user
  end
end
```

# Query Optimization Techniques

Strategies for optimizing Ecto queries and eliminating N+1 patterns.

## Batching Queries

### Replace Loop Queries with IN Clause

```elixir
# BAD: N queries
Enum.map(user_ids, fn id -> Repo.get(User, id) end)

# GOOD: Single query
from(u in User, where: u.id in ^user_ids)
|> Repo.all()
```

### Batch Inserts

```elixir
# BAD: N inserts
Enum.each(items, fn item ->
  %Item{}
  |> Item.changeset(item)
  |> Repo.insert()
end)

# GOOD: Single insert
Repo.insert_all(Item, items)
```

### Batch Updates

```elixir
# BAD: N updates
Enum.each(users, fn user ->
  user
  |> User.changeset(%{active: false})
  |> Repo.update()
end)

# GOOD: Single update
from(u in User, where: u.id in ^user_ids)
|> Repo.update_all(set: [active: false])
```

## Query Composition

### Composable Query Functions

```elixir
defmodule MyApp.Queries.UserQueries do
  import Ecto.Query

  def base, do: from(u in User)

  def active(query \\ base()) do
    from u in query, where: u.active == true
  end

  def with_posts(query \\ base()) do
    from u in query, preload: [:posts]
  end

  def recent(query \\ base(), days \\ 30) do
    cutoff = DateTime.utc_now() |> DateTime.add(-days, :day)
    from u in query, where: u.inserted_at > ^cutoff
  end
end

# Usage: Compose queries
UserQueries.base()
|> UserQueries.active()
|> UserQueries.with_posts()
|> UserQueries.recent(7)
|> Repo.all()
```

## Subqueries for Complex Filtering

### EXISTS Subquery

```elixir
# Find users with at least one published post
posts_subquery = from p in Post,
  where: p.user_id == parent_as(:user).id,
  where: p.published == true

from u in User,
  as: :user,
  where: exists(posts_subquery)
|> Repo.all()
```

### Count Subquery

```elixir
# Get users with post counts
post_counts = from p in Post,
  group_by: p.user_id,
  select: %{user_id: p.user_id, count: count(p.id)}

from u in User,
  left_join: pc in subquery(post_counts),
  on: pc.user_id == u.id,
  select: {u, pc.count}
|> Repo.all()
```

## Window Functions

### Ranking Within Groups

```elixir
# Get top post per user
from p in Post,
  windows: [user_window: [partition_by: p.user_id, order_by: [desc: p.likes]]],
  select: %{
    post: p,
    rank: row_number() |> over(:user_window)
  }
|> Repo.all()
|> Enum.filter(fn %{rank: rank} -> rank == 1 end)
```

## Database-Specific Optimizations

### PostgreSQL DISTINCT ON

```elixir
# Latest post per user (PostgreSQL only)
from p in Post,
  distinct: p.user_id,
  order_by: [asc: p.user_id, desc: p.inserted_at]
|> Repo.all()
```

### Index Hints

Ensure indexes exist for:

- Foreign keys (`user_id`, `post_id`)
- Columns in WHERE clauses
- Columns in ORDER BY
- Columns used in joins

```elixir
# Migration
create index(:posts, [:user_id])
create index(:posts, [:published, :inserted_at])
```

## Avoiding Common Pitfalls

### Select Only Needed Fields

```elixir
# BAD: Select all columns when only needing ids
Repo.all(User)
|> Enum.map(& &1.id)

# GOOD: Select only what's needed
from(u in User, select: u.id)
|> Repo.all()
```

### Use Streams for Large Datasets

```elixir
# BAD: Load all into memory
Repo.all(LargeTable)
|> Enum.each(&process/1)

# GOOD: Stream processing
LargeTable
|> Repo.stream()
|> Stream.each(&process/1)
|> Stream.run()
```

### Aggregate in Database

```elixir
# BAD: Count in Elixir
Repo.all(User) |> length()

# GOOD: Count in database
Repo.aggregate(User, :count)
```

## Monitoring Queries

### Telemetry for Query Logging

```elixir
# In application.ex
:telemetry.attach(
  "ecto-query-logger",
  [:my_app, :repo, :query],
  &MyApp.QueryLogger.handle_event/4,
  nil
)

defmodule MyApp.QueryLogger do
  require Logger

  def handle_event(_event, measurements, metadata, _config) do
    if measurements.total_time > 100_000_000 do  # > 100ms
      Logger.warning("Slow query: #{metadata.query}")
    end
  end
end
```