将左外部联接查询转换为ECTO [英] Convert a LEFT OUTER JOIN query to Ecto

问题描述

我找不到如何将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;

是否可以将此SQL语句替换为ecto函数？

推荐答案

Aleft_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
            )

这篇关于将左外部联接查询转换为ECTO的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！