Run an Ecto Query in an Ash Manual Action

Not getting the data you want from your Ash action? Try a manual action, in Ecto, hombre!

After Many Attempts

I had tried many things. Ash makes things so nice and tidy with its cool, terse macros for everything. But filter(expr()) was failing me. Manual calculations were working, but then I couldn't filter on them in actions. Then I read that raw SQL queries in Ash were possible, along with the admonition to use Ecto to build off the already-started query on the resource. So, I tried Ecto directly in Ash... and it worked!

Manual Ash Actions

To implement a manual action, use the manual macro:

defmodule MyResource do
  # ...
  actions
    read :special_thing do
      manual fn ash_query, ecto_query, context ->
        IO.inspect(ecto_query, label: "ecto_query")
      end
    end
  end
end

If you recompile and call this:

MyResource.special_thing()

You'll see that there's an Ecto.Query that's already been started and passed into this callback.

ecto_query: #Ecto.Query<from p0 in MyResource, as: 0,
 select: struct(m0, [:id, :name])>

Ecto Queries in Ash

Now that you have ecto_query, you can start writing Ecto-style queries, not using Ash.Query or Ash expr.

This is possible because, as the docs for AshPostgres.Repo say:

This repo is a thin wrapper around an Ecto.Repo.

So let's write some Ecto queries:

# ...
read :special_thing do
  manual fn _ash_query, ecto_query, _context ->
    # This query is doable in an expr() and doesn't require a manual action.
    # My actual query is distractingly-big for this example.
    updated_query = 
      from mr in ecto_query,
        join: f in assoc(mr, :floop),
        join: ft in assoc(mr, :floop_thing),
        where: ft.wow_factor == "mega"
    # ...
  end
end

Now how to execute the query? Well, AshPostgres.Repo is a wrapper around Ecto.Repo, right? Somewhere, you should have a repo.ex for Ash that looks in part like:

defmodule MyRepo do
  use AshPostgres.Repo, otp_app: :my_app
  # ...
end

So let's use that repo to execute our query:

MyRepo.all(updated_query)

But we need to return {:ok, [list, of, results]} from this manual action callback:

{:ok, MyRepo.all(updated_query)}

But what about the potenial error case? Ash.Query.read will return {:ok, results} or {:error, reason}. But Ecto.Repo.all throws an exception in the error case, so we need a try/rescue block:

try do
  {:ok, MyRepo.all(updated_query)}
rescue
  e ->
    {:error, e}
end

The Entire Solution

At this point, we should be golden. We can define our action in a manual callback, we can define our data set with a flexible Ecto query, and we can return that for the action.

Here's all the relevant code together:

defmodule MyResource do
  # ...
  import Ecto.Query

  actions
    read :special_thing do
      manual fn ash_query, ecto_query, context ->
        updated_query = 
          from mr in ecto_query,
            join: f in assoc(mr, :floop),
            join: ft in assoc(mr, :floop_thing),
            where: ft.wow_factor == "mega"
        try do
          {:ok, MyRepo.all(updated_query)}
        rescue
          e ->
            {:error, e}
        end
      end
    end
  end
end

Carry on, and act manually!

Optional: Only One Select Error

When we join to the ecto_query query, we don't have to select again. Our resource is already selected. If we select again, Ecto growls at us:

** (Ecto.Query.CompileError) only one select expression is allowed in query
    (my_app 0.1.0) lib/my_app/my_domain/resources/my_resource.ex:166: MyResource.manual_0_generated_05E2D9A32863AE2D58B94772EFC75701/3
    (ash 3.3.3) lib/ash/actions/read/read.ex:2440: Ash.Actions.Read.run_query/4
    ...

Change a query that might look like this:

updated_query = 
  from mr in ecto_query,
    # joins and wheres...
    select: mr

To this:

updated_query = 
  from mr in ecto_query,
    # joins and wheres...