See Manual Relationships for an idea of manual relationships in general. Manual relationships allow for expressing complex/non-typical relationships between resources in a standard way. Individual data layers may interact with manual relationships in their own way, so see their corresponding guides.
# in the resource
relationships do
has_many :tickets_above_threshold, Helpdesk.Support.Ticket do
manual Helpdesk.Support.Ticket.Relationships.TicketsAboveThreshold
end
end
# implementation
defmodule Helpdesk.Support.Ticket.Relationships.TicketsAboveThreshold do
use Ash.Resource.ManualRelationship
use AshPostgres.ManualRelationship
require Ash.Query
require Ecto.Query
def load(records, _opts, %{query: query, actor: actor, authorize?: authorize?}) do
# Use existing records to limit resultds
rep_ids = Enum.map(records, & &1.id)
# Using Ash to get the destination records is ideal, so you can authorize access like normal
# but if you need to use a raw ecto query here, you can. As long as you return the right structure.
{:ok,
query
|> Ash.Query.filter(representative_id in ^rep_ids)
|> Ash.Query.filter(priority > representative.priority_threshold)
|> Helpdesk.Support.read!(actor: actor, authorize?: authorize?)
# Return the items grouped by the primary key of the source, i.e representative.id => [...tickets above threshold]
|> Enum.group_by(& &1.representative_id)}
end
# query is the "source" query that is being built.
# _opts are options provided to the manual relationship, i.e `{Manual, opt: :val}`
# current_binding is what the source of the relationship is bound to. Access fields with `as(^current_binding).field`
# as_binding is the binding that your join should create. When you join, make sure you say `as: ^as_binding` on the
# part of the query that represents the destination of the relationship
# type is `:inner` or `:left`.
# destination_query is what you should join to to add the destination to the query, i.e `join: dest in ^destination-query`
def ash_postgres_join(query, _opts, current_binding, as_binding, :inner, destination_query) do
{:ok,
Ecto.Query.from(_ in query,
join: dest in ^destination_query,
as: ^as_binding,
on: dest.representative_id == as(^current_binding).id,
on: dest.priority > as(^current_binding).priority_threshold
)}
end
def ash_postgres_join(query, _opts, current_binding, as_binding, :left, destination_query) do
{:ok,
Ecto.Query.from(_ in query,
left_join: dest in ^destination_query,
as: ^as_binding,
on: dest.representative_id == as(^current_binding).id,
on: dest.priority > as(^current_binding).priority_threshold
)}
end
# _opts are options provided to the manual relationship, i.e `{Manual, opt: :val}`
# current_binding is what the source of the relationship is bound to. Access fields with `parent_as(^current_binding).field`
# as_binding is the binding that has already been created for your join. Access fields on it via `as(^as_binding)`
# destination_query is what you should use as the basis of your query
def ash_postgres_subquery(_opts, current_binding, as_binding, destination_query) do
{:ok,
Ecto.Query.from(_ in destination_query,
where: parent_as(^current_binding).id == as(^as_binding).representative_id,
where: as(^as_binding).priority > parent_as(^current_binding).priority_threshold
)}
end
endManual relationships can be very powerful, as they can leverage the full power of Ecto to do arbitrarily complex things. Here is an example of a recursive relationship that loads all employees under the purview of a given manager using a recursive CTE.
While the below is very powerful, if at all possible we suggest using ltree for hierarchical data. Its built in to postgres and AshPostgres has built in support for it. For more, see:
AshPostgres.Ltree.
Keep in mind this is an example of a very advanced use case, not something you'd typically need to do.
defmodule MyApp.Employee.ManagedEmployees do
@moduledoc """
A manual relationship which uses a recursive CTE to find all employees managed by a given employee.
"""
use Ash.Resource.ManualRelationship
use AshPostgres.ManualRelationship
alias MyApp.Employee
alias MyApp.Repo
import Ecto.Query
@doc false
@impl true
@spec load([Employee.t()], keyword, map) ::
{:ok, %{Ash.UUID.t() => [Employee.t()]}} | {:error, any}
def load(employees, _opts, _context) do
employee_ids = Enum.map(employees, & &1.id)
all_descendants =
Employee
|> where([l], l.manager_id in ^employee_ids)
|> recursive_cte_query("employee_tree", Employee)
|> Repo.all()
employees
|> with_descendants(all_descendants)
|> Map.new(&{&1.id, &1.descendants})
|> then(&{:ok, &1})
end
defp with_descendants([], _), do: []
defp with_descendants(employees, all_descendants) do
Enum.map(employees, fn employee ->
descendants = Map.get(all_descendants, employee.id, [])
%{employee | descendants: with_descendants(descendants, all_descendants)}
end)
end
@doc false
@impl true
@spec ash_postgres_join(
Ecto.Query.t(),
opts :: keyword,
current_binding :: any,
as_binding :: any,
:inner | :left,
Ecto.Query.t()
) ::
{:ok, Ecto.Query.t()} | {:error, any}
# Add a join from some binding in the query, producing *as_binding*.
def ash_postgres_join(query, _opts, current_binding, as_binding, join_type, destination_query) do
immediate_parents =
from(destination in destination_query,
where: parent_as(^current_binding).manager_id == destination.id
)
cte_name = "employees_#{as_binding}"
descendant_query =
recursive_cte_query_for_join(
immediate_parents,
cte_name,
destination_query
)
case join_type do
:inner ->
{:ok,
from(row in query,
inner_lateral_join: descendant in subquery(descendant_query),
on: true,
as: ^as_binding
)}
:left ->
{:ok,
from(row in query,
left_lateral_join: descendant in subquery(descendant_query),
on: true,
as: ^as_binding
)}
end
end
@impl true
@spec ash_postgres_subquery(keyword, any, any, Ecto.Query.t()) ::
{:ok, Ecto.Query.t()} | {:error, any}
# Produce a subquery using which will use the given binding and will be
def ash_postgres_subquery(_opts, current_binding, as_binding, destination_query) do
immediate_descendants =
from(destination in Employee,
where: parent_as(^current_binding).id == destination.manager_id
)
cte_name = "employees_#{as_binding}"
recursive_cte_query =
recursive_cte_query_for_join(
immediate_descendants,
cte_name,
Employee
)
other_query =
from(row in subquery(recursive_cte_query),
where:
row.id in subquery(
from(row in Ecto.Query.exclude(destination_query, :select), select: row.id)
)
)
{:ok, other_query}
end
defp recursive_cte_query(immediate_parents, cte_name, query) do
recursion_query =
query
|> join(:inner, [l], lt in ^cte_name, on: l.manager_id == lt.id)
descendants_query =
immediate_parents
|> union(^recursion_query)
{cte_name, Employee}
|> recursive_ctes(true)
|> with_cte(^cte_name, as: ^descendants_query)
end
defp recursive_cte_query_for_join(immediate_parents, cte_name, query) do
# This is due to limitations in ecto's recursive CTE implementation
# For more, see here:
# https://elixirforum.com/t/ecto-cte-queries-without-a-prefix/33148/2
# https://stackoverflow.com/questions/39458572/ecto-declare-schema-for-a-query
employee_keys = Employee.__schema__(:fields)
cte_name_ref =
from(cte in fragment("?", literal(^cte_name)), select: map(cte, ^employee_keys))
recursion_query =
query
|> join(:inner, [l], lt in ^cte_name_ref, on: l.manager_id == lt.id)
descendants_query =
immediate_parents
|> union(^recursion_query)
cte_name_ref
|> recursive_ctes(true)
|> with_cte(^cte_name, as: ^descendants_query)
end
endWith the above definition, employees could have a relationship like this:
has_many :managed_employees, MyApp.Employee do
manual MyApp.Employee.ManagedEmployees
endAnd you could then use it in calculations and aggregates! For example, to see the count of employees managed by each employee:
aggregates do
count :count_of_managed_employees, :managed_employees
end