How can I filter by the number of has_many relationships with ash expr?

Ash Forum Ash Questions / Help
1

For example, when Author has many Post

read :list_authors_has_two_posts do
  filter expr(posts ??)
end

In SQL

SELECT authors.*
FROM authors
JOIN (
    SELECT author_id
    FROM books
    GROUP BY author_id
    HAVING COUNT(book_id) = 2
) AS filtered_authors ON authors.id = filtered_authors.author_id
2

Assuming you have an Author that has_many :posts:

You can set up a count aggregate in your Author resource, eg.

aggregates do 
  count :post_count, :posts
end

This will add a post_count attribute to your Author resource. Then you can use it in an action filter, eg.

read :list_authors_with_two_posts do 
  filter expr(post_count == 2)
end

Note that if you want to access post_count eg. in templates, you’ll need to load the aggregate in the action that loads the data. You can use it in filtering without loading it, Ash will load it for you behind the scenes, but it won’t return it because you haven’t specifically requested it.

read :list_authors_with_two_posts do 
  filter expr(post_count == 2)
  prepare build(load: [:post_count])
end
1 Like