Home >Technology peripherals >It Industry >Understanding Elixir's Ecto Querying DSL: The Basics

Understanding Elixir's Ecto Querying DSL: The Basics

William Shakespeare
William ShakespeareOriginal
2025-02-18 12:03:101077browse

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