elixir 教程_了解Elixir的Ecto查询DSL：基础知识

elixir 教程

This article will take a look at the basics of querying with Ecto, a domain-specific language (DSL) for writing queries and interacting with databases in Elixir. This will involve going through joins, associations, aggregation functions, and so on.

本文将介绍使用Ecto进行查询的基础知识，Ecto是一种用于编写查询并与Elixir中的数据库进行交互的领域特定语言 (DSL)。 这将涉及通过联接，关联，聚合功能等。

A basic knowledge of Elixir is assumed, and knowing the fundamentals of Ecto beforehand will help too.

假定具备Elixir的基本知识 ，并且事先了解Ecto的基础知识也将有所帮助。

申请申请 (Ectoing Application)

All of the examples in this article series can be run against my demonstrative Ectoing application. I highly encourage you to set this up (as described below) and run all of the queries as you read along. This will help to solidify your understanding by playing around with the examples to see what works and what doesn’t work.

本系列文章中的所有示例都可以在我的演示性Ectoing应用程序上运行。 我强烈建议您进行设置(如下所述)并在阅读过程中运行所有查询。 通过遍历示例查看有效和无效的方法，这将有助于巩固您的理解。

Let’s quickly set up the application:

让我们快速设置应用程序：

git clone https://github.com/tpunt/ectoing
cd ectoing
mix deps.get

# don't forget to update the credentials in config/config.exs
mix ecto.create
mix ecto.migrate

# populate the database with some dummy data
mix run priv/repo/seeds.exs

(I’ve chosen to use MySQL for this. The examples throughout this article should work uniformly across all supported databases, so whilst the Mariaex dependency could be switched out to use another database, I would advise against this. This is because some examples in the next article will contain MySQL-dependent code.)

(我选择为此使用MySQL。本文中的示例应在所有受支持的数据库上统一工作，因此尽管可以将Mariaex依赖项切换为使用其他数据库，但我建议不要这样做 。这是因为其中的一些示例下一篇文章将包含与MySQL相关的代码。)

The database structure is as follows:

数据库结构如下：

基本查询 (Basic Querying)

Let’s start with some basic queries to get a feel for Ecto’s querying DSL.

让我们从一些基本查询开始，以了解Ecto的查询DSL。

Note that whilst all examples can be executed in the Elixir shell (via iex -S mix in the ectoing base directory), the Ecto.Query module will have to be imported first. This will make all of the querying DSL macros (such as from) available to us whilst working in the shell.

请注意，虽然所有示例都可以在Elixir Shell中执行(通过在基本目录中的ectoing iex -S mix )，但Ecto.Query模块必须首先导入。 这将使我们在外壳中工作时可以使用所有查询DSL宏(例如from )。

Let’s start with the most trivial of queries — selecting all users with their complete records:

让我们从最简单的查询开始-选择所有具有完整记录的用户：

SELECT * FROM users;

query = Ectoing.User

Ectoing.Repo.all query

(All examples will firstly show the SQL syntax, and then how this translates into the Ecto query syntax.)

(所有示例将首先显示SQL语法，然后将其转换为Ecto查询语法。)

To get the complete records for all users, we simply perform a query on the desired model (in this case, it’s Ectoing.User). This works because Ecto will, by default, return all of the fields defined in the corresponding model’s schema definition if its select clause is omitted. Ecto’s Repo.all/2 function is then used to execute the query, where it fetches a list of results (since we expect more than one result from the query). Let’s take a quick look at one of these returned records:

为了获得所有用户的完整记录，我们只需对所需模型进行查询(在本例中为Ectoing.User )。 这样做是因为外生将默认，如果它返回所有在相应型号的模式定义定义的字段select子句省略。 然后，使用ecto的Repo.all/2函数执行查询，并在其中获取结果列表(因为我们期望查询有多个结果)。 让我们快速看一下这些返回的记录之一：

[%Ectoing.User{__meta__: #Ecto.Schema.Metadata<:loaded>,
  firstname: "Thomas",
  friends_of: #Ecto.Association.NotLoaded<association :friends_of is not loaded>,
  friends_with: #Ecto.Association.NotLoaded<association :friends_with is not loaded>,
  id: 1,
  inserted_at: #Ecto.DateTime<2016-05-15T20:23:58Z>,
  messages: #Ecto.Association.NotLoaded<association :messages is not loaded>,
  surname: "Punt",
  updated_at: #Ecto.DateTime<2016-05-15T20:23:58Z>,
  username: "tpunt"},
  ...
]

A struct of type Ectoing.User has been returned, where members of the struct match the field names for the Ectoing.User model. Notably, we also have some associations (friend, friends, and messages) not loaded that have been embedded in there. We’ll be covering Ecto’s associations in more detail later, but for now just note that they are Ecto’s solution for managing the loading of foreign key relationships across models.

已返回类型为Ectoing.User的结构，其中该结构的成员与Ectoing.User模型的字段名称匹配。 值得注意的是，我们还嵌入了一些未加载的关联( friend ， friends和messages )。 稍后我们将更详细地介绍Ecto的关联，但现在请注意，它们是Ecto的用于管理跨模型的外键关系的加载的解决方案。

Quite often though, we won’t want to fetch the whole record. So let’s just select a user’s first name and last name:

但是，我们经常不想获取整个记录。 因此，我们只需选择用户的名字和姓氏即可：

SELECT firstname, surname FROM users;

query = from u in Ectoing.User,
  select: [u.firstname, u.surname]

Ectoing.Repo.all query

In the from macro, we specify the model (Ectoing.User) to select data from and use a convenience variable (u) to reference it. The select clause is then used to select the columns u.firstname and u.surname as a list, making the resulting value a list of two element lists:

在from宏中，我们指定模型( Ectoing.User )从中选择数据，并使用便利变量( u )进行引用。 然后，使用select子句选择列u.firstname和u.surname作为列表，使结果值成为两个元素列表的列表：

[["Thomas", "Punt"], ["Liam", "Mann"], ["John", "Doe"], ["Jane", "Doe"],
 ["Bruno", "Škvorc"]]

The select clause enables us to pattern match inside of it, so whilst square brackets were used above to return the columns as a list, we could quite easily have returned a list of tuples or a list of maps instead:

select子句使我们能够在其中进行模式匹配，因此，尽管在上面使用方括号将列作为列表返回，但我们可以很容易地返回元组列表或地图列表：

query = from u in Ectoing.User,
  select: {u.firstname, u.surname}

Ectoing.Repo.all query

# result
[{"Thomas", "Punt"}, {"Liam", "Mann"}, {"John", "Doe"}, {"Jane", "Doe"},
 {"Bruno", "Škvorc"}]

query = from u in Ectoing.User,
  select: %{firstname: u.firstname, surname: u.surname}

Ectoing.Repo.all query

# result
[%{firstname: "Thomas", surname: "Punt"}, %{firstname: "Liam", surname: "Mann"},
 %{firstname: "John", surname: "Doe"}, %{firstname: "Jane", surname: "Doe"},
 %{firstname: "Bruno", surname: "Škvorc"}]

查询API样式 (Querying API Styles)

So far, we’ve been using the keywords query syntax for our queries. This is generally the most prevalent syntax, but occasionally you’ll see Ecto’s alternative querying API syntax: the macro syntax. Let’s take a look at this alternative syntax by translating the above query to select the first names and surnames of all users:

到目前为止，我们一直在使用关键字查询语法进行查询。 通常，这是最流行的语法，但有时您会看到Ecto的替代查询API语法：宏语法。 让我们通过翻译上面的查询以选择所有用户的名字和姓氏来看看这种替代语法：

query = (Ectoing.User                   
|> select([u], [u.firstname, u.surname]))

Ectoing.Repo.all query

(The parentheses encapsulating the whole query aren’t needed, but have been included because they enable for the code to be easily copied and pasted directly into IEx.)

(不需要括住整个查询的括号，因为它们使代码可以轻松复制并直接粘贴到IEx中，所以已包括在内。)

This time, we pass the model as the first argument to select/3, where the second argument specifies the binding variable for the model (in this case, it’s u). The third argument selects the columns, which can (again) be pattern matched to return tuples or maps instead of lists.

这次，我们将模型作为第一个参数传递给select/3 ，其中第二个参数指定模型的绑定变量(在本例中为u )。 第三个参数选择列，可以(再次)进行模式匹配以返回元组或映射而不是列表。

Just so that we can get used to both syntax styles, I’ll demonstrate both from now on alongside the SQL code for each query.

为了使我们能够习惯两种语法样式，从现在开始，我将为每种查询在SQL代码旁边进行演示。

限制性查询和自定义结果集 (Restrictive Querying and Customizing Result Sets)

Almost always, we will want to select only a subset of records from the database. This can be done by a range of functions that closely resemble their clause counterparts in SQL. These include where (in combination with the comparison operators in and like (there is an ilike, though it has been deprecated in Ecto 2.0)), limit, offset, and distinct.

几乎总是，我们只想从数据库中选择记录的一个子集。 这可以通过一系列与SQL中的子句对应项非常相似的函数来完成。 这些包括where (与比较运算符组合in和like (有一个ilike ，尽管它已经在外生2.0被弃用))， limit ， offset ，和distinct 。

For example, we can select all users with surnames equal to “doe” with the following:

例如，我们可以通过以下方式选择所有姓氏等于“ doe”的用户：

SELECT * FROM users WHERE surname = "doe";

surname = "doe"

# Keywords query syntax
query = from u in Ectoing.User,
  where: u.surname == ^surname

# Macro syntax
query = (Ectoing.User
|> where([u], u.surname == ^surname))

Ectoing.Repo.all query

The above comparison is case-insensitive and returns back both of the John Doe and Jane Doe users. Notice the usage of the pin operator on the surname variable: this is to (explicitly) interpolate variables into the query. Such interpolated variables have their values automatically casted to the underlying column type defined in the model’s schema definition.

上面的比较不区分大小写，并且返回了John Doe和Jane Doe用户。 请注意在surname变量上使用pin运算符 ：这是(明确地)将变量插值到查询中。 此类内插变量的值会自动转换为模型的架构定义中定义的基础列类型。

Let’s try something a little more complex by selecting all distinct surnames, ordering them, and then limiting the result set:

通过选择所有不同的姓氏，对其进行排序，然后限制结果集，让我们尝试一些更复杂的事情：

SELECT DISTINCT surname FROM users LIMIT 3 ORDER BY surname;

# Keywords query syntax
query = from u in Ectoing.User,
  select: u.surname,
  distinct: true,
  limit: 3,
  order_by: u.surname

# Macro syntax
query = (Ectoing.User
|> select([u], u.surname)
|> distinct(true)
|> limit(3)
|> order_by([u], u.surname))

Ectoing.Repo.all query
# ["Doe", "Mann", "Punt"]

The distinct/3 function above will select distinct values according to the column(s) specified in the select/3 function. distinct/3 also allows for columns to be directly passed to it (i.e. distinct: u.surname), enabling for certain columns to be selected by distinctly, and then for alternative columns to be returned (via select/3). Since MySQL does not support the DISTINCT ON syntax, however, we are unable to do this when using the MySQL adapter (the Postgres adapter allows this, for example). The final order_by/3 then orders the result set (in ascending order, by default) according to the passed columns.

上面的distinct/3函数将根据select/3函数中指定的列选择不同的值。 distinct/3还允许将列直接传递给它(即distinct: u.surname )，从而使某些列可以被不同地选择，然后返回替代列(通过select/3 )。 但是，由于MySQL不支持DISTINCT ON语法，因此在使用MySQL适配器时，我们无法做到这一点(例如，Postgres适配器允许这样做)。 然后，最后的order_by/3根据传递的列对结果集进行排序(默认情况下以升序排列)。

For a complete list of operators and functions supported in Ecto’s querying API, check out its documentation. Also, for a complete list of literals that can be used in Ecto queries, see its query expressions documentation.

有关Ecto的查询API支持的运算符和功能的完整列表， 请查看其文档 。 另外，有关可在Ecto查询中使用的文字的完整列表， 请参见其查询表达式文档 。

聚合查询 (Aggregation Queries)

Ecto provides us with a number of aggregation functions that we’d typically find in SQL, including: group_by, having, count, avg, sum, min, and max.

Ecto为我们提供了许多通常在SQL中可以找到的聚合函数，包括： group_by ， having ， count ， avg ， sum ， min和max 。

Let’s try a couple of these out by selecting users who have an average rating of 4 or greater by their friends:

让我们通过选择平均得分为4或更高的用户来尝试其中的几个：

SELECT friend_id, avg(friend_rating) AS avg_rating
FROM friends
GROUP BY friend_id
    HAVING avg_rating >= 4
ORDER BY avg_rating DESC;

# Keywords query 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)]

# Macro syntax
query = (Ectoing.Friend
|> select([f], %{friend_id: f.friend_id, avg_rating: avg(f.friend_rating)})
|> group_by([f], f.friend_id)
|> having([f], avg(f.friend_rating) >= 4)
|> order_by([f], [desc: avg(f.friend_rating)]))

Ectoing.Repo.all query
# [%{avg_rating: #Decimal<4.0000>, friend_id: 3},
#  %{avg_rating: #Decimal<4.0000>, friend_id: 5}]

It’s a little less elegant from Ecto’s perspective, since we can’t alias the average rating column — requiring us to use the same column calculation three times in the query. But as with the previous examples, Ecto’s querying DSL maps very closely to raw SQL, which makes it quite easy to use.

从Ecto的角度来看，这有点不太优雅，因为我们不能为平均评级列取别名-要求我们在查询中使用三次相同的列计算。 但是，与前面的示例一样，Ecto的查询DSL与原始SQL的映射非常紧密，这使其非常易于使用。

结论 (Conclusion)

We’ve covered the absolute fundamentals of Ecto’s querying DSL in this article, showing how closely it maps to raw SQL. In the next article, Elixir’s Ecto Querying DSL: Beyond the Basics, we cover some more complex topics, including joins, composition queries, SQL fragment injection, loading associations, and query prefixing.

在本文中，我们已经介绍了Ecto查询DSL的绝对基础，并显示了它与原始SQL的映射关系。 在下一篇文章Elixir的Ecto Querying DSL：超越基础之外 ，我们涵盖了一些更复杂的主题，包括联接，组合查询，SQL片段注入，加载关联和查询前缀。

翻译自: https://www.sitepoint.com/understanding-elixirs-ecto-querying-dsl-the-basics/

elixir 教程