How do I join two tables in a read action?

How do I join tables A (has_many B) and B (belongs_to A) inside a read action?

Is there any documentation describing how to do all the query stuff that we have on Ecto.Query (Ecto.Query — Ecto v3.11.2)?

Thanks.

There is no way to do explicit joins in Ash. This is part of the declarative nature of the framework. An explicit join is an “imperative” instruction. The more important question is why do you want to join? Do you want to load some related data?

Resource
|> Ash.Query.load(:relationship)

Do you want to filter where something is true about related data?

Resource
|> Ash.Query.filter(related.data.foo == true)

Do you want to sort on related data?

Resource
|> Ash.Query.sort(Ash.Sort.expr_sort(first(related.data, field: :foo))) 

Sorting like this will be made better in future updates, but currently the only place you can access related things without using an “inline aggregate” (first in this case) is in Ash.Query.filter.

The reason for this has to do with the way filters differ in a declarative sense from sorts. Happy to explain in more detail.

Declarative querying

While it can sometimes be tempting to specifically say “join to X”, we explicitly do not provide a built in way to do that. Not all data layers can join, and joins will break the composition capabilities inherent in things like calculations and aggregates.

Escape hatches

If you absolutely must join, you can do something like this:

read :read_action do
  modify_query fn query, ecto_query, context -> 
    {:ok, from row in ecto_query, join: ....}
  end
end
2 Likes

Thanks, @zachdaniel. This is what I need. How do I do that inside a read action?

read :read do
  filter expr(related.data.foo == true)
end

is one way.

read :read do
  prepare fn query, _ -> 
    Ash.Query.filter(query, related.data.foo == true)
  end
end

is another.

2 Likes

important note: you can only have on filter statement in your action currently. It’s on our list to support multiple, but the latest one specified will overwrite the first one. It was added before prepare was added, which you can have any number of and do anything with.

1 Like