Calculation with arguments used in another calculation for sorting

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

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:

12 Replies

frankdugan3OP•2y ago

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

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]

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

ZachDaniel•2y ago

👋 okay, so there isn't currently anything to reuse a calculation that is being selected, unfortunately so it will depend on how postgres does its work TBH In fact, this might be more than problematic for your case, you'll have to test it 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? This will likely need to be optimized at the ash_postgres layer, but it is complicated. Lemme show you what it would look like in ash-land, and then we can see if its necessary to optimize

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])

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])

frankdugan3OP•2y ago

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

ZachDaniel•2y ago

yep, it can 🙂

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

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

frankdugan3OP•2y ago

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

ZachDaniel•2y ago

yeah, I'm pretty sure you can say sort({Calculation.new(....), {:asc, %{arg1: :value}}}) maybe try it out 😆

frankdugan3OP•2y ago

🤯 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

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

== 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? (it's a json field, so string-keyed)

ZachDaniel•2y ago

^arg(:field) on that case

frankdugan3OP•2y ago

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

ZachDaniel•2y ago

oh right [] expects static values you might need to use a fragment

frankdugan3OP•2y ago

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

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

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•2y ago

🔥

Gaming

Programming

Calculation with arguments used in another calculation for sorting

Did you find this page helpful?