Convert a LEFT OUTER JOIN query to Ecto

Question

I can't figure out how to translate an SQL statement into Ecto.

The Phoenix Setup

mix phx.gen.html Location Country countries name
mix phx.gen.html Location FederalState federal_states name
mix phx.gen.html Calendar Day days date_value:date
mix phx.gen.html Calendar Period periods name 
                                         starts_on:date 
                                         ends_on:date
                                         country_id:references:countries
                                         federal_state_id:references:federal_states
mix pix.gen.html Calendar Slot slots day_id:references:days 
                                     period_id:references:periods

The SQL statement

SELECT days.date_value, periods.name FROM days 
LEFT OUTER JOIN slots ON (days.id = slots.day_id) 
LEFT OUTER JOIN periods ON (slots.period_id = periods.id and 
(periods.country_id = 1 OR 
periods.federal_state_id = 5)) 
WHERE days.date_value >= '2017-01-01' AND 
days.date_value <='2017-12-31' 
ORDER BY days.date_value;

Is it possible to replace this SQL statement with an Ecto function?

Answer 1

A left_join does a LEFT OUTER JOIN by default. The rest of the query is straightforward to translate if you use the same alias name for the tables. If you have a starts_on and ends_on defined as Date structs with appropriate values, this should work:

query = from(
             days in Day,
             left_join: slots in MehrSchulferien.Calendar.Slot,
             on: days.id == slots.day_id,
             left_join: periods in MehrSchulferien.Calendar.Period,
             on: slots.period_id == periods.id and
                 (periods.country_id == ^federal_state.country_id or
                  periods.federal_state_id == ^federal_state.id),
             where: days.date_value >= ^starts_on and
                    days.date_value <= ^ends_on,
             order_by: days.date_value
            )