I have an Ash query that I need to convert into an Ecto query to do some more complex manipulations (group by, etc) using the Ash.Query.data_layer_query/2 function.
Is there any way for me to either transform my Ecto query back into an Ash.Query struct so I can use its pagination support or use Ash pagination support directly in my ecto query results?
I don’t believe there is a way to do that, no. What you can potentially do is use the modify_query option of read actions and do your grouping there. It depends a lot on what you’re doing though.
The problem is that the query will group based on the joined resource and return it plus some aggregations.
Here is the query:
select
e1.id,
e1.score,
count(t0.*) purchases,
sum(t0.buy_price) purchases_total_amount,
count(t0.*) filter (where sell_price is not null) sales,
sum(t0.sell_price) filter (where sell_price is not null) sales_total_amount,
sum(t0.profit) total_profit
from transactions as t0
left outer join entities as e1 on t0.entity_id = e1.id
where t0.buy_date between '2022-02-12' and '2024-02-12'
and e1.last_year_buy_records < 100
group by e1.id
order by t.score desc;
So, I have an action from the Transaction resource that returns the following ash query (showing in sql):
from transactions as t0
left outer join entities as e1 on t0.entity_id = e1.id
where t0.buy_date between '2022-02-12' and '2024-02-12'
and e1.last_year_buy_records < 100
order by t.score desc
From that, I’m transforming it into an ecto query and doing the group_by part and select part.
If I use the read action, if implemented from the Transaction resource, it would expect to return transactions, but it actually returns entities which is another resource.
If I implement it in the Entity resource, then my from part of the query will use the entities table instead of the transactions one.
Also, the reason that I’m not doing that query directly from the Entity resource and using aggregations, is because all my filters (the where part, which I removed a lot of lines) are all handled by my form and filter it based in transactions columns.
Hm…it might be possible to rewrite your query to be based on entities, and make it an action on entities, which would simplify this significantly.
Like, you’ve got count(t0.*) purchases which appears to be “the number of transactions for a given entity that was between x and y date”.
You could do that as a calculation on entities like so:
calculate :purchase_count, :integer, expr(count(:transactions, query: [filter: [
buy_date < ^arg(:start_date) and by_date > ^arg(:end_date)
]])) do
argument :start_date, :utc_datetime, allow_nil?: false
argument :end_date, :utc_datetime, allow_nil?: false
end
calculate :sale_count, :integer, expr(count(:transactions, query: [filter: [
not(is_nil(sell_price)) and expr(buy_date < ^arg(:start_date) and by_date > ^arg(:end_date))
]])) do
argument :start_date, :utc_datetime, allow_nil?: false
argument :end_date, :utc_datetime, allow_nil?: false
end
...and so on
Specifying each calculation this way is pretty verbose, for sure but you’d have a flexible solution that would then support loading it directly, i.e
Entity
|> Ash.Query.load(
purchase_count: %{start_date: start_date, end_date: end_date},
sale_count: %{start_date: start_date, end_date: end_date},
...and so on
)
Otherwise you’re probably stuck implementing your own custom logic outside of Ash.
Hi Zach.
I tried something like this:
calculate :purchase_count,
:integer,
expr(
count(:transactions,
query: [
filter: [
buy_date < ^arg(:start_date) and buy_date > ^arg(:end_date)
]
]
)
) do
argument :start_date, :date, allow_nil?: false
argument :end_date, :date, allow_nil?: false
end
Considering that buy_date is a Transaction attribute, should I be able to access it in this query?
Using the suggested calculate I got this error:
-> mix compile
Compiling 1 file (.ex)
error: undefined variable "buy_date"
│
237 │ buy_date < ^arg(:start_date) and buy_date > ^arg(:end_date)
│ ^^^^^^^^
│
└─ lib/pacman/markets/entity.ex:237:23: Pacman.Markets.Entity (module)
== Compilation error in file lib/pacman/markets/entity.ex ==
** (CompileError) lib/pacman/markets/entity.ex: cannot compile module Pacman.Markets.Entity (errors have been logged)
(elixir 1.16.0) expanding macro: Kernel.and/2
lib/pacman/markets/entity.ex:237: Pacman.Markets.Entity (module)
(ash 2.18.1) expanding macro: Ash.Expr.expr/1
As a reference I created an aggregate to list buy dates like
list :transaction_dates, :transactions, :buy_date
and this part works to access buy_date
Sorry, my bad
filter: [ :buy_date < arg(:start_date) and :buy_date > arg(:end_date)]
this way its compile
The filter must be wrapped in its own expr. Using atoms isn’t what you want.
Yeah. I realized when it returned zero =P
Is there any way to use sum instead of count in expr?
Yes, you will just need to add the field option, sum(foo.bar, field: :field)
Abusing the topic a little (if you prefer, I’ll open a new one).
Is there a way to move this expr to an individual module and use it like this?
I have this calculate
calculate :sales_total_amount, :integer do
calculation expr(
sum(:transactions,
field: :sell_price,
query: [
filter: [
expr(
fragment(
"st_dwithin(?, ?::geography, ?)",
property_geography,
^arg(:point),
^arg(:distance_in_meters)
) and
fragment(
"(? between ? and ?)",
buy_date,
^arg(:start_date),
^arg(:end_date)
) and
not is_nil(sell_price)
)
]
]
)
)
argument :start_date, :date, allow_nil?: false
argument :end_date, :date, allow_nil?: false
argument :distance_in_meters, :float, allow_nil?: false
argument :point, :geo_any, allow_nil?: false
end
I would like to have something like
calculate :sales_total_amount, :integer do
calculation CalculateHere
argument :start_date, :date, allow_nil?: false
argument :end_date, :date, allow_nil?: false
argument :distance_in_meters, :float, allow_nil?: false
argument :point, :geo_any, allow_nil?: false
end
and a module CalculateHere similar with Ash.Calculation like:
defmodule CalculateHere do
@moduledoc false
use Ash.Calculation
import Ash.Query
# it does not work, how to do it?
def select(query, _opts, _context) do
start_date = get_argument(query, :start_date)
end_date = Ash.Query.get_argument(query, :end_date)
query
|> Ash.Query.filter(
expr(
count(:transactions,
query: [
filter: [
# expr(buy_date > ^arg(:start_date) and buy_date < ^arg(:end_date))
expr(
fragment(
"(? between ? and ?)",
buy_date,
^start_date,
^end_date
) and ...
)
]
]
)
)
)
end
end
If you have an example or documentation talking about it, I would really appreciate it.
You’re looking for this 
defmodule YourCalculation do
use Ash.Calculation
import Ash.Expr
import Ash.Filter.TemplateHelpers
def expression(_opts, _context) do
expr(
sum(:transactions,
field: :sell_price,
...
)
)
end
end
Hi Zach!
Based on the calculations created in this topic, like:
calculate :purchases_total_amount, :integer do
calculation expr(
sum(:transactions,
field: :buy_price,
query: [
filter: [
expr(
fragment(
"st_dwithin(?, ?::geography, ?)",
property_geography,
^arg(:point),
^arg(:distance_in_meters)
) and
fragment(
"(? between ? and ?)",
buy_date,
^arg(:start_date),
^arg(:end_date)
)
)
]
]
)
)
Load is working great, just like:
Ash.Query.load(
purchases_total_amount: %{
start_date: start_date,
end_date: end_date,
point: geo_point,
distance_in_meters: distance
}
)
Is there a way to sort (desc/asc) by this calculation? I tried to just use Ash.Query.sort(purchases_toltal_amount: : desc) but got this error:
Invalid value provided for calculation argument start_date in purchases_total_amount: : is required.
nil
at sort
How to send argument to a load function?
Found it
© Copyright Elixir Forum | Terms | Privacy & Cookies
