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.

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:

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 = 0
username = "%tp%"

# Keywords query syntax
get_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 syntax
get_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).

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.

Continue reading %Elixir’s Ecto Querying DSL: Beyond the Basics%

by Thomas Punt via SitePoint