Hi folks,
Running into a bit of a roadblock with a query I am trying to write. The query and result expected in raw SQL is:
select * from entry right outer join generate_series('2020-01-01'::timestamp, '2020-01-15', '1 day') gs on cast(gs as date) = cast(entry.entry_date as date) order by gs;
id | entry_date | gs
----+---------------------+---------------------
| | 2020-01-01 00:00:00
1 | 2020-01-02 00:00:00 | 2020-01-02 00:00:00
| | 2020-01-03 00:00:00
| | 2020-01-04 00:00:00
2 | 2020-01-05 00:00:00 | 2020-01-05 00:00:00
| | 2020-01-06 00:00:00
| | 2020-01-07 00:00:00
| | 2020-01-08 00:00:00
| | 2020-01-09 00:00:00
| | 2020-01-10 00:00:00
| | 2020-01-11 00:00:00
5 | 2020-01-12 00:00:00 | 2020-01-12 00:00:00
| | 2020-01-13 00:00:00
| | 2020-01-14 00:00:00
| | 2020-01-15 00:00:00
I have the following query set up so far:
query = from entry in __MODULE__,
where: entry.account_id == ^account_id,
right_join: gs in fragment("SELECT * FROM generate_series(?::timestamp, ?, '1 day') gs", type(^start_day, Ecto.Date), type(^{2020, 3, 4}, Ecto.Date)),
on: entry.entry_date == fragment("cast(gs as date)"),
select: %{entry: entry}
Which is all fine, I get the one entry I expect, but when I add gs
I’m getting:
** (Ecto.QueryError) PostgreSQL does not support selecting all fields from without a schema. Please specify a schema or specify exactly which fields you want to select in query:
How do I specify any additional schema for this given its effectively just a list of dates?
For a bit of extra context, my goal here is to end up with a list of maps. When the date in the generated date range matches the entry_date
on my table, I want that information included as well. So desired end data formatted something like:
[
%{
date: "2020-03-02" # comes from the fragment date range
},
%{
date: "2020-03-03",
entry: %{}
}
]
This data will be powering a calendar-esque graph that requires the date for each item to be displayed, even if there is no data for that particular day.
Apologies if I am not being super clear, still quite new so I haven’t gotten used to all the terminology, happy to answer any clarifying questions! Thank you