marvinljw
How to inject function in a query? Creating a new variable in a query
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!
Marked As Solved
al2o3cr
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_partand 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_partisn’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 “isreg_dbetween2022-06-12and2022-07-30? What about2021-06-12and2021-07-30, etc?”- pro: more index-friendly since it’s matching on the whole value of
reg_d - con: if
reg_dcould be many many years ago, the query will get very large
- pro: more index-friendly since it’s matching on the whole value of
- change the data format: store a
next_anniversary_dcolumn that containsreg_din the current year.- pro: the check becomes very very simple and index-friendly: “is
next_anniversary_din the supplied range?” - con: now you need code to update the anniversary column every year.
- pro: the check becomes very very simple and index-friendly: “is
Also Liked
LostKobrakai
The following should just work.
from u in User, where: u.reg_d > ^start_date and u.reg_d < ^end_date
Popular in Questions
Other popular topics
Latest Phoenix Threads
Latest on Elixir Forum
Sponsor Spotlight
Producing high quality Elixir screencasts since 2017.
Categories:
Sub Categories:
Forums
Popular Tags
- #ecto
- #liveview
- #troubleshooting
- #learning-elixir
- #deployment
- #library
- #erlang
- #testing
- #genserver
- #mix
- #absinthe
- #remote-other
- #otp
- #plug
- #how-to-question
- #macros
- #postgres
- #channels
- #elixirconf
- #exunit
- #discussion
- #code-sync
- #javascript
- #podcasts
- #onsite
- #dialyzer
- #docker
- #authentication
- #umbrella
- #full-time-contract
- #podcasts-by-brainlid
- #ecto-query
- #elixir-ls
- #phoenix_html
- #iex
- #blog-post
- #graphql
- #genstage
- #ai
- #websockets
- #supervisor
- #advent-of-code
- #elixirconf-us
- #distillery
- #processes
- #forms
- #api
- #metaprogramming
- #security
- #performance
Our Sponsors
Build Elixir applications with speed and confidence.
Supporting innovation across the BEAM ecosystem.
We build reliable cloud platforms for business-critical systems.
Catch errors, track performance, monitor hosts and more.
Real-time error tracking, performance insights, and observability for devs.
Practical resources that improve the lives of professional developers.
Develop your skills with books, videos, and courses.
The deployment platform built for Elixir: PaaS ease, VPS control.
Courses that'll move you from confusion to "Aha, now I get it!"