A few weeks ago I wrote about a set of experiments I ran to try to diagnose a problem I was having with Ecto and timex. I have still not figured out what’s going wrong but I’ve moved on a bit. In this post I want to write a little bit about Ecto and date ranges. I’ll use the date ranges as an example while describing how to use custom SQL in Ecto queries.

In this post I’ll be using Elixir 1.2.1 and the following dependencies (according to mix.lock):

%{ " connection" : { :hex , :connection , " 1.0.2" }, " db_connection" : { :hex , :db_connection , " 0.2.3" }, " decimal" : { :hex , :decimal , " 1.1.1" }, " ecto" : { :hex , :ecto , " 1.1.3" }, " poolboy" : { :hex , :poolboy , " 1.5.1" }, " postgrex" : { :hex , :postgrex , " 0.11.0" }}

Let’s start with a new mix project:

$ mix new date_ranges * creating README.md * creating .gitignore * creating mix.exs * creating config * creating config/config.exs * creating lib * creating lib/date_ranges.ex * creating test * creating test/test_helper.exs * creating test/date_ranges_test.exs Your Mix project was created successfully. You can use "mix" to compile it, test it, and more: cd date_ranges mix test Run "mix help" for more commands.

Setup Ecto

I’ve covered this in Setting up Ecto in Elixir and repeated a lot of it in Experiments with Ecto Queries so I won’t repeat all the steps here. But here’s a summary of what I’ve done:

Install and Start Ecto

Configure the Database

Write the Repo Module

Create the database

These steps come directly from Setting up Ecto in Elixir. For more detail see that post or for to see the exact changes I made you can check out the github repo.

Create a Model

Next we need a model to store some date ranges. We can write up something simple like this:

defmodule DateRanges . DateRange do use Ecto . Schema schema " date_ranges" do field :start , Ecto . Date field :end , Ecto . Date timestamps end end

We just describe a date range with a start and end date. We also need a migration to add a corresponding table to our database. This will do:

defmodule DateRanges . Repo . Migrations . AddDateRanges do use Ecto . Migration def change do create table ( :date_ranges ) do add :start , :date add :end , :date timestamps end end end

And then we can migrate our database:

$ mix ecto.migrate 08:46:48.270 [info] == Running DateRanges.Repo.Migrations.AddDateRanges.change/0 forward 08:46:48.270 [info] create table date_ranges 08:46:48.277 [info] == Migrated in 0.0s

My goal with this project is to show how to write Ecto queries against date ranges. So, let’s get started with a test to demonstrate:

Setup tests

We want to run our tests within a database transaction. In order to do this we need to configure Ecto to use the Sandbox adapter. Usually we would do this only for the test env. But, in this example we really only care about the test env. There is no dev or prod. So I’ll modify config.exs and add this line:

config :date_ranges, DateRanges.Repo, adapter: Ecto.Adapters.Postgres, database: "date_ranges", username: "postgres", password: "postgres", + pool: Ecto.Adapters.SQL.Sandbox +

Next, I added this line to begin transactions when starting up the tests:

ExUnit.start() + + Ecto.Adapters.SQL.begin_test_transaction(DateRanges.Repo) +

As the final setup step I wrote up a skeleton for my test file with a setup/1 function:

defmodule DateRangeTest do use ExUnit . Case alias DateRanges . Repo alias DateRanges . DateRange setup tags do unless tags [ :async ] do Ecto . Adapters . SQL . restart_test_transaction ( DateRanges . Repo , []) end seed :ok end test " x" do end def seed do ranges = [ { { 2016 , 1 , 31 }, { 2016 , 2 , 12 } }, { { 2016 , 2 , 1 }, { 2016 , 2 , 4 } }, { { 2016 , 2 , 3 }, { 2016 , 2 , 4 } }, { { 2016 , 2 , 12 }, { 2016 , 2 , 16 } }, { { 2016 , 2 , 28 }, { 2016 , 3 , 10 } }, { { 2016 , 3 , 3 }, { 2016 , 3 , 7 } }, { { 2016 , 4 , 1 }, { 2016 , 4 , 9 } }, { { 2016 , 4 , 6 }, { 2016 , 4 , 12 } }, { { 2016 , 4 , 21 }, { 2016 , 4 , 25 } }, { { 2016 , 5 , 1 }, { 2016 , 5 , 5 } }, { { 2016 , 5 , 7 }, { 2016 , 5 , 8 } }, { { 2016 , 5 , 8 }, { 2016 , 5 , 8 } }, ] Enum . each ranges , fn { s , e } -> Repo . insert! % DateRange { start: Ecto . Date . from_erl ( s ), end : Ecto . Date . from_erl ( e ) } end end end

The setup function restarts the database transaction at the beginning of each test. This maintains a clean environment for each tests. Then setup calls seed to fill in some default data that I reference in the tests.

The seed function uses a list, ranges , which is just a bunch of dates I made up (in Erlang date format). Then I insert them all into the database using Enum.each over Repo.insert! .

Finally, there is one test in the middle but it is currently a placeholder. The next step is to…

Write a Test

test " It can query overlapping ranges" do target = % DateRange { start: Ecto . Date . from_erl ({ 2016 , 4 , 18 }), end : Ecto . Date . from_erl ({ 2016 , 4 , 22 }) } expected = % DateRange { start: Ecto . Date . from_erl ({ 2016 , 4 , 21 }), end : Ecto . Date . from_erl ({ 2016 , 4 , 25 }) } result = target |> DateRange . overlapping |> Repo . one assert result . start == expected . start assert result . end == expected . end end

Of course, this test fails:

1) test It can query overlapping ranges (DateRangeTest) test/date_range_test.exs:17 ** (UndefinedFunctionError) undefined function DateRanges.DateRange.overlapping/1 stacktrace: (date_ranges) DateRanges.DateRange.overlapping(%DateRanges.DateRange{__meta__: #Ecto.Schema.Metadata<:built>, end: #Ecto.Date<2016-04-21>, id: nil, inserted_at: nil, start: #Ecto.Date<2016-04-18>, updated_at: nil}) test/date_range_test.exs:29 . Finished in 0.1 seconds (0.1s on load, 0.03s on tests) 2 tests, 1 failure

Make the test pass

To pass this test we need to implement an overlapping/1 function. This function is passed a target date and returns something we can pass to Repo.all/2 . This means DateRange.overlapping/1 must return an Ecto.Query.t value, that is it should be a query. We want something like this:

def overlapping ( target ) do from range in DateRange , where: overlaps ( range , ^ target ) end

But what is overlaps ? The Ecto query language doesn’t expose this.

Fortunately, Postgres has an overlaps function. We can write a custom SQL fragment to use this overlaps function like this:

import Ecto . Query , only: [ from: 1 , from: 2 ] def overlapping ( target ) do from range in DateRanges . DateRange , where: fragment ( " (?, ?) OVERLAPS (?, ?)" , range . start , range . end , type ( ^ target . start , Ecto . Date ), type ( ^ target . end , Ecto . Date )) end

With this implementation of overlapping/1 the test passes:

Finished in 0.1 seconds (0.1s on load, 0.03s on tests) 2 tests, 0 failures

How does this work?

The beginning of our query looks quite normal: from range in DateRanges.DateRange, where: . Next, we have a call to the fragment/1 macro. It starts with a string:

" (?, ?) OVERLAPS (?, ?)"

This is a fragment of SQL which uses the OVERLAPS function to compare two date ranges. The ? characters are placeholders that need to be filled in with real values. Those values are described as further arguments to fragment/1 . We are effectively generating SQL that would look something like this:

" (range.start, range.end) OVERLAPS (^target.start, target.end)"

The last piece to explain is the the type/2 calls. With fragments Ecto doesn’t know the type of the data elements and we can use the type/2 function to describe the type. In overlapping/1 we describe target.start and target.end as Ecto.Date .

Conclusion

In this post we looked at how to use Ecto fragments to inject custom SQL into a composable query. I learned a lot about how to do this while working on an app of mine. And I hope I was able to explain it and help others to do the same thing.