Today I Learned

A Hashrocket project

Ecto's `distinct` adds an order by clause

When using distinct on you may encounter this error:

select distinct on (color) id, color from fruits order by id;
-- ERROR:  SELECT DISTINCT ON expressions must match initial ORDER BY expressions

This is because distinct on needs to enounter the rows in a specific order so that it can make the determination about which row to take. The expressions must match the initial ORDER BY expressions!

So the above query works when it looks like this:

select distinct on (color) id, color from fruits order by color, id;

OK, now it works.

Ecto’s distinct function helps us avoid this common error by prepending an order by clause ahead of the order by clause you add explicitly.

This elixir statement:

Fruit |> distinct([f], f.color) |> order_by([f], f.id) |> Repo.all

Produces this sql (cleaned up for legibility):

select distinct on (color) id, color from fruits order by color, id;

Ecto added color to the order by!

Without any order by at all, distinct does not prepend the order by.

Read the docs!

Looking for help? Elixir is quickly gaining momentum for web applications that need concurrency, performance, and the ability to connect to many different clients. The developers at Hashrocket are learning along with the rest of the development community that Elixir and Phoenix are viable Rails alternatives for the right application. Check out the source code for Today I Learned, written in Elixir, and contact us if you need help with your Elixir project.