ecto-query-patterns

What this skill does

# Ecto Query Patterns

Master Ecto's powerful Query DSL to build efficient, composable database queries.
This skill covers the query syntax, filtering, joining, aggregation, preloading
associations, and advanced query composition patterns.

## Basic Query with from Macro

```elixir
import Ecto.Query, only: [from: 2]

# Basic query using keyword syntax
query = from u in "users",
          where: u.age > 18,
          select: u.name

# Execute the query
MyApp.Repo.all(query)
```

Queries are built using the `from/2` macro and only sent to the database when
passed to a `Repo` function like `all/1`, `one/1`, or `get/2`. The keyword syntax
provides a readable way to construct queries.

## Query with Schema Module

```elixir
query = from u in MyApp.User,
          where: u.age > 18,
          select: u.name

MyApp.Repo.all(query)
```

Using a schema module instead of a table name string provides better type safety
and allows Ecto to use the schema's field definitions for validation and casting.

## Bindingless Query Construction

```elixir
from MyApp.Post,
  where: [category: "fresh and new"],
  order_by: [desc: :published_at],
  select: [:id, :title, :body]
```

Bindingless syntax allows building queries without explicit variable bindings.
This works well for simple queries and when using keyword list syntax for conditions.

## Query with Explicit Bindings

```elixir
query = from p in MyApp.Post,
          where: p.category == "fresh and new",
          order_by: [desc: p.published_at],
          select: struct(p, [:id, :title, :body])

MyApp.Repo.all(query)
```

Explicit bindings (like `p` for posts) allow for more complex conditions and
selections. The `struct/2` function selects only specific fields from the schema.

## Dynamic Query Variables

```elixir
category = "fresh and new"
order_by = [desc: :published_at]
select_fields = [:id, :title, :body]

query = from MyApp.Post,
          where: [category: ^category],
          order_by: ^order_by,
          select: ^select_fields

MyApp.Repo.all(query)
```

The pin operator `^` allows interpolating Elixir values into queries. This is
essential for parameterized queries and prevents SQL injection.

## Where Clause with Expressions

```elixir
query = from u in MyApp.User,
          where: u.age > 0,
          select: u.name

# Multiple where clauses are combined with AND
query = from u in MyApp.User,
          where: u.age > 18,
          where: u.confirmed == true,
          select: u

MyApp.Repo.all(query)
```

Query expressions support field access, comparison operators, and literals.
Multiple `where` clauses are automatically combined with AND logic.

## Composable Queries

```elixir
# Create a base query
query = from u in MyApp.User, where: u.age > 18

# Extend the query
query = from u in query, select: u.name

MyApp.Repo.all(query)
```

Queries are composable - you can build on existing queries by using them in the
`in` clause. This enables powerful query abstraction and reusability.

## Query Composition Function Pattern

```elixir
def most_recent_from(query, minimum_date) do
  from p in query,
    where: p.published_at > ^minimum_date,
    order_by: [desc: p.published_at]
end

# Usage
MyApp.Post
|> most_recent_from(~N[2024-01-01 00:00:00])
|> MyApp.Repo.all()
```

Extracting query logic into functions creates reusable, testable query components.
This pattern is fundamental to building maintainable query code.

## Or Where Conditions

```elixir
from p in MyApp.Post,
  where: p.category == "elixir" or p.category == "phoenix",
  select: p
```

Use the `or` keyword for alternative conditions. For more complex OR logic,
consider using `Ecto.Query.dynamic/2`.

## IN Query with List

```elixir
categories = ["elixir", "phoenix", "ecto"]

query = from p in MyApp.Post,
          where: p.category in ^categories,
          select: p

MyApp.Repo.all(query)
```

The `in` operator checks if a field value exists in a list of values. Use the
pin operator to interpolate the list variable.

## Like and ILike for Pattern Matching

```elixir
search_term = "%elixir%"

query = from p in MyApp.Post,
          where: like(p.title, ^search_term),
          select: p

# Case-insensitive version
query = from p in MyApp.Post,
          where: ilike(p.title, ^search_term),
          select: p
```

Use `like/2` for case-sensitive pattern matching and `ilike/2` for case-insensitive
matching. Wildcards `%` match any characters.

## Selecting Specific Fields

```elixir
# Select multiple fields
query = from p in MyApp.Post,
          select: {p.id, p.title}

MyApp.Repo.all(query)  # Returns [{1, "Title 1"}, {2, "Title 2"}]

# Select as map
query = from p in MyApp.Post,
          select: %{id: p.id, title: p.title}

MyApp.Repo.all(query)  # Returns [%{id: 1, title: "Title 1"}, ...]

# Select struct with specific fields
query = from p in MyApp.Post,
          select: struct(p, [:id, :title, :body])

MyApp.Repo.all(query)  # Returns Post structs with only selected fields loaded
```

Selecting specific fields instead of entire records improves query performance
by reducing data transfer and memory usage.

## Aggregation Functions

```elixir
# Count records
query = from p in MyApp.Post,
          select: count(p.id)

MyApp.Repo.one(query)  # Returns integer count

# Average
query = from p in MyApp.Post,
          select: avg(p.rating)

# Sum
query = from o in MyApp.Order,
          select: sum(o.total)

# Min and Max
query = from p in MyApp.Product,
          select: {min(p.price), max(p.price)}
```

Ecto supports standard SQL aggregation functions including `count/1`, `avg/1`,
`sum/1`, `min/1`, and `max/1`.

## Group By and Having

```elixir
query = from p in MyApp.Post,
          group_by: p.category,
          select: {p.category, count(p.id)}

MyApp.Repo.all(query)  # Returns [{"elixir", 10}, {"phoenix", 5}]

# With having clause
query = from p in MyApp.Post,
          group_by: p.category,
          having: count(p.id) > 5,
          select: {p.category, count(p.id)}
```

Use `group_by` to group results by field values and `having` to filter groups
based on aggregate values.

## Order By

```elixir
# Single field ascending
query = from p in MyApp.Post,
          order_by: p.published_at

# Single field descending
query = from p in MyApp.Post,
          order_by: [desc: p.published_at]

# Multiple fields
query = from p in MyApp.Post,
          order_by: [desc: p.published_at, asc: p.title]

# With nulls positioning
query = from p in MyApp.Post,
          order_by: [desc_nulls_last: p.published_at]
```

The `order_by` option controls result ordering. You can specify ascending or
descending order, multiple fields, and null positioning.

## Limit and Offset for Pagination

```elixir
# Simple limit
query = from p in MyApp.Post,
          limit: 10

# With offset for pagination
page = 2
per_page = 10

query = from p in MyApp.Post,
          order_by: [desc: p.published_at],
          limit: ^per_page,
          offset: ^((page - 1) * per_page)

MyApp.Repo.all(query)
```

Use `limit` and `offset` for pagination. Always include an `order_by` clause
to ensure consistent pagination results.

## Inner Join

```elixir
query = from p in MyApp.Post,
          join: c in MyApp.Comment,
          on: c.post_id == p.id,
          select: {p.title, c.body}

MyApp.Repo.all(query)
```

Inner joins return only records that have matching records in both tables. The
`on` clause specifies the join condition.

## Join with assoc Helper

```elixir
query = from p in MyApp.Post,
          join: c in assoc(p, :comments),
          select: {p, c}

MyApp.Repo.all(query)
```

The `assoc/2` helper uses the association definition from your schema, making
joins more maintainable and less error-prone than manually specifying foreign keys.

## Left Join

```elixir
query = from p in MyApp.Post,
          left_join: c in assoc(p, :comments),
          select: {p, c}

MyApp.Repo.all(query)
```

Left joins return all records from the left table (posts) even if there are