Powering Elixir with better Ecto queries

If you have worked with Phoenix, the go-to framework for devs in Elixir, chances are you have worked with Ecto. While the documentation is crisp, it needs more specific use cases. In this blog, we will be sharing a few of those.

Ecto is a database wrapper and an ORM (Object Relational Mapper) for the Elixir programming language. It allows for efficient querying and manipulation of databases using Elixir code.

Using fragments to count with conditions

We will be using the Article schema for the following example.

Article
---------------
title: text
body: text
edits: int
pages: int
created_at: timestamp
modified_at: timestamp


We have a schema Article where we need to find the number of articles that have more than 5 edits and are more than one page long. edits and pages are integer attributes in the schema. Now, the easiest way to do this is the following:

def articles_count(edits, pages) do
Article
|> where([a], a.edits > 5 and a.pages > 1)
|> Repo.count()
end


Along with the count we are also required to get the title and body of all such articles. If we go with the above approach we need to run two queries, once to get the count and another time to get the title and body. This would not be a good approach as it will require multiple hits to the database for querying.

We would prefer to achieve the same in one query. We tried doing the same using fragment.

The above query should now be able to return both the information in a single query.

Fetch from multiple tables conditionally

We will be using the MonthlyPerformance, QuarterlyPerformance and AnnualPerformance schema for the following example.

MonthlyPerformance
-----------------------
stock: text
year: int
month: int
high: decimal
low: decimal
returns: decimal

QuarterlyPerformance
-----------------------
stock: text
year: int
quarter: int
high: decimal
low: decimal
returns: decimal

AnnualPerformance
-----------------------
stock: text
year: int
high: decimal
low: decimal
returns: decimal
end


Let us consider the scenario where we have three schemas. MonthlyPerformance, QuarterlyPerformanceand AnnualPerformance of stocks.

A lot of the fields in these tables are going to be similar. This is where we can use Ecto’s flexibility to write simple queries where the same attributes can be requested from either of the three tables in a single function.

Generally, we create queries for each schema like the example below.

This makes us write similar code for multiple tables. A lot of this repetition will happen as we write queries for each use case. To avoid this scenario we can write conditional queries.

Fetch individual fields from the table dynamically

We will be using the MatchStat schema for the following example.

MatchStat
---------------
id: UUID
1st_innings_score: int
2nd_innings_score: int
runs_scored: int
balls_bowled: int
minutes_played: float
0s: int
1s: int
2s: int
3s: int
4s: int
5s: int
6s: int

One of the scenarios we encountered was, where we had to fetch a particular field from a table and we did not know what field might be requested. We could not return all fields as the table had around 80 attributes and returning all would be highly inefficient. Let us take the example of a cricket match where each stat has to be recorded for analysis. The stats may range from 1st_innings_score, 2nd_innings_score,balls_bowled , runs_scored, minutes_played, 0s, 1s, 2s, 3s, 4s, 5s, 6s, etc.

We need to be able to return individual stats for a match based on the field name of the schema. The easiest way to achieve this is to simply write a query for each field which is a very inefficient idea. Ecto provides us with an easier way of using a map where we can write a query in the following manner.

def match_stat(id, options) do
MatchStat
|> where([ms], ms.id == id)
|> select([ms], map(ms, ^options[:fields]))
|> Repo.one()
end


The :fields atom is an array containing all the required fields to be returned. Let us now take the scenario that this table contains some team-level stats inside two JSON fields named team1_stats and team1_stats. To fetch the individual attributes from inside a JSON field, it is not possible to use the map function. However, the field function along with the fragment function allows us to do this, as below:

def match_stat(id, options) do
MatchStat
|> where([ms], ms.id == id)
|> select([ms], map(ms, ^options[:fields]))
|> Repo.one()
end

`:stat` here can be `team1_stats` or `team2_stats`, while `attribute` can be an attribute inside the JSON.

Conclusion

One of the best attributes of Ecto is, its syntax and nomenclature are very similar to plain SQL despite being an ORM. This allows us to find SQL solutions in Ecto easily. While it does not have everything built-in, it gives us a way to make our queries better. It has crisp documentation but needs more advanced usage. This blog makes an attempt to describe some of these use cases above.