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:

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!