Используйте Ecto для генерации_серий в postgres, а также для получения нулевых значений как "0"

Question

Я хочу отобразить диапазон дат (статистика), например:

Dates        | Count
--------------------
"2016-09-01" | 0
"2016-09-02" | 0
"2016-09-03" | 0
"2016-09-04" | 0
"2016-09-05" | 0
"2016-09-06" | 12
"2016-09-07" | 9
"2016-09-08" | 0
"2016-09-09" | 90

Необработанный SQL-запрос:

select date(d) as day, count(clicks.id)
  from generate_series(
    current_date - interval '13 day', 
    current_date, 
    '1 day'
  ) d 
left join clicks on date(clicks.inserted_at) = d and clicks.link_id = 15
group by day order by day;

Я хотел бы использовать его с Ecto без Raw SQL

Самая близкая вещь, которую я нашел, была:

query = from c in Like
  where: c.link_id == ^link.id

...

from( c in query,
  join: day in fragment("select generate_series(current_date - interval '10 day', current_date, '1 day')::date AS d "),
  on: fragment("date(?) = d", field(c, :inserted_at)),
  group_by: fragment("day"),
  order_by: fragment("day"),
  select: %{
    day: fragment("date(d) as day"),
    count: count(c.id)
  }
)

Но он возвращает только даты, представленные в таблице, а не весь диапазон (т. Е. Отображает 0 для дат, которые не совпадают в тот день)

Answer 1

Ты можешь использовать right_join так как ты присоединяешься clicks с сгенерированными датами в Ecto Query вместо сгенерированных дат с clicks как вы сделали в сыром SQL.

Тестовое задание:

alias MyApp.{Click, Repo}
import Ecto.Query

from(c in Click,
  order_by: c.inserted_at,
  select: fragment("date(?) as day", c.inserted_at)
)
|> Repo.all
|> IO.inspect

from(c in Click,
  right_join: day in fragment("select generate_series(current_date - interval '10 day', current_date, '1 day')::date AS d "),
  on: fragment("date(?) = d", field(c, :inserted_at)),
  group_by: fragment("day"),
  order_by: fragment("day"),
  select: %{
    day: fragment("date(d) as day"),
    count: count(c.id)
  }
)
|> Repo.all
|> IO.inspect

Выход:

[debug] QUERY OK source="clicks" db=1.9ms
SELECT date(c0."inserted_at") as day FROM "clicks" AS c0 ORDER BY c0."inserted_at" []
[{2016, 9, 12}, {2016, 9, 13}, {2016, 9, 13}, {2016, 9, 13}, {2016, 9, 13},
 {2016, 9, 15}, {2016, 9, 17}, {2016, 9, 17}]
[debug] QUERY OK source="clicks" db=1.8ms
SELECT date(d) as day, count(c0."id") FROM "clicks" AS c0 RIGHT OUTER JOIN (select generate_series(current_date - interval '10 day', current_date, '1 day')::date AS d ) AS f1 ON date(c0."inserted_at") = d GROUP BY day ORDER BY day []
[%{count: 0, day: {2016, 9, 8}}, %{count: 0, day: {2016, 9, 9}},
 %{count: 0, day: {2016, 9, 10}}, %{count: 0, day: {2016, 9, 11}},
 %{count: 1, day: {2016, 9, 12}}, %{count: 4, day: {2016, 9, 13}},
 %{count: 0, day: {2016, 9, 14}}, %{count: 1, day: {2016, 9, 15}},
 %{count: 0, day: {2016, 9, 16}}, %{count: 2, day: {2016, 9, 17}},
 %{count: 0, day: {2016, 9, 18}}]

Редактировать: Вы также можете больше использовать мощные возможности запросов Экто и меньше полагаться на as а также fragment, Следующий запрос генерирует тот же результат:

from(c in Click,
  right_join: day in fragment("select generate_series(current_date - interval '10 day', current_date, '1 day')::date AS d"),
  on: day.d == fragment("date(?)", c.inserted_at),
  group_by: day.d,
  order_by: day.d,
  select: %{
    day: fragment("date(?)", day.d),
    count: count(c.id)
  }
)