Ecto Querying in Elixir: A Beginner's Guide
This article introduces Ecto, Elixir's query language, guiding you through basic querying techniques. We'll cover joins, associations, aggregation, and more, assuming a basic understanding of Elixir and Ecto fundamentals.
Key Concepts:
- Ecto is Elixir's DSL for database interactions, closely mirroring SQL.
- It offers keyword and macro query syntaxes, functionally equivalent but differing in style.
- Core functions like
where
,limit
,offset
, anddistinct
allow for precise data selection. - Aggregation functions (
group_by
,having
,count
,avg
,sum
,min
,max
) enable complex data calculations.
Getting Started with the ectoing
Application:
The examples use the ectoing
application. Clone, set up, and migrate the database as follows:
git clone https://github.com/tpunt/ectoing cd ectoing mix deps.get # Update credentials in config/config.exs mix ecto.create mix ecto.migrate mix run priv/repo/seeds.exs
(MySQL is used here; while adaptable to other databases, some later examples might be MySQL-specific.)
The database schema:
Basic Queries:
Let's begin with simple queries. Remember to import Ecto.Query
in the Elixir shell (iex -S mix
).
Fetching all users:
SQL:
SELECT * FROM users;
Ecto (Keyword Syntax):
query = Ectoing.User Ectoing.Repo.all(query)
Ecto (Macro Syntax):
query = Ectoing.User |> Ecto.Query.all() Ectoing.Repo.all(query)
Selecting specific fields (firstname, surname):
SQL:
SELECT firstname, surname FROM users;
Ecto (Keyword Syntax):
query = from u in Ectoing.User, select: [u.firstname, u.surname] Ectoing.Repo.all(query)
Ecto (Macro Syntax):
query = Ectoing.User |> Ecto.Query.select([u], [u.firstname, u.surname]) Ectoing.Repo.all(query)
The results will be lists of lists, tuples, or maps depending on the select
clause structure.
Filtering and Customizing Results:
Let's refine queries to select subsets of data.
Selecting users with surname "doe":
SQL:
SELECT * FROM users WHERE surname = "doe";
Ecto (Keyword Syntax):
surname = "doe" query = from u in Ectoing.User, where: u.surname == ^surname Ectoing.Repo.all(query)
Ecto (Macro Syntax):
surname = "doe" query = Ectoing.User |> Ecto.Query.where([u], u.surname == ^surname) Ectoing.Repo.all(query)
Selecting distinct surnames, ordered, and limited:
SQL:
SELECT DISTINCT surname FROM users LIMIT 3 ORDER BY surname;
Ecto (Keyword Syntax):
query = from u in Ectoing.User, select: u.surname, distinct: true, limit: 3, order_by: u.surname Ectoing.Repo.all(query)
Ecto (Macro Syntax):
query = Ectoing.User |> Ecto.Query.select([u], u.surname) |> Ecto.Query.distinct(true) |> Ecto.Query.limit(3) |> Ecto.Query.order_by([u], u.surname) Ectoing.Repo.all(query)
Aggregation Queries:
Ecto supports aggregation functions.
Finding users with an average friend rating of 4 or greater:
SQL:
SELECT friend_id, avg(friend_rating) AS avg_rating FROM friends GROUP BY friend_id HAVING avg_rating >= 4 ORDER BY avg_rating DESC;
Ecto (Keyword Syntax):
query = from f in Ectoing.Friend, select: %{friend_id: f.friend_id, avg_rating: avg(f.friend_rating)}, group_by: f.friend_id, having: avg(f.friend_rating) >= 4, order_by: [desc: avg(f.friend_rating)] Ectoing.Repo.all(query)
Ecto (Macro Syntax): (Similar structure to keyword syntax, using pipe operator)
Conclusion:
This introduction covers Ecto's querying basics. The next steps involve exploring joins, complex queries, and advanced techniques. Refer to the Ecto documentation for a comprehensive guide.
The above is the detailed content of Understanding Elixir's Ecto Querying DSL: The Basics. For more information, please follow other related articles on the PHP Chinese website!

This tutorial guides you through building a serverless image processing pipeline using AWS services. We'll create a Next.js frontend deployed on an ECS Fargate cluster, interacting with an API Gateway, Lambda functions, S3 buckets, and DynamoDB. Th

This pilot program, a collaboration between the CNCF (Cloud Native Computing Foundation), Ampere Computing, Equinix Metal, and Actuated, streamlines arm64 CI/CD for CNCF GitHub projects. The initiative addresses security concerns and performance lim

This Go-based network vulnerability scanner efficiently identifies potential security weaknesses. It leverages Go's concurrency features for speed and includes service detection and vulnerability matching. Let's explore its capabilities and ethical


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

Atom editor mac version download
The most popular open source editor

Dreamweaver Mac version
Visual web development tools

PhpStorm Mac version
The latest (2018.2.1) professional PHP integrated development tool

mPDF
mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),

EditPlus Chinese cracked version
Small size, syntax highlighting, does not support code prompt function