Complex queries with Ecto

Ecto is a powerful library for working with databases in Elixir. One of its most useful features is the ability to construct complex queries using a simple, expressive syntax.

One way to build complex queries in Ecto is to use the where function, which allows you to specify conditions that must be met for a record to be included in the results. For example, you might use where to find all users who are over the age of 18 and have a certain role:

import Ecto.Query

query = from u in User,
  where: u.age > 18 and u.role == "admin",
  select: u

users = Repo.all(query)

You can also use or to combine multiple conditions, like this:

import Ecto.Query

query = from u in User,
  where: u.age > 18 or u.role == "admin",
  select: u

users = Repo.all(query)

Another useful function is group_by, which allows you to group records by a certain field. For example, you might use group_by to count the number of users in each role:

import Ecto.Query

query = from u in User,
  group_by: u.role,
  select: {u.role, count(u.id)}

role_counts = Repo.all(query)

You can also use order_by to sort the results of a query. For example, you might use order_by to sort users by their age:

import Ecto.Query

query = from u in User,
  order_by: u.age,
  select: u

users = Repo.all(query)

Because they are all functions you can pipe them to build one single query:

import Ecto.Query

query = User
  |> where([u], u.age > 18)
  |> where([u], u.role == "admin")
  |> group_by([u], u.role)
  |> select([u], {u.role, count(u.id)})

users = Repo.all(query)

Notice how, in the previous example, we divided our where clause into 2 functions, you might find this way more readable when building your query in this case it's important to know that ecto will create the where clause using and.

One another important thing is that you can start your query with a Ecto Schema, like User, it will consider it the same as from(u in User).

These are just a few examples of the complex queries that you can build with Ecto. With its simple, expressive syntax, Ecto makes it easy to build powerful queries that can retrieve exactly the data you're looking for.

For more ecto built in functions you can reach out to its documentation.