I am a huge fan of Formula 1 racing (my favorite driver is Felipe Massa). My brother, father, and I even participate in our own version of a fantasy Formula 1 league. I wrote a Ruby on Rails app a few years ago to handle all the bookkeeping of the league including trades and point calculations. Looking back on it now, it is definitely code written by a 4-years-less-experienced me – which means it’s time for an upgrade.

The time came to rewrite the app and I considered a number of different technologies to create it from scratch. I first considered trying out Rails 5 even though it was not officially released yet. However, I’ve also been dabbling in Elixir over the past couple years but hadn’t yet tried out the Phoenix web framework. In using Phoenix, I was introduced to Ecto. I decided to rewrite it in Elixir with Phoenix.

One of the things I wanted to do was take a series of race results of a driver and create a table of current standings. In my past implementation in Rails, I didn’t know of a good, quick, performant way to do this so I stored standings after every race directly in the database. However, I was surprised how easy it was to calculate the standings on the fly in an Ecto query.

A Quick F1 Rules Primer

If you don’t mind, let me take a moment to discuss how the standings are calculated in Formula 1. If you are a fan or already know this, feel free to skip ahead.

Over the course of a season, there are multiple races. In each and every race, a driver has the opportunity to score points based on their finishing position in the race. The driver that has the most points at the end of the season is the champion. To the right is a table of the top 5 positions in the championship after 3 races. This is what we will try to recreate.

For a race, only the top 10 finishers are awarded any points. From 1st to 10th, they are awarded as follows: 25, 18, 15, 12, 10, 8, 6, 4, 2, 1.

On To Elixir

For these examples, let’s assume we have some models that contain the data we need. Some of this code will not make sense outside of Ecto and Phoenix but if you aren’t familiar with those, they should be pretty self-explainatory. There is some code left out to make this a little more succinct.

defmodule FantasyF1.Driver do
  schema "drivers" do
    # Ecto automatically created an id column
    field :first, :string
    field :last, :string
    has_many :results
  end
end

defmodule FantasyF1.Race do
  schema "races" do
    # Ecto automatically created an id column
    field :round, :integer
    has_many :results, Result
  end
end

defmodule FantasyF1.Result do
  schema "results" do
    # Ecto automatically created an id column
    field :points, :integer
    belongs_to :race
    belongs_to :driver
  end
end

All we are doing here is letting our application know about how our data is structured. That’s about all we need in order to explore how we can use Ecto to return the information we need to construct a current standings table after any specific race.

Enter Ecto

One of the nice things about Ecto is that we can compose our query step by step so let’s go down that route. The first thing we need to do is set up a query that joins Results and Drivers:

query = from r in FantasyF1.Result,
        join: d in FantasyF1.Driver,
        where: r.driver_id == d.id

# => #Ecto.Query<from r in FantasyF1.Result,
#                join: d in FantasyF1.Driver,
#                on: true,
#                where: r.driver_id == d.id>

Let’s take this apart. from r in FantasyF1.Result sets up our first part of the query. We are telling Ecto that during this query segment, we want to (eventually) select some fields from the results table that contains records for our FantasyF1.Result model.

join: d in FantasyF1.Driver let’s Ecto know that we’d like to join in results from the drivers table and we’ll refer to that table as d for now. Note that there is no “special sauce” to these variable names – we could name them whatever we want. I just named them r and d here for brevity.

Finally, we perform the join by letting Ecto know how to join them: we want results.driver_id to equal drivers.id. So far so good.

If you set up a project like this and run this in iex, you’ll find that nothing is yet grabbed from the database. So let’s keep composing. Now we want to perform another join, this time with the drivers table.

query = from [r, d] in query,
        join: race in FantasyF1.Race,
        where: r.race_id == race.id

# => #Ecto.Query<from r0 in FantasyF1.Result,
#                join: d in FantasyF1.Driver, on: true,
#                join: r1 in FantasyF1.Race, on: true,
#                where: r0.driver_id == d.id,
#                where: r0.race_id == r1.id>

Here we are continuing to compose our query so we’ll continue using the query variable name.

Look at from [r, d] in query. We previously joined two tables in query so if we are going to continue composing from there, we need to let Ecto know how to refer to the two previously joined tables. Note that order does matter here. In this case, r will be the results table and d will be the drivers table.

Next we join in the races table on results.race_id == race.id (results is referred to as r above).

And with this, we have joined three tables but still have not pulled any results from the database.

Let’s calculate

Now we get to the meat. We are going to do a number of things here. We’ll select the fields we want returned, let Ecto know how to group our results, sum up the total number of points earned by the drivers, then order based on that sum, while limiting the results up to a particular race “round”.

query = from [res, dri, rac] in query,
        where: rac.round <= 3,
        select: %{driver: dri.last,
                  points: sum(res.points)},
        group_by: dri.id,
        order_by: [desc: sum(res.points)]

# => #Ecto.Query<from r0 in FantasyF1.Result,
#                join: d in FantasyF1.Driver, on: true,
#                join: r1 in FantasyF1.Race, on: true,
#                where: r0.driver_id == d.id,
#                where: r0.race_id == r1.id,
#                where: r1.round <= 3,
#                group_by: [d.id],
#                order_by: [desc: sum(r0.points)],
#                select: %{driver: d.last, points: sum(r0.points)}>

from [res, dri, rac] in query again sets us up to utilize our now three joined tables. Remember, order matters here and we can name our variables whatever we want.

where: rac.round <= 3 limits our query to just select the results from the first 3 races.

This is the first time we’ve seen select being used but it lets Ecto know exactly what we want selected from the database and in what form. In this case, we’d like a Map of the driver’s last name and the sum of the points they’ve scored in the three races we’re limiting it to. I like that Ecto provides a sum here for us to use without dropping into SQL fragments.

group_by: dri.id tells the database that for these results, we need them returned grouped by the Driver id. This allows the suming of the result points to be done by Driver.

Finally, we tell Ecto we want the results ordered by the sum of their points with the most points first.

With all this, we still haven’t actually hit the database yet – we’re just continuing to compose our query.

Fetching the results

The last thing we need to do is to finally tell Ecto to use this composed query and take it to the database. We do this by passing the composed query to our Repo (which tells Ecto how to interact with our database) and asking for all the results. Running this in iex with this year’s actual race results yields this (after some formatting help):

iex(13)> FantasyF1.Repo.all query
[%{driver: "Rosberg",    points: 75},
 %{driver: "Hamilton",   points: 39},
 %{driver: "Ricciardo",  points: 36},
 %{driver: "Vettel",     points: 33},
 %{driver: "Raikkonen",  points: 28},
 %{driver: "Massa",      points: 22},
 %{driver: "Kvyat",      points: 21},
 %{driver: "Grosjean",   points: 18},
 %{driver: "Verstappen", points: 13},
 %{driver: "Bottas",     points:  7},
 %{driver: "Hulkenberg", points:  6},
 %{driver: "Sainz Jr",   points:  4},
 %{driver: "Vandoorne",  points:  1},
 %{driver: "Perez",      points:  0},
 %{driver: "Magnussen",  points:  0},
 %{driver: "Ericsson",   points:  0},
 %{driver: "Wehrlein",   points:  0},
 %{driver: "Alonso",     points:  0},
 %{driver: "Nasr",       points:  0},
 %{driver: "Button",     points:  0},
 %{driver: "Haryanto",   points:  0},
 %{driver: "Gutierrez",  points:  0},
 %{driver: "Palmer",     points:  0}]

# => SELECT d1."last", sum(r0."points")
#      FROM "results" AS r0
#      INNER JOIN "drivers" AS d1 ON TRUE
#      INNER JOIN "races" AS r2 ON TRUE
#      WHERE (r0."driver_id" = d1."id")
#        AND (r0."race_id" = r2."id")
#        AND (r2."round" <= 3)
#      GROUP BY d1."id"
#      ORDER BY sum(r0."points") DESC

When I first discovered that I could do all that in a single query that was built up over a number of segments, I was floored.

For you racing purists out there, you’ll notice that there are some complex tie breakers that have not been implemented in this code. For example, all those 0-point drivers are not actually equal based on tie breakers. The official tie breaker is number of 1st place finishes, then number of 2nd place finishes, then number of 3rd place finishes, and so on until the tie is broken. While not implemented here, I’m confident you could do this tie breaking calculation within the single Ecto query.

Wrapping up

I’m still very much a newbie when it comes to Ecto and Elixir. But I believe that further proves my point that you can do some very powerful things with Ecto without having to know it all or even writing a single SQL fragment. These calculations were all done in the database server – we did no ordering or summing or grouping outside of Postgres. This will undoubtedly lead to faster page loads in my final FantasyF1 app.

I’d love to hear your thoughts on this topic. Would you have structured your query differently? Did I miss something that a 4-years-more-experienced me would be ashamed of? Let me know! I’m @geolessel on Twitter and @geo in the elixir-lang Slack group.

Update

May 25, 2016

Reader @luk3thomas asked me on Twitter if I could post the SQL output of the final composed query:

@geolessel Can it output the raw SQL? It would be nice to see the query in the blog post :)

— Luke Thomas (@luk3thomas) May 25, 2016

I thought that was a great idea! I’ve updated the code samples to include what would be output if you ran the samples in an iex session. For the first few samples, while we are building the query, you can see it returns an #Ecto.Query result. As mentioned in the post, that doesn’t get translated into the SQL until FantasyF1.Repo.all query is run. All that is now much more obvious with the output in the post. Thanks, Luke!