WHERE clause with Timex.Duration

Hey all,

I’m pretty new (a.k.a. noob) about Elixir and Phoenix. I’m learning elixir and Phoenix by creating API. But I have a problem about model query when I use Timex library. I’m trying to add;

def find_users_randomly(query, user_id, count \\ 10) do
  from a in query,
    where: a.id != ^user_id,
    where: is_nil(a.last_time_get_msg_at),
    or_where: 45 < Timex.Duration.diff(a.last_time_get_msg_at, Duration.now, :minutes),
    order_by: [desc: :last_login_at],
    select: [:id, :uuid],
    limit: ^count
end

But Timex.Duration.diff(a.last_time_get_msg_at, Duration.now, :minutes) gives error below.

== Compilation error on file web/models/user.ex ==
** (Ecto.Query.CompileError) `Duration.diff(a.last_time_get_msg_at(), Duration.now(), :minutes)` is not a valid query expression

I tried to use dynamic, cast and also pipe-based but I couldn’t find the solution for this.

What can I do for this? What’s your suggest?

packages:

{:timex, “~> 3.1”}
{:phoenix_ecto, “~> 3.0”}
{:phoenix, “~> 1.2.1”}

model example;

defmodule Test.User do
  use Test.Web, :model
  use Timex

  alias Timex.Duration

  schema "users" do
    field :last_login_at, Ecto.DateTime, default: Ecto.DateTime
    field :last_time_get_msg_at, Ecto.DateTime

    timestamps()
  end

  def changeset(struct, params \\ %{}) do
    struct
    |> cast(params, [:last_login_at])
    |> validate_required([:last_login_at])
  end

  def find_users_randomly(query, user_id, count \\ 10) do
    from a in query,
      where: a.id != ^user_id,
      where: is_nil(a.last_time_get_msg_at),
      or_where: 45 < Duration.diff(a.last_time_get_msg_at, Duration.now, :minutes),
      order_by: [desc: :last_login_at],
      select: [:id],
      limit: ^count
  end
end

Thanks!

3 Likes

I got the answer on stackoverflow.

You can’t use Timex (or any Elixir function) in a query like this since one argument is a database field. You’ll have to find the equivalent function in PostgreSQL (or whichever database you’re using). – Dogbert

In PostgreSQL, you can subtract now() and the date and compare to the interval 45 minutes:

or_where: fragment("now() - ? <= interval '45 minutes'", a.last_time_get_msg_at),

– Dogbert

2 Likes