frankdugan3:

I have a calculation that uses a postgres function to return a json object:

calculate :total_hours_report,
          Hsm.Ash.Payroll.TotalHoursReport,
          expr(
            type(
              fragment(
                "get_attendance_summary(?, ?, ?)",
                id,
                ^arg(:start_date),
                ^arg(:end_date)
              ),
              ^Hsm.Ash.Payroll.TotalHoursReport
            )
          ) do
  private? true
  argument :start_date, :date, allow_nil?: false
  argument :end_date, :date, allow_nil?: false
end

It produces a number of fields like total_hours etc that I’d like to sort on.

I know I need to extract the field with another calculation and sort on that, but I’m getting a little hung up on how to handle calling a calculation with args inside another calculation with args. It’s also a fairly expensive operation, so if possible I’d like to somehow tell it to use the already-loaded calculation and not require the arguments at all.

Any pointers? <:thinkies:915154230078222336>

frankdugan3:

FWIW, I load it with this action:

read :total_hours_report do
  argument :start_date, :date, allow_nil?: false
  argument :end_date, :date, allow_nil?: false
  argument :sort_param, :string, default: "code"

  prepare fn query, _ ->
    start_date = Ash.Query.get_argument(query, :start_date)
    end_date = Ash.Query.get_argument(query, :end_date)
    sort_param = Ash.Query.get_argument(query, :sort_param)
    sort = Ash.Sort.parse_input!(__MODULE__, sort_param)

    query
    |> Ash.Query.sort(sort)
    |> Ash.Query.load(total_hours_report: %{start_date: start_date, end_date: end_date})
    |> Ash.Query.filter(total_hours_report["total_hours"] > 0)
  end
end

And this code_interface:

define :total_hours_report,
  action: :total_hours_report,
  args: [:start_date, :end_date, :sort_param]

zachdaniel:

👋

zachdaniel:

okay, so there isn’t currently anything to reuse a calculation that is being selected, unfortunately

zachdaniel:

so it will depend on how postgres does its work TBH

zachdaniel:

In fact, this might be more than problematic for your case, you’ll have to test it

zachdaniel:

because each thing that depends on a single field might need to rerun the calculation. I’d hope that postgres was smart enough not to do that, but honestly they probably aren’t?

zachdaniel:

This will likely need to be optimized at the ash_postgres layer, but it is complicated.

zachdaniel:

Lemme show you what it would look like in ash-land, and then we can see if its necessary to optimize

zachdaniel:

calculate :some_key, :type, expr(total_hours_report(start_date: arg(:start_date), end_date: arg(:end_date), sort_param: arg(:sort_param))[:some_key])

frankdugan3:

OK, how do I use that calculation to sort, because sort can’t take args, right? 🤔

zachdaniel:

yep, it can 🙂

zachdaniel:

sort(calc: {:asc, %{arg1: :value}})

frankdugan3:

Oooh, OK. Does that work w/ ad-hoc calcs as well?

zachdaniel:

yeah, I’m pretty sure you can say sort({Calculation.new(....), {:asc, %{arg1: :value}}})

zachdaniel:

maybe

zachdaniel:

try it out 😆

frankdugan3:

🤯

frankdugan3:

Almost there, running into trouble making this access dynamic:

calculate :total_hours_report_field,
          :decimal,
          expr(
            total_hours_report(start_date: arg(:start_date), end_date: arg(:end_date))[arg(:field)]
          ) do
  private? true
  argument :start_date, :date, allow_nil?: false
  argument :end_date, :date, allow_nil?: false
  argument :field, :string, allow_nil?: false
end

== Compilation error in file lib/hsm/ash/employees/resources/employee.ex ==
** (RuntimeError) {:%{}, [], [__struct__: Ash.Query.Call, args: [:field], name: :arg, operator?: false, relationship_path: []]} is not a valid path to get
    (ash 2.9.18) lib/ash/expr/expr.ex:126: Ash.Expr.do_expr/2

Is there a different syntax to make that dynamic?

frankdugan3:

(it’s a json field, so string-keyed)

zachdaniel:

^arg(:field) on that case

frankdugan3:

Still same error with [^arg(:field)] .

zachdaniel:

oh

zachdaniel:

right

zachdaniel:

[] expects static values

zachdaniel:

you might need to use a fragment

frankdugan3:

Got it! 🚀

calculate :total_hours_report_field,
  :decimal,
  expr(
    fragment(
      "(get_attendance_summary(?, ?, ?) ->> ?)::decimal",
      id,
      ^arg(:start_date),
      ^arg(:end_date),
      ^arg(:field)
    )
  ) do
  private? true
  argument :start_date, :date, allow_nil?: false
  argument :end_date, :date, allow_nil?: false
  argument :field, :string, allow_nil?: false
end

read :total_hours_report do
  argument :start_date, :date, allow_nil?: false
  argument :end_date, :date, allow_nil?: false
  argument :sort_param, :string, allow_nil?: false

  prepare fn query, _ ->
    start_date = Ash.Query.get_argument(query, :start_date)
    end_date = Ash.Query.get_argument(query, :end_date)
    sort_param = Ash.Query.get_argument(query, :sort_param)

    sort =
      case sort_param do
        "total_hours_report_paid" ->
          [
            total_hours_report_field:
              {:asc, %{start_date: start_date, end_date: end_date, field: "paid"}}
          ]

        "-total_hours_report_paid" ->
          [
            total_hours_report_field:
              {:desc, %{start_date: start_date, end_date: end_date, field: "paid"}}
          ]

        # ...
          
        sort_param ->
          case Ash.Sort.parse_input(__MODULE__, sort_param) do
            {:ok, sort} -> sort
            _ -> [:code]
          end
      end

    query
    |> Ash.Query.load(total_hours_report: %{start_date: start_date, end_date: end_date})
    |> Ash.Query.sort(sort)
    |> Ash.Query.filter(total_hours_report["total_hours"] > 0)
  end
end

zachdaniel:

🔥