Let me lead by saying that this is a probably a bad idea and if you're starting from scratch you should instead look at using a Postgres extension like
ltree
instead.
Recently I was working on a client's application and they have a Location
resource which represents a physical location. Locations are a tree - ie they
have a belongs_to :parent_location, Location
relationship that if loaded
essentially performs a self-join on the locations table. Likewise it has a
has_many :child_locations, Location
relationship which can be used to load all
direct children of a given location. This has worked fine until now, when a new
requirement arrived from the client - "users can be assigned to locations, and
can only access their assigned location and any sub-locations". This sounds
straight-forward enough until you get to the "and any sub-locations" - as you'll
see that really throws a wrench into the works.
The first thing we need to do is add the relationships to our resources:
defmodule MyApp.User do
# ...
relationships do
# ...
has_one :location, MyApp.Location
end
end
defmodule MyApp.Location do
# ...
relationships do
# ...
belongs_to :user, MyApp.User
end
end
Next, we need to set up the policy to allow users to access their assigned location.
defmodule MyApp.Location do
# ...
policies do
policy action_type(:read) do
authorize_if relates_to_actor_via([:user])
end
end
end
It's about here that I realised how much trouble I was in. I needed a way to express "authorize if the location is assigned to the actor or any of the ancestor locations are assigned to the actor. After some thinking (and a bit of googling) I realised that I was going to have to use a recursive common table expression to find all the locations which are ancestors of the location being accessed.
A quick check of the Ecto hexdocs showed that Ecto does provide a way to execute recursive CTEs, but that behaviour is not exposed by AshPostgres
, so I was going to have to implement a manual relationship. To make this work.
I started by adding the relationship to the location resource:
defmodule MyApp.Location do
# ...
relationships do
# ...
has_many :ancestor_locations, MyApp.Location do
manual MyApp.Location.RecursiveAncestorRelationship
end
end
end
How we need to implement our manual relationship behaviour module:
defmodule MyApp.Location.RecursiveAncestorRelationship do
use Ash.Resource.ManualRelationship
@impl true
def load(locations, _opts, _context) do
# what goes here?
end
end
Sadly, the documentation for manual relationships is not great (which is part of why I'm writing this), so I had to do a bit of hunting to figure out that what is expects is a map with Location IDs as the keys and a list of related records as the values. So in our case we may want something like this:
# if locations contains [%MyApp.Location{id: 3, name: "Wellington", parent_location_id: 2}]
%{
3 => [
%MyApp.Location{id: 2, name: "North Island", parent_location_id: 1},
%MyApp.Location{id: 1, name: "New Zealand", parent_location_id: nil}
]
}
Now that we know what we need to generate, let's start by writing a SQL query which will get us what we want. Here's the query that will give us the location with the ID 3 and all it's ancestors.
WITH RECURSIVE location_tree AS (
SELECT
l0.id,
l0.name,
l0.parent_location_id
FROM
locations l0
WHERE
l0.id = 3
UNION
SELECT
l2.id,
l2.name,
l2.parent_location_id
FROM
locations l2
INNER JOIN
location_tree
ON
l2.id = location_tree.parent_location_id
)
SELECT
id,
name,
parent_location_id
FROM
location_tree
The query above is a little complicated, but it breaks down fairly simply. The
WITH RECURSIVE location_tree
creates a recursive CTE and gives it a name. The
first select within the CTE gives us the initial rows (ie select location 3).
The second select performs the recursion - Postgres will use the output of the
first select as the input to the second select, and then recursively pump the
last output into the second select until it emits no more rows. The UNION
says that we want all the unique rows from the results. If you want all the
results, regardless of uniqueness use UNION ALL
instead.
The last select query basically says "select the contents of the cte" which is what actually executes the query.
I have the following seed data in my database:
| id | name | parent_location_id |
| 1 | New Zealand | null |
| 2 | North Island | 1 |
| 3 | Wellington | 2 |
| 4 | Te Papa Tongarewa | 3 |
And running the query above returns:
| id | name | parent_location_id |
| 3 | Wellington | 2 |
| 2 | North Island | 1 |
| 1 | New Zealand | null |
So now we know we can get the data we need, let's start converting this into an Ecto query which will load the data we need.
I'm going to start by just executing the query once for every row, which is horribly inefficient but does provide us the data we need to validate our assumptions:
defmodule MyApp.Location.RecursiveAncestorRelationship do
use Ash.Resource.ManualRelationship
import Ecto.Query
@impl true
def load(locations, _opts, _context) do
Enum.reduce(locations, {:ok, %{}}, fn %{id: id, parent_location_id: parent_id},
{:ok, results} ->
immediate_parents =
MyApp.Location
|> where([l], l.id == ^parent_id)
recursion_query =
MyApp.Location
|> join(:inner, [l], lt in "location_tree", on: l.id == lt.parent_location_id)
ancestors_query =
immediate_parents
|> union(^recursion_query)
ancestors =
{"location_tree", Location}
|> recursive_ctes(true)
|> with_cte("location_tree", as: ^ancestors_query)
|> MyApp.Repo.all()
{:ok, Map.put(results, id, ancestors)}
end)
end
end
Now we can run the following in IEx:
iex> require Ash.Query
...> {:ok, [wellington]} = MyApp.Location |> Ash.Query.filter(id: 3) |> MyApp.read()
{:ok,
[
#MyApp.Location<
ancestor_locations: #Ash.NotLoaded<:relationship>,
child_locations: #Ash.NotLoaded<:relationship>,
parent_location: #Ash.NotLoaded<:relationship>,
__meta__: #Ecto.Schema.Metadata<:loaded, "locations">,
id: 3,
name: "Wellington",
parent_location_id: 2,
aggregates: %{},
calculations: %{},
__order__: nil,
...
>
]}
...> wellington |> MyApp.load(:ancestor_locations)
{:ok,
#MyApp.Location<
ancestor_locations: [
#MyApp.Location<
ancestor_locations: #Ash.NotLoaded<:relationship>,
child_locations: #Ash.NotLoaded<:relationship>,
parent_location: #Ash.NotLoaded<:relationship>,
__meta__: #Ecto.Schema.Metadata<:loaded, "location_tree">,
id: 2,
name: "North Island",
parent_location_id: 1,
aggregates: %{},
calculations: %{},
__order__: nil,
...
>,
#MyApp.Location<
ancestor_locations: #Ash.NotLoaded<:relationship>,
child_locations: #Ash.NotLoaded<:relationship>,
parent_location: #Ash.NotLoaded<:relationship>,
__meta__: #Ecto.Schema.Metadata<:loaded, "location_tree">,
id: 1,
name: "New Zealand",
parent_location_id: nil,
aggregates: %{},
calculations: %{},
__order__: nil,
...
>
],
child_locations: #Ash.NotLoaded<:relationship>,
parent_location: #Ash.NotLoaded<:relationship>,
__meta__: #Ecto.Schema.Metadata<:loaded, "locations">,
id: 3,
name: "Wellington",
parent_location_id: 2,
aggregates: %{},
calculations: %{},
__order__: nil,
...
>}
Awesome! Now we can go back and write our policy:
defmodule MyApp.Location do
# ...
policies do
policy action_type(:read) do
authorize_if relates_to_actor_via([:user])
authorize_if relates_to_actor_via([:ancestor_locations, :user])
end
end
end
I've created a user who is linked to the Wellington location, and so should be able to see both "Wellington" and "Te Papa Tongarewa". Let's try it:
iex> require Ash.Query
...> {:ok, [user]} = MyApp.User |> Ash.Query.filter(id: 1) |> MyApp.read()
...> MyApp.read(MyApp.Location, actor: user, authorize?: true)
** (RuntimeError) No such relationship for Elixir.MyApp.Location, required in `relates_to_actor` check
Oh no!
This is the point where I realised that I was in over my head and I asked Zach for help. It turns out that this supremely unhelpful error message is caused by the fact that Ash is trying to get the data layer (AshPostgres
) to turn the relationship into a JOIN
so that it can be used in the filter. Sadly, my implementation of Ash.Resource.ManualRelationship
is not enough for Ash to go on in this case. I also need to implement the even worse documented AshPostgres.ManualRelationship
behaviour.