elixir - 将 LEFT OUTER JOIN 查询转换为 Ecto

Question

我不知道如何将 SQL 语句翻译成 Ecto。

Phoenix 设置

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

SQL语句

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;

是否可以用 Ecto 函数替换此 SQL 语句？

Answer 1

默认情况下，left_join 执行 LEFT OUTER JOIN。如果对表使用相同的别名，则查询的其余部分很容易翻译。如果您将 starts_on 和 ends_on 定义为具有适当值的 Date 结构，这应该可以工作:

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
            )