I want to calculate the number of unread Posts for each User in a list of User IDs. Read (verb, past tense) Posts are tracked with PostView, by recording the ID of the last read Post for a given User ID.

Here’s the SQL of what I need:

select
  u.id user_id,
  case
    when pv.user_id is not null then pv.count
    else p.count
  end unread_posts_count
from users u
left join (
  select pv.user_id, count(p2.id) "count"
  from post_views pv
  left join posts p1 on p1.id = pv.last_post_id
  left join posts p2 on p2.inserted_at > p1.inserted_at and p2.archived_at is null
  group by pv.member_id
) tp on tp.user_id = u.id
left join (
  select count(p.id), t.id tenant_id
  from tenants t
  left join posts p on p.tenant_id = t.id
  group by t.id
) p on p.tenant_id = u.tenant_id
where u.id in $1

It seems Ash wants you to use the aggregate DSL combined with relationships to do this. I modified test/actions/has_many_test.exs from the Ash code repo to approximate what my app is doing. Also, I added an attribute to the respective resources to represent a tenant ID, because my app is multitenant.

  defmodule Post do
  ...
      attribute :tenant_id, :string, public?: true
  ...
  end

  defmodule PostView do
    @moduledoc false
    use Ash.Resource,
      domain: Ash.Test.Actions.HasManyTest.Domain,
      data_layer: Ash.DataLayer.Ets

    ets do
      private?(true)
    end

    actions do
      default_accept :*
      defaults [:read, :destroy, create: :*, update: :*]
    end

    attributes do
      uuid_primary_key :id

      attribute :last_post_id, :uuid, public?: true
      attribute :user_id, :uuid, public?: true
    end

    relationships do
      belongs_to :user, Ash.Test.Actions.HasManyTest.User do
        source_attribute :user_id
        destination_attribute :id
        public?(true)
      end

      belongs_to :last_post, Post do
        source_attribute :last_post_id
        destination_attribute :id
        public?(true)
      end
    end
  end

  defmodule User do
    @moduledoc false
    use Ash.Resource,
      domain: Ash.Test.Actions.HasManyTest.Domain,
      data_layer: Ash.DataLayer.Ets

    ets do
      private?(true)
    end

    actions do
      default_accept :*
      defaults [:read, :destroy, create: :*, update: :*]
    end

    attributes do
      uuid_primary_key :id

      attribute :name, :string, public?: true
      attribute :tenant_id, :string, public?: true
    end

    relationships do
      has_one :post_view, PostView do
        source_attribute :id
        destination_attribute :user_id
        public?(true)
      end

      has_many :unread_posts, Post do
        source_attribute :tenant_id
        destination_attribute :tenant_id
        public?(true)

        # This is eventually what I want to do, but it always produces no matches
        # filter expr(inserted_at > parent(post_view.last_post.inserted_at))

        # After some experimentation, I found that this filters as expected
        filter expr(id != parent(post_view.last_post_id))
        # But this never matches, even though it seems equivalent
        filter expr(id != parent(post_view.last_post.id))
      end
    end
  end

So it seems that when the parent(...) expression reaches more than two layers deep, nothing matches, but if it reaches only two layers, it does. I also tried parent(...).id, but that gave an error.