Dynamic where clause in ecto query

[tio407]

I’d like to the change the item.inserted_at below to be in business days not normal days. I created a function called is_10_business_days? to determine if the date corresponds. How would I call there in my where clause?

  def list_users() do
    from(
      u in User,
      # other info here
      where:
        item.status == "foo" and
          is_nil(item.sent_at) and item.inserted_at < ago(10, "day")
    )
    |> Repo.all()

[lucaong]

You cannot execute your own Elixir function as a where clause, because the query has to be translated to SQL and executed by the database server.

In this case I would instead create a function that calculates how many actual days ago is N business days ago:

@spec business_days_ago(integer()) :: integer()
def business_days_ago(n) do
  # ...
end

Then you can use it in your query as:

ten_business_days = business_days_ago(10)

# ...
where:
  item.status == "foo" and
    is_nil(item.sent_at) and item.inserted_at < ago(^ten_business_days, "day")

[tio407]

Thank you!