Using generate_series in ecto and passing a value

I am trying to use generate_series in this query to get the number of sales in a time period even with the days that have no sales. I got it working with a hard-coded value but now I need to get all the sales since the contract was created.

What I have so far:

query =
from(
c in Contract,
join: v in Voucher,
on: v.contract_id == c.id,
join: s in Sale,
on: s.voucher_id == v.id and c.id == ^contract_id,
right_join:
day in fragment(
"select generate_series(current_date - interval '60 day', current_date, '1 day')::date AS d"
),
on: day.d == fragment("date(?)", s.date),
group_by: day.d,
select: %{
number_sales: count(s.id),
total_value: sum(s.value),
date: day.d
},
order_by: [asc: day.d]
)

Repo.all(query)
|> Enum.map(fn entry -> Map.put(entry, :date, Date.from_erl!(entry.date)) end)


With this query I get all the sales in the last 60 days even with the days with no sales.

I now changed the line generate_series('2018-06-01', current_date, '1 day') to
start at the date the contract was created but I get the following error:

generate_series('2018-06-01', current_date, '1 day')

[debug] QUERY ERROR source="contracts" db=1.0ms
SELECT count(s2."id"), sum(s2."value"), f3."d" FROM "contracts" AS c0 INNER
JOIN "vouchers" AS v1 ON v1."contract_id" = v1."id" INNER JOIN "sales" AS s2
ON (s2."voucher_id" = v1."id") AND (c0."id" = $1) RIGHT OUTER JOIN (select
generate_series(c0."inserted_at", current_date, '1 day')::date AS d) AS f3 ON
f3."d" = date(s2."date") GROUP BY f3."d" ORDER BY f3."d" [1]
** (Postgrex.Error) ERROR 42P01 (undefined_table): invalid reference to FROM-
clause entry for table "c0"
(ecto) lib/ecto/adapters/sql.ex:431: Ecto.Adapters.SQL.execute_and_cache/7
(ecto) lib/ecto/repo/queryable.ex:133: Ecto.Repo.Queryable.execute/5
(ecto) lib/ecto/repo/queryable.ex:37: Ecto.Repo.Queryable.all/4
(app) lib/app/contracts/contracts.ex:313: App.Contracts.get_sales_day/1


Modified code:

query =
from(
c in Contract,
join: v in Voucher,
on: v.contract_id == c.id,
join: s in Sale,
on: s.voucher_id == v.id and c.id == ^contract_id,
right_join:
day in fragment(
"select generate_series(?, current_date, '1 day')::date AS d",
c.inserted_at
),
on: day.d == fragment("date(?)", s.date),
group_by: day.d,
select: %{
number_sales: count(s.id),
total_value: sum(s.value),
date: day.d
},
order_by: [asc: day.d]
)
Repo.all(query)
|> Enum.map(fn entry -> Map.put(entry, :date, Date.from_erl!(entry.date)) end)


on: v.contract_id == v.id

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.