How to select date range generated in fragment?

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 :slight_smile:

I think you need to remove SELECT * FROM from your fragment. Join directly on generate_series

I gave that a whirl but Ecto complains about a syntax error. Without SELECT * FROM the fragment has a right paren that has no matching left paren, couldn’t figure out how to make that not be the case, here is the specific error and the debug query:

[debug] QUERY ERROR source="entry" db=3.7ms
SELECT e0."id", e0."account_id", e0."value", e0."trend", e0."deleted", e0."note", e0."entry_date", e0."inserted_at", e0."updated_at" FROM "entry" AS e0 INNER JOIN (generate_series($1::date::timestamp, $2::date, '1 day') gs) <--- mis-placed paren AS f1 ON e0."entry_date" = cast(gs as date) WHERE (e0."account_id" = $3) [{2019, 9, 3}, {2020, 3, 4}, 30]
Server: localhost:4000 (http)
Request: GET /api/v1/entry/graph?timezone=%22US/Eastern%22
** (exit) an exception was raised:
    ** (Postgrex.Error) ERROR 42601 (syntax_error): syntax error at or near ")"

Then I would make a custom query in sql to get the date range and the entry ids. And then make another query where I get the entries based on the ids

query = """
SELECT gs, FROM generate_series('2020-01-01'::timestamp, '2020-01-05', '1 day') AS gs
LEFT JOIN entry ON cast(gs AS date) = cast(entry.entry_date AS date)
result = Ecto.Adapters.SQL.query!(Repo, query, [])

I am also trying to fill in gaps by joining on a fragment composed from a series. I cannot see how to instruct Ecto to convert generated values for which they are no matches to a map with just the value. The ScheduleView is a struct that contains a property named number. When there is no result satisfying the number I would still like to get an empty map with the number from the right. Without the select I just get those ScheduleView items that satisfy the from/to condition.

** (ArgumentError) cannot load 1 as type :map

 |> where([s], s.from >= ^from )
 |> where([s], s.from <= ^to)
 |> join(:right, [s], u in fragment("generate_series(0, 4)"), on: u == s.number)
 |> select([s, u],  %{label: s.label, number: u})
 |> order_by([s, u], [s.label])