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

Phoenix Forum Questions / Help
, , , ,
1

Hi all,

Currently I need to create a function to get all the users with registration date between a given start_date to a given end_date regardless of the year. This means that the user with registration date of ~D[2016-06-19] should appear for the function get_users(~D[2022-06-12], ~D[2022-07-30]).

So, I am trying to create a new_date(year of start_date, reg_d.month, reg_d.day) before comparing with the interval. Let’s assume that both the start_date and end_date is always on the same year.

def get_users(start_date, end_date) do
start_year = start_d.year

query = from u in User, 
    where ^Date.from_erl!({ start_year, (fragment(“date_part(‘month’, ?)”, u.reg_d)), (fragment(“date_part(‘day’, ?)”, u.reg_d)) }) > ^start_date 
    and ^Date.from_erl!({ start_year, (fragment(“date_part(‘month’, ?)”, u.reg_d)), (fragment(“date_part(‘day’, ?)”, u.reg_d)) }) < ^end_date

Repo.all(query)
end

When i write the above, it shows “CompileError undefined function fragment/2 Stacktrace: | (elixir 1.12.3) src/elixir_locals.erl:114: anonymous fn/3 in :elixir_locals.ensure_no_undefined_local/3”

Thank you in advance!

1 Like
2

The following should just work.

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

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.
1 Like
4

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.

5

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.