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
belongs_to relationship. That is, a
Rental has many
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
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:
This would return all the colums for
rentals where there are no associated
ON clause joins by the association. The
WHERE clause filters the result to those rentals without any matching
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:
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:
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.
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:
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:
(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:
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.