The correct way to filter by an interval in Ash

Ash Forum Ash Questions / Help
, , ,
1

I am trying to filter records by a calculated interval.

My resource has these two attributes:

    attribute :retry_at, :utc_datetime, allow_nil?: true
    attribute :tried_at, :utc_datetime, allow_nil?: true

I tried the following calculation:

  calculations do
    calculate :interval, :time, expr(retry_at - tried_at)
  end

Then the following query

ten_minutes = Time.new!(0, 10, 0)

Red.Practice.Card
|> Ash.Query.filter(interval > ^ten_minutes)
|> Red.Practice.read!()

This felt like amazing, because it composed a SQL query that actually worked. The calculation even returned a nice nil when one of the values are missing.

Except that my time type for the calculation is wrong. It can not handle more than a day, because a Time in Elixir represents a time of day.

I tried this:

    calculate :interval, :naive_datetime, expr(fragment("retry_at - tried_at"))

But then I get the following error:

(ArgumentError) cannot load `%Postgrex.Interval{months: 0, days: 0, secs: 600, microsecs: 0}` as type #Ash.Type.NaiveDatetime.EctoType<[]>

How can I create a good interval calculation on which I can filter?

2

So are you trying to see how much time is left? I would perhaps return an integer, and using a postgres function to turn the interval into a number of seconds, or something along those lines.

Otherwise, what you could do is not make a calculation that actually returns an interval, and instead make a calculation that tells you if the interval would fit a provided time (which if you want to support longer than one day you’d want to accept an integer amount of seconds).

calculate :interval_contains, :integer, expr(fragment("EXTRACT(EPOCH FROM INTERVAL ?)", retry_at - tried_at) > ^arg(:seconds)) do
  argument :seconds, :integer, allow_nil?: false
end

Then you can do

ten_minutes = :timer.minutes(10) / 1000

Red.Practice.Card
|> Ash.Query.filter(interval_contains(seconds: ten_minutes))
|> Red.Practice.read!()

This way you aren’t surfacing an interval type into your application that you have to figure out how to represent in Elixir via a type.

1 Like
3

Thanks for the reply. I was trying to query a list of cards the student is struggling with in order to present that to a parent or tutor.

A small variation on what you suggested was exactly what I needed:

calculate :interval_in_seconds,
              :integer,
              expr(fragment("EXTRACT(EPOCH FROM ?)", retry_at - tried_at))

And then I can query it with:

require Ash.Query

day_of_seconds = round(:timer.hours(24) / 1000)

Red.Practice.Card
|> Ash.Query.filter(interval_in_seconds < ^day_of_seconds)
|> Red.Practice.read!()

And the filtering happens in Postgres, amazing!

1 Like