Use Ash keyset pagination with Ecto queries?

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)