search
HomeTechnology peripheralsIt IndustryUnderstanding Elixir's Ecto Querying DSL: The Basics

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, and distinct 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:

Understanding Elixir's Ecto Querying DSL: The Basics

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!

Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Serverless Image Processing Pipeline with AWS ECS and LambdaServerless Image Processing Pipeline with AWS ECS and LambdaApr 18, 2025 am 08:28 AM

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

CNCF Arm64 Pilot: Impact and InsightsCNCF Arm64 Pilot: Impact and InsightsApr 15, 2025 am 08:27 AM

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

Building a Network Vulnerability Scanner with GoBuilding a Network Vulnerability Scanner with GoApr 01, 2025 am 08:27 AM

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

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

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

Hot Tools

Atom editor mac version download

Atom editor mac version download

The most popular open source editor

Dreamweaver Mac version

Dreamweaver Mac version

Visual web development tools

PhpStorm Mac version

PhpStorm Mac version

The latest (2018.2.1) professional PHP integrated development tool

mPDF

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

EditPlus Chinese cracked version

Small size, syntax highlighting, does not support code prompt function