Elixir's Ecto Querying DSL: Beyond the Basics

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

Once again, a basic knowledge of Elixir is assumed, as well as the basics of Ecto, which I covered in An Introduction to Elixir’s Ecto Library.

Key Takeaways

• Ecto allows for query composition in Elixir, enabling developers to create reusable queries and combine them for DRYer and more maintainable code. This can be achieved using either the keywords query syntax or the macro syntax.
• Ecto provides the ability to handle table relationships (joins and associations) in the models. Associations, defined using the has_one/3, has_many/3, and belongs_to/3 macros, allow developers to handle table relationships implemented as foreign keys in the models.
• Ecto supports SQL fragment injection, a feature that allows for SQL code to be directly injected into a query using the fragment/1 function. This is useful when developers need to drop back down into raw SQL for operations not covered by Ecto’s functions.
• Ecto supports explicit casting and dynamic field access. Explicit casting allows developers to specify the type an expression should be cast to, while dynamic field access enables searching any field from a given table, making queries more general and versatile.

Query Composition

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

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

<span>SELECT id, username FROM users;
</span><span>SELECT id, username FROM users WHERE username LIKE "%tp%";
</span><span>SELECT id, username FROM users WHERE username LIKE "%tp%" LIMIT 10, 0;
</span>

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

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).

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.

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 has_one/3 and has_many/3 macros (for models containing other models), and the belongs_to/3 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:

<span>SELECT id, username FROM users;
</span><span>SELECT id, username FROM users WHERE username LIKE "%tp%";
</span><span>SELECT id, username FROM users WHERE username LIKE "%tp%" LIMIT 10, 0;
</span>

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

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

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

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:

has_many <span>:messages, Ectoing.Message
</span>

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

belongs_to <span>:user, Ectoing.User
</span>

<span># Ectoing.Message
</span>belongs_to <span>:user, Ectoing.User, foreign_key: some_other_fk_name
</span>

Returned value:

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

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 Repo.preload function:

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

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

<span>[%Ectoing.User{__meta__: #Ecto.Schema.Metadata<:loaded>,
</:loaded></span>  <span>firstname: <span>"Jane"</span>,
</span>  <span>friends_of: #Ecto.Association.NotLoaded<association :friends_of is not loaded>,
</association></span>  <span>friends_with: #Ecto.Association.NotLoaded<association :friends_with is not loaded>,
</association></span>  <span>id: 4,
</span>  <span>inserted_at: #Ecto.DateTime,
</span>  <span>messages: #Ecto.Association.NotLoaded<association :messages is not loaded>,
</association></span>  <span>surname: <span>"Doe"</span>,
</span>  <span>updated_at: #Ecto.DateTime,
</span>  <span>username: <span>"jane_doe"</span>},
</span> <span>%Ectoing.User{__meta__: #Ecto.Schema.Metadata<:loaded>,
</:loaded></span>  <span>firstname: <span>"Jane"</span>,
</span>  <span>friends_of: #Ecto.Association.NotLoaded<association :friends_of is not loaded>,
</association></span>  <span>friends_with: #Ecto.Association.NotLoaded<association :friends_with is not loaded>,
</association></span>  <span>id: 4,
</span>  <span>inserted_at: #Ecto.DateTime,
</span>  <span>messages: #Ecto.Association.NotLoaded<association :messages is not loaded>,
</association></span>  <span>surname: <span>"Doe"</span>,
</span>  <span>updated_at: #Ecto.DateTime,
</span>  <span>username: <span>"jane_doe"</span>}]
</span>

results <span>= Ectoing.Repo.all query
</span>Ectoing<span>.Repo.preload results, :messages
</span>

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

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

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.

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 Working with Ecto associations and embeds.

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 fragment/1 function, enabling for SQL code to be directly injected into a query.

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

<span>SELECT id, username FROM users;
</span><span>SELECT id, username FROM users WHERE username LIKE "%tp%";
</span><span>SELECT id, username FROM users WHERE username LIKE "%tp%" LIMIT 10, 0;
</span>

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

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

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.

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.

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 type/2 function to specify the expression and the type it should be cast to.

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 Ecto.Schema.timestamps 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:

has_many <span>:messages, Ectoing.Message
</span>

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:

belongs_to <span>:user, Ectoing.User
</span>

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.

Dynamic Field Access

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

<span>SELECT id, username FROM users;
</span><span>SELECT id, username FROM users WHERE username LIKE "%tp%";
</span><span>SELECT id, username FROM users WHERE username LIKE "%tp%" LIMIT 10, 0;
</span>

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:

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

The field/2 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 previous article 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!

Frequently Asked Questions (FAQs) about Elixir’s Ecto Querying DSL

What is Elixir’s Ecto Querying DSL and why is it important?

Elixir’s Ecto Querying DSL (Domain Specific Language) is a powerful tool for interacting with databases. It provides a way to write queries in a syntax that is close to SQL, but with the added benefits of compile-time safety, better integration with Elixir code, and potential for abstraction and code reuse. It’s important because it allows developers to write complex queries in a more readable and maintainable way, reducing the likelihood of errors and making the code easier to understand and modify.

How does Ecto handle associations between tables?

Ecto provides a way to define associations between tables using the has_many, has_one, and belongs_to macros. These associations allow you to query related data in a convenient and efficient way. For example, if you have a User schema and each user has many Posts, you can retrieve all posts for a user with a simple query.

Can I use Ecto to perform complex queries involving joins, subqueries, and aggregations?

Yes, Ecto supports a wide range of query operations, including joins, subqueries, and aggregations. You can use the join keyword to join tables, the from keyword to create subqueries, and functions like sum, avg, min, and max to perform aggregations. This makes Ecto a powerful tool for querying data in complex ways.

How does Ecto handle transactions?

Ecto provides a Repo.transaction function that allows you to execute multiple operations in a single transaction. If any operation fails, all changes made within the transaction are rolled back. This ensures data consistency and integrity.

Can I use Ecto with databases other than PostgreSQL?

While Ecto was initially designed to work with PostgreSQL, it now supports other databases as well, including MySQL and SQLite. You can specify the database type when setting up your Ecto repository.

How does Ecto handle migrations?

Ecto provides a robust migration system that allows you to create, modify, and delete database tables in a controlled and reversible way. You can generate migration files using mix tasks, and then define the changes in the migration file using Ecto’s DSL.

Can I use Ecto to validate data before inserting or updating it in the database?

Yes, Ecto provides a changeset function that allows you to validate data before it’s inserted or updated in the database. You can define validation rules in your schema, and then use the changeset function to apply these rules to the data.

How does Ecto handle database connections?

Ecto uses a connection pool to manage database connections. This allows it to efficiently handle multiple concurrent queries, ensuring that your application remains responsive even under heavy load.

Can I use Ecto to perform raw SQL queries?

Yes, while Ecto’s DSL provides a high-level, abstracted way to write queries, you can also use the Ecto.Adapters.SQL.query function to execute raw SQL queries if needed.

How does Ecto handle schemaless queries?

Ecto provides a Ecto.Query.API.dynamic function that allows you to build queries dynamically, without a predefined schema. This can be useful when you need to build queries based on user input or other runtime data.

The above is the detailed content of Elixir's Ecto Querying DSL: Beyond the Basics. For more information, please follow other related articles on the PHP Chinese website!