How to inject function in a query? Creating a new variable in a query

The following should just work.

from u in User, where: u.reg_d > ^start_date and u.reg_d < ^end_date

Ecto builds a whole query and then sends it to the database - you seem to be hoping to execute some Elixir for each matching row, which isn’t supported.

There are a couple different approaches you could take to solve this:

• build a complex query using date_part and logical operators - basically re-implement date comparison by hand but without comparing the years.
  • pro: does everything in SQL
  • con: does everything in SQL; IIRC date_part isn’t particularly index-friendly
• build a complex query by generating a copy of the supplied date range and combining the clauses with OR. In your example, it’d be checking “is reg_d between 2022-06-12 and 2022-07-30? What about 2021-06-12 and 2021-07-30, etc?”
  • pro: more index-friendly since it’s matching on the whole value of reg_d
  • con: if reg_d could be many many years ago, the query will get very large
• change the data format: store a next_anniversary_d column that contains reg_d in the current year.
  • pro: the check becomes very very simple and index-friendly: “is next_anniversary_d in the supplied range?”
  • con: now you need code to update the anniversary column every year.

I think using date_part is the best solution and you could add an index on the function itself in postgres.

See:

So:

CREATE INDEX user_registration_doy ON users(date_part('doy', registration_date));

Then probably:

def query(from_date, to_date) do
  from_doy = Date.day_of_year(from_date)
  to_doy = Date.day_of_year(to_date)

  User
  |> where([x], fragment("date_part('doy', ?) >= ?", x.start_date, ^from_doy)
  |> where([x], fragment("date_part('doy', ?) <= ?", x.start_date, ^end_doy)
  |> Repo.all()
end

n.b. Must validate both pg and elixir algos agree on day of year.

Thanks for the reply. I changed the data format of reg_d to the current year by creating a subquery. From there, I compared the new_reg_d with the start_date and end_date and it works.