"undefined function fragment" in from or alternative errors if I change things around

Questions / Help
,
1

Hello there!

I have the following Ecto query:

content_per_day = 
  from c in Content,
    right_join: day in fragment("select generate_series(current_date - interval '30 day', current_date, '1 day')::date AS d "), 
    on: fragment("date(?) = d", field(c, :inserted_at)),
    join: p in assoc(c, :publication),
    join: owner in assoc(p, :publication_owners),
    where: owner.id == ^user.id,
    group_by: fragment("day"),
    order_by: [asc: fragment("day")],
    select: [count(c.id), fragment("date(d) as day")]

However I want to always select a list of days, even if there is no content published, so I’m making the content the RIGHT JOIN and want to make the generated_series the primary table:

content_per_day = 
  from day in fragment("select generate_series(current_date - interval '30 day', current_date, '1 day')::date AS d "),
    right_join: c in Content,

Or alternatively:

content_per_day = 
  from day in "select generate_series(current_date - interval '30 day', current_date, '1 day')::date AS d ",

Now the second two error… is there a better or correct way to do this?

Maybe I can create a view that generates the series for me so I don’t have this issue…

2

Ah, I’ve made the view and it works but not how I want - the where clause seems to be the issue. I’ll rewrite it as a subquery and post the result here shortly.