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.
, thank you for the detailed reproduction.
hours down the drain not realizing it was just a logic issue. As you had alluded to in the comments in your test, you need a filter like this:
