Reporting with Ash?

Ash Forum Ash Questions / Help
1

Does anyone create reports with Ash to summarize resources?

I asked about querying SELECT count(1), title FROM posts GROUP BY title with Ash, but Zach said we should use Ecto directly.

Since we’re using Ash as our application framework, I’m uncertain about when to use Ash queries versus Ecto queries for reporting functionality.

2

It depends largely on the reporting. The primary way actions are expected to behave is to return 1+ of the resource that action is referencing. For actions that return something other than the resource itself, you can use Generic Actions to use Ecto. The perk of doing it this way is you still have access to many of Ash’s features, e.g. authz policies.

If I understand correctly, you could also do simple counts directly on the domain with define_calculation. I could be wrong on that though, haven’t tried it yet!

1 Like
3

Depends on the complexity of the report. If it requires simple calculations or aggregates then I use the resource aggregates and calculations. But if it is a more complex report, I use the generic action to run ecto query.

Untested example that should work:

action :my_report, {:array, :map} do
  argument :inserted_at, :datetime, allow_nil?: false

  run fn input, _ ->
    query = """
      SELECT id, name, inserted_at
      FROM users
      WHERE inserted_at = $1
      UNION
      SELECT id, name, inserted_at
      FROM customers
      WHERE inserted_at = $1
    """

    case Ecto.Adapters.SQL.query(MyApp.Repo, query, [input.inserted_at]) do
      {:ok, %Postgrex.Result{rows: rows, columns: columns}} ->
        results = Enum.map(rows, fn row ->
          columns
          |> Enum.zip(row)
          |> Map.new()
        end)
        {:ok, results}
      {:error, error} ->
        {:error, error}
    end
  end
end
4

It’s worth pointing out that all Ash resources are Ecto schemas too. from row in MyApp.Accounts.User etc also works :slight_smile:

2 Likes