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

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 = from u in MyApp.User,
          where: u.age > 18,
          select: u.name

MyApp.Repo.all(query)

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

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)

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)

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

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

undefined

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

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

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

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

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

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

MyApp.Repo.all(query)

search_term = "%elixir%"

query = from p in MyApp.Post,
          where: like(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.

undefined

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

undefined

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

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}]

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

undefined

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

undefined

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

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)

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

MyApp.Repo.all(query)

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

MyApp.Repo.all(query)

undefined

Preloading fetches associated data efficiently, preventing N+1 query problems.
Separate query preloading is simpler but may require more database round trips.

query = from p in MyApp.Post,
          join: c in assoc(p, :comments),
          where: c.published_at > p.updated_at,
          preload: [comments: c]

MyApp.Repo.all(query)

query = from p in MyApp.Post,
          join: c in assoc(p, :comments),
          join: l in assoc(c, :likes),
          where: l.inserted_at > c.updated_at,
          preload: [:author, comments: {c, likes: l}]

MyApp.Repo.all(query)

posts = MyApp.Repo.all(MyApp.Post)
posts_with_comments = MyApp.Repo.preload(posts, :comments)

The `preload/2` function can preload associations after fetching records. You
can also customize the preload query for fine-grained control.

undefined

Subqueries allow using the result of one query as input to another, enabling
complex analytical queries.

undefined

The `fragment/1` function allows embedding raw SQL in queries when Ecto's DSL
doesn't support a specific database feature. Use sparingly as it reduces portability.

query = from p in MyApp.Post,
          hints: ["USE INDEX FOO"],
          where: p.title == "title"

Query hints provide database-specific optimization instructions like index usage
or table sampling. Hints are database-specific and may not be portable.

defmodule MyApp.PostQueries do
  import Ecto.Query

  def filter(query \\ MyApp.Post, filters) do
    query
    |> filter_by_category(filters[:category])
    |> filter_by_published(filters[:published])
    |> filter_by_search(filters[:search])
  end

  defp filter_by_category(query, nil), do: query
  defp filter_by_category(query, category) do
    from p in query, where: p.category == ^category
  end

  defp filter_by_published(query, nil), do: query
  defp filter_by_published(query, published) do
    from p in query, where: p.published == ^published
  end

  defp filter_by_search(query, nil), do: query
  defp filter_by_search(query, search) do
    from p in query, where: ilike(p.title, ^"%#{search}%")
  end
end

Building queries dynamically allows handling optional filters and complex search
criteria. Pattern matching on nil values keeps the code clean and readable.

defmodule MyApp.PostQueries do
  import Ecto.Query

  def search(filters) do
    MyApp.Post
    |> where(^build_where_clause(filters))
    |> MyApp.Repo.all()
  end

  defp build_where_clause(filters) do
    Enum.reduce(filters, dynamic(true), fn
      {:category, value}, dynamic ->
        dynamic([p], ^dynamic and p.category == ^value)

      {:published, value}, dynamic ->
        dynamic([p], ^dynamic and p.published == ^value)

      {:min_rating, value}, dynamic ->
        dynamic([p], ^dynamic and p.rating >= ^value)

      _, dynamic ->
        dynamic
    end)
  end
end

undefined

The `distinct` option removes duplicate rows from results. You can specify which
fields to use for determining uniqueness.

posts_query = from p in MyApp.Post,
                where: p.published == true,
                select: %{type: "post", title: p.title}

pages_query = from p in MyApp.Page,
                where: p.active == true,
                select: %{type: "page", title: p.title}

Union combines results from multiple queries. Use `union/2` to remove duplicates
or `union_all/2` to keep them.

undefined

Locking prevents race conditions in concurrent operations. Pessimistic locking
uses database locks, while optimistic locking uses version fields.

defp newest_records(parent_ids, assoc, n) do
  %{related_key: related_key, queryable: queryable} = assoc

  squery = from q in queryable,
             where: field(q, ^related_key) == parent_as(:parent_ids).id,
             order_by: {:desc, :created_at},
             limit: ^n

  query = from f in fragment("SELECT id from UNNEST(?::int[]) AS id", ^parent_ids),
             as: :parent_ids,
             inner_lateral_join: s in subquery(squery),
             on: true,
             select: s

  MyApp.Repo.all(query)
end

query = from p in MyApp.Post, as: :posts
query = from [posts: p] in query,
          join: c in assoc(p, :comments), as: :comments
query = from [posts: p, comments: c] in query,
          where: c.score > 10,
          select: {p.title, c.body}