Querying For Models With no Matching Associations in Ecto

I’ve been working with Ecto a lot lately and wanted to spend this post writing up something I’ve learned while trying to put together a query. I’m by no means a database expert and this is one of the more complex queries I’ve had to write so far.

In the private project I’m working on, we have a database schema that consists of rentals that are marked unavailable on certain dates. These unavailable date ranges are stored in an Unavailability model and associated through a has_many / belongs_to relationship. That is, a Rental has many unavalable_date_ranges and Unavailability belongs to a Rental like this:

When trying to find a rental I want the user to be able to search (query) for rentals that are available on a given day or range of days. This means finding rentals that have no associated Unavailability records that overlap the desired days.

A while back I had written a query that turned out not to work. It only worked for very simple cases but failed when a rental had more than one associated Unavailability. I set out to fix this.

Research and Experimentation

I’m not an SQL or Ecto expert so I set out to research what I needed to learn in order to solve my problem. After some googling around I came across the blog post “All, None, and One: The SQL Left Join Trick”. This post taught me, as it promissed, how to use a left join to find rows with no matching associations. I highly recommend reading the post but I’ll summarize what I took away from it.

A left join between Rental and Unavailability will contain all of the rows from Rental even if they have no matching unavailable_date_ranges. And we can add additional criteria so that the results has only those rentals with no matching unavalable_date_ranges, that is those that are available.

I played around with this technique and was able to build up this query:

SELECT * FROM rentals
    LEFT OUTER JOIN unavailabilities
      ON (unavailabilities.rental_id = rentals.id)
  WHERE rental_id IS NULL;

This would return all the colums for rentals where there are no associated unavalable_date_ranges. The ON clause joins by the association. The WHERE clause filters the result to those rentals without any matching unavalable_date_ranges.

But this doesn’t get me exactly what I want. I need to add in a condition to check for overlap. I know how to write the condition:

(unavailabilities.start, unavailabilities.end) OVERLAPS
  ('2016-01-31'::date, '2016-02-05'::date)

but not where to put it. After some failed experiments and more googling I came to this stack overflow answer which helped me to work out the right SQL syntax. Here’s the SQL query I ended up with:

SELECT * FROM rentals
    LEFT OUTER JOIN unavailabilities
      ON (unavailabilities.rental_id = rentals.id)
      AND (unavailabilities.start, unavailabilities.end)
        OVERLAPS ('2016-01-31'::date, '2016-02-05'::date)
  WHERE rental_id IS NULL;

Working within the Postgres console I was able to confirm that this works for my use cases.

Build the Ecto Query from the SQL Query

The next step was to take the SQL query I have build and turn it into an Ecto query. Of course, I could try to use the raw SQL in a fragment but it would be nice to use a plain Ecto query if possible.

While doing this work I found the post “Diving Into Ecto Part 2” very helpful. As was the Ecto documentation for join.

Just as with the raw SQL the one thing that I had a little trouble with was adding the overlaps condition in the right place. Most join examples use assoc as in this example from the docs:

from p in Post,
  left_join: c in assoc(p, :comments),
  select: {p, c}

I wanted to combine the association with the overlap condition. But, this doesn’t work. Instead I had to write out the association condition by hand, which is similar to what we see in the SQL.

I ended up with this function to build the query:

@doc """
Query rentals available on specific date range.

* `query` - Initial query to start with.  Only, rentals included in this
   query will be considered.
* `s` - starting date to check for availablity
* `e` - ending date to check for availablity
"""
def available_between(query \\ Rental, s, e) do
  s = parse_date(s)
  e = parse_date(e)

  from rental in query,
    left_join: range in Unavailability,
      on: (range.rental_id == rental.id) and overlaps(range, ^s, ^e),
    where: is_nil(range.rental_id)
end

defmacro overlaps(range, s, e) do
  #  Uses the Postgres specific OVERLAPS function
  quote do
    fragment("(?, ?) OVERLAPS (?, ?)",
             unquote(range).start, unquote(range).end,
             type(unquote(s), Ecto.Date), type(unquote(e), Ecto.Date))
  end
end

The condition (range.rental_id == rental.id) plays the same role as assoc(rental, :unavailable_date_ranges) and then I combined that with overlaps(range, ^s, ^e) to setup the left join. I think this resembles the SQL quite closely.

Here’s the SQL generated by Ecto as shown in the logs:

SELECT r0."id", r0."title", r0."description", r0."location", r0."owner_id",
       r0."inserted_at", r0."updated_at" FROM "rentals" AS r0
    LEFT OUTER JOIN "unavailabilities" AS u1
      ON (u1."rental_id" = r0."id")
      AND (u1."start", u1."end") OVERLAPS ($1::date, $2::date)
  WHERE (u1."rental_id" IS NULL) [{2016, 1, 31}, {2016, 2, 5}]

Conclusion

In this post we explored a bit of SQL and I learned a bit about left joins and how to use the effectively. We also looked at building up Ecto queries from SQL. I learned a lot in doing this work and I hope you were able to learn from this post.