elixir 教程

This article builds on the fundamentals of Ecto that I covered in . I’ll now explore Ecto’s more advanced features, including query composition, joins and associations, SQL fragment injection, explicit casting, and dynamic field access.

本文基于我在 。 现在，我将探讨Ecto的更高级功能，包括查询组合，联接和关联，SQL片段注入，显式转换和动态字段访问。

Once again, a is assumed, as well as the basics of Ecto, which I covered in .

再次假设的以及Ecto的基础知识，我在 。

查询组成 (Query Composition)

Separate queries in Ecto can be combined together, allowing for reusable queries to be created.

Ecto中的独立查询可以组合在一起，从而允许创建可重用的查询。

For example, let’s see how we can create three separate queries and combine them together to achieve DRYer and more reusable code:

例如，让我们看看如何创建三个单独的查询并将它们组合在一起以实现DRYer和更可重用的代码：

SELECT id, username FROM users;SELECT id, username FROM users WHERE username LIKE "%tp%";SELECT id, username FROM users WHERE username LIKE "%tp%" LIMIT 10, 0;

offset = 0username = "%tp%"# Keywords query syntaxget_users_overview = from u in Ectoing.User,  select: [u.id, u.username]search_by_username = from u in get_users_overview,  where: like(u.username, ^username)paginate_query = from search_by_username,  limit: 10,  offset: ^offset# Macro syntaxget_users_overview = (Ectoing.User|> select([u], [u.id, u.username]))search_by_username = (get_users_overview|> where([u], like(u.username, ^username)))paginate_query = (search_by_username|> limit(10)|> offset(^offset))Ectoing.Repo.all paginate_query

The SQL version is quite repetitive, but the Ecto version on the other hand is quite DRY. The first query (get_users_overview) is just a generic query to retrieve basic user information. The second query (search_by_username) builds off the first by filtering usernames according to some username we are searching for. The third query (paginate_query) builds off of the second, where it limits the results and fetches them from a particular offset (to provide the basis for pagination).

SQL版本具有很强的重复性，但是Ecto版本却相当干燥。 第一个查询( get_users_overview )只是用于检索基本用户信息的通用查询。 第二个查询( search_by_username )通过根据我们要搜索的用户名过滤用户名来构建第一个查询。 第三个查询( paginate_query )是在第二个查询的基础上构建的，它会限制结果并从特定偏移量获取结果(以提供分页的基础)。

It’s not hard to imagine that all of the above three queries could be used together to provide search results for when a particular user is searched for. Each may also be used in conjunction with other queries to perform other application needs too, all without unnecessarily repeating parts of the query throughout the codebase.

不难想象，当搜索特定用户时，可以将上述三个查询全部一起使用来提供搜索结果。 每个查询也可以与其他查询结合使用，以执行其他应用程序需求，而无需在整个代码库中不必要地重复查询的各个部分。

加盟与协会 (Joins and Associations)

Joins are pretty fundamental when querying, and yet we’re only just covering them now. The reason for this is because learning about joins in Ecto alone is not useful: we need to know about associations as well. Whilst these are not difficult to learn about, they’re not quite as trivial as the other topics covered so far.

在查询中，联接是非常基本的，但是我们现在仅涉及它们。 这样做的原因是，仅了解Ecto中的联接是没有用的：我们还需要了解关联。 尽管不难学习，但它们并不像到目前为止涵盖的其他主题那么琐碎。

Simply put, associations enable developers to handle table relationships (implemented as foreign keys) in the models. They are defined in the schemas for each model using the and macros (for models containing other models), and the macro (for models that are apart of other models — those that have the foreign keys).

简而言之，关联使开发人员能够处理模型中的表关系(实现为外键)。 使用和宏(适用于包含其他模型的模型)，并使用宏(适用于与其他模型分开的模型-具有外键的模型)在每个模型的模式中定义它们。

Looking at our Ectoing application, we can see one example of an association between the Ectoing.User model and the Ectoing.Message model. The schema defined in Ectoing.User defines the following association:

查看我们的Ectoing应用程序，我们可以看到Ectoing.User模型与Ectoing.Message模型之间的关联的一个示例。 Ectoing.User定义的架构定义了以下关联：

has_many :messages, Ectoing.Message

We can see that one user has many messages (Ectoing.Message), and we’re calling this association :messages.

我们可以看到一个用户有很多消息( Ectoing.Message )，我们称这种关联为:messages 。

In the Ectoing.Message model, we define the following association relationship:

在Ectoing.Message模型中，我们定义以下关联关系：

belongs_to :user, Ectoing.User

Here, we’re saying that the model, Ectoing.Message, belongs to the Ectoing.User model. We have also named the association as :user. By default, Ecto will append an _id onto the belongs_to association name and use that as the foreign key name (so here, it would be :user_id). This default behavior can be overridden by manually specifying the foreign key name by specifying the foreign_key option. For example:

在这里，我们说，该模型， Ectoing.Message ，属于Ectoing.User模型。 我们还将该关联命名为:user 。 默认情况下，Ecto会将一个_id附加到belongs_to关联名称上，并将其用作外键名称(因此，此处为:user_id )。 通过指定foreign_key选项手动指定外键名称，可以覆盖此默认行为。 例如：

# Ectoing.Messagebelongs_to :user, Ectoing.User, foreign_key: some_other_fk_name

Let’s now take a look at a simple query that uses a join to fetch a user and their messages:

现在，让我们看一个简单的查询，该查询使用联接来获取用户及其消息：

SELECT * FROM users u INNER JOIN messages m ON u.id = m.user_id WHERE u.id = 4;

# Keywords query syntaxquery = from u in Ectoing.User,  join: m in Ectoing.Message, on: u.id == m.user_id,  where: u.id == 4# Macro syntaxquery = (Ectoing.User|> join(:inner, [u], m in Ectoing.Message, u.id == m.user_id)|> where([u], u.id == 4))Ectoing.Repo.all query

Returned value:

返回值：

[%Ectoing.User{__meta__: #Ecto.Schema.Metadata<:loaded>,  firstname: "Jane",  friends_of: #Ecto.Association.NotLoaded
    
     ,  friends_with: #Ecto.Association.NotLoaded
     
      ,  id: 4,  inserted_at: #Ecto.DateTime<2016-05-15T20:23:58Z>,  messages: #Ecto.Association.NotLoaded
      
       ,  surname: "Doe",  updated_at: #Ecto.DateTime<2016-05-15T20:23:58Z>,  username: "jane_doe"}, %Ectoing.User{__meta__: #Ecto.Schema.Metadata<:loaded>,  firstname: "Jane",  friends_of: #Ecto.Association.NotLoaded
       
        ,  friends_with: #Ecto.Association.NotLoaded
        
         ,  id: 4,  inserted_at: #Ecto.DateTime<2016-05-15T20:23:58Z>,  messages: #Ecto.Association.NotLoaded
         
          , surname: "Doe", updated_at: #Ecto.DateTime<2016-05-15T20:23:58Z>, username: "jane_doe"}]
         
        
       
      
     
    

Noticeably, we have a number of unloaded associations, including the :messages association. Loading this association can be done in one of two ways: from the result set of a query or from within the query itself. Loading associations from a result set can be done with the function:

显然，我们有许多卸载的关联，包括:messages关联。 可以通过以下两种方式之一加载此关联：从查询的结果集或从查询本身内部。 可以使用函数从结果集中加载关联：

results = Ectoing.Repo.all queryEctoing.Repo.preload results, :messages

Loading associations from within a query can be done using a combination of the assoc and preload functions:

可以使用assoc和preload函数的组合从查询中加载关联：

SELECT * FROM users u INNER JOIN messages m ON u.id = m.user_id WHERE u.id = 4;

# Keywords query syntaxquery = from u in Ectoing.User,  join: m in assoc(u, :messages),  where: u.id == 4,  preload: [messages: m]# Macro syntaxquery = (Ectoing.User|> join(:inner, [u], m in assoc(u, :messages))|> where([u], u.id == 4)|> preload([u, m], [messages: m]))Ectoing.Repo.all query

Now, we have the messages association loaded in the result:

现在，我们在结果中加载了消息关联：

[%Ectoing.User{__meta__: #Ecto.Schema.Metadata<:loaded>,  firstname: "Jane",  friends_of: #Ecto.Association.NotLoaded
    
     ,  friends_with: #Ecto.Association.NotLoaded
     
      ,  id: 4,  inserted_at: #Ecto.DateTime<2016-05-15T20:23:58Z>,  messages: [%Ectoing.Message{__meta__: #Ecto.Schema.Metadata<:loaded>,    id: 5,    inserted_at: #Ecto.DateTime<2016-05-15T20:23:58Z>,    message_body: "Message 5",    updated_at: #Ecto.DateTime<2016-05-15T20:23:58Z>,    user: #Ecto.Association.NotLoaded
      
       ,    user_id: 4},   %Ectoing.Message{__meta__: #Ecto.Schema.Metadata<:loaded>,    id: 6,    inserted_at: #Ecto.DateTime<2016-05-15T20:23:58Z>,    message_body: "Message 6",    updated_at: #Ecto.DateTime<2016-05-15T20:23:58Z>,    user: #Ecto.Association.NotLoaded
       
        ,    user_id: 4}],  surname: "Doe",  updated_at: #Ecto.DateTime<2016-05-15T20:23:58Z>,  username: "jane_doe"}]
       
      
     
    

Associations implicitly join on the primary key and foreign key columns for us, and so we don’t have to specify an :on clause. From the above, we can also see that when it comes to preloading associations, they aren’t lazily loaded. Associations must be explicitly loaded if they are wanted.

关联对我们隐式地联接在主键和外键列上，因此我们不必指定:on子句。 从上面的内容中，我们还可以看到，在涉及到预加载关联时，并不会延迟加载它们。 如果需要关联，则必须显式加载它们。

Because this article specifically focuses on Ecto’s querying DSL, we won’t cover the inserting, updating, or deleting of associations here. For more information on this, check out the blog post .

因为本文专门针对Ecto的查询DSL，所以在此我们将不介绍插入，更新或删除关联。 有关此的更多信息，请查看博客文章 。

SQL片段注入 (SQL Fragment Injection)

Whilst Ecto provides us with a lot of functionality, it only provides functions for common operations in SQL (it doesn’t aim to emulate the whole SQL language). When we need to drop back down into raw SQL, we can use the function, enabling for SQL code to be directly injected into a query.

尽管Ecto为我们提供了许多功能，但它仅提供了SQL中常见操作的功能(其目的不是模仿整个SQL语言)。 当我们需要退回到原始SQL时，可以使用函数，使SQL代码可以直接注入查询中。

For example, let’s perform a case-sensitive search on the username field:

例如，让我们在用户名字段上执行区分大小写的搜索：

SELECT username FROM users WHERE username LIKE BINARY '%doe';

username = "%doe"# Keywords query syntaxquery = from u in Ectoing.User,  select: u.username,  where: fragment("? LIKE BINARY ?", u.username, ^username)# Macro syntaxquery = (Ectoing.User|> select([u], u.username)|> where([u], fragment("? LIKE BINARY ?", u.username, ^username)))Ectoing.Repo.all query

(The above contains MySQL-specific SQL. If you’re using another database, then this will not work for you.)

(以上包含MySQL特定SQL。如果您使用的是其他数据库，则此方法对您不起作用。)

The fragment/1 function takes the SQL code as a string that we’d like to inject as the first parameter. It enables for columns and values to be bound to the SQL code fragment. This is done via placeholders (as question marks) in the string, with subsequent arguments passed to fragment being bound to each placeholder respectively.

fragment/1函数将SQL代码作为我们要插入的字符串作为第一个参数。 它使列和值可以绑定到SQL代码片段。 这是通过字符串中的占位符(作为问号)完成的，传递给fragment后续参数分别绑定到每个占位符。

显式铸造 (Explicit Casting)

Another way Ecto uses the models’ schema definitions is by automatically casting interpolated expressions within queries to the respective field types defined in the schema. These interpolated expressions are cast to the type of the field that they are being compared to. For example, if we have a query fragment such as u.username > ^username, where u.username is defined as field :username, :string in the schema, the username variable will automatically be cast to a string by Ecto.

Ecto使用模型的模式定义的另一种方法是通过将查询中的插值表达式自动转换为模式中定义的各个字段类型。 这些内插表达式将转换为与其进行比较的字段的类型。 例如，如果我们有一个查询片段，例如u.username > ^username ，其中在模式中u.username被定义为field :username, :string ，Ecto将自动将username变量转换为字符串。

Sometimes, however, we don’t always want Ecto to cast interpolated expressions to the defined field types. And other times, Ecto will not be able to infer the type to cast an expression to (typically, this is when fragments of SQL code are involved). In both instances, we can use the function to specify the expression and the type it should be cast to.

但是，有时我们并不总是希望Ecto将插值表达式转换为已定义的字段类型。 在其他时候，Ecto将无法推断类型以将表达式强制转换为对象(通常是在涉及SQL代码片段的情况下)。 在这两种情况下，我们都可以使用函数来指定表达式及其应转换为的类型。

Let’s take the first case of wanting to cast an expression to another type, since this is the more interesting scenario. In our Ectoing application, we have used the macro to add two extra fields to each of our tables: updated_at and inserted_at. The macro, by default, sets type of these fields to have a type of Ecto.DateTime. Now, if we’d like to see how many users have registered in the current month, we could use a simple query like the following:

让我们来考虑将表达式转换为另一种类型的第一种情况，因为这是更有趣的情况。 在我们的Ectoing的应用程序，我们使用了宏添加两个额外的字段我们每一个表： updated_at和inserted_at 。 默认情况下，该宏将这些字段的类型设置为Ecto.DateTime的类型。 现在，如果我们想查看当月有多少用户注册，可以使用类似以下的简单查询：

Ectoing.Repo.all from u in Ectoing.User,                   select: count(u.id),                                       where: u.inserted_at >= ^Ecto.Date.from_erl({2016, 05, 01})

This will, however, give us a Ecto.CastError, since an Ecto.Date struct cannot be cast to an Ecto.DateTime struct (since we’re comparing the interpolated Ecto.Date expressions to a field of type Ecto.DateTime). In this case, we could either build a Ecto.DateTime struct, or we could specify to Ecto that we’d like to cast the expression to Ecto.Date instead of Ecto.DateTime:

但是，这将给我们一个Ecto.CastError ，因为Ecto.Date结构不能转换为Ecto.DateTime结构(因为我们正在将插值的Ecto.Date表达式与Ecto.DateTime类型的字段进行Ecto.DateTime )。 在这种情况下，我们可以构建Ecto.DateTime结构，也可以向Ecto指定将表达式转换为Ecto.Date而不是Ecto.DateTime ：

Ectoing.Repo.all from u in Ectoing.User,                   select: count(u.id),                                       where: u.inserted_at >= type(^Ecto.Date.from_erl({2016, 05, 01}), Ecto.Date)

Now, Ecto happily accepts the query. After the cast operation, it then translates the interpolated Ecto.Date expression to the underlying :date type, which then lets the underlying database (MySQL, in this case) handle the comparison between a date and a datetime.

现在，Ecto愉快地接受了查询。 Ecto.Date转换操作之后，它会将插值的Ecto.Date表达式转换为基础:date类型，然后，基础数据库(在本例中为MySQL)可以处理日期和日期时间之间的比较。

动态现场访问 (Dynamic Field Access)

Let’s go back to our example from composing queries together, where we performed a username search:

让我们回到组合查询的示例中，我们执行了用户名搜索：

search_by_username = from u in get_users_overview,  where: like(u.username, ^username)

Like the pagination query that came after it, we can generalize this query too, so that it can search any field from a given table. This can be done by performing a dynamic field access:

像其后的分页查询一样，我们也可以对该查询进行一般化，以便它可以搜索给定表中的任何字段。 这可以通过执行动态字段访问来完成：

query = Ectoing.Userquery_by_field = :usernameusername = "%tp%"# Keywords query syntaxsearch_by_field = from table in query,  where: like(field(table, ^query_by_field), ^username)# Macro syntaxsearch_by_field = (query|> where([table], like(field(table, ^query_by_field), ^username)))Ectoing.Repo.all search_by_field

The function is used for when a field needs to be specified dynamically. Its first argument is the table of the field to be accessed, and the second argument is the field’s name itself, specified as an atom. Using a general query like the above, we can encapsulate it within a function and use parameters to search for any given field from the table specified in the given query.

函数用于需要动态指定字段时。 它的第一个参数是要访问的字段的表，第二个参数是字段的名称本身，指定为原子。 使用上述类似的通用查询，我们可以将其封装在函数中，并使用参数从给定查询中指定的表中搜索任何给定字段。

结论 (Conclusion)

In both this and my on Ecto’s querying DSL, we’ve covered quite a lot of what it’s capable of. The features mentioned should cover the vast majority of cases encountered when using Ecto within applications. But there are still some topics that haven’t been covered (such as query prefixing). There’s also all of the new upcoming features in Ecto’s much anticipated 2.0 release, including sub queries, aggregation queries, and many-to-many associations. These, as well as other features not specific to Ecto’s querying DSL, will be covered in future articles — so stay tuned!

在本篇以及我关于Ecto的查询DSL的 ，我们都介绍了它的功能。 提到的功能应涵盖在应用程序中使用Ecto时遇到的绝大多数情况。 但是，仍有一些主题尚未涵盖(例如查询前缀)。 Ecto备受期待的2.0版本中还包含所有即将推出的新功能，包括子查询，聚合查询和多对多关联。 这些以及其他并非Ecto的查询DSL特有的功能，将在以后的文章中介绍-请继续关注！

翻译自:

elixir 教程