I’m trying to preload an associated schema and count of records (likes) in a many_to_many relation.
These are 3 relevant schemas:
schema "users" do
has_many :created_posts, Post, foreign_key: :poster_id
many_to_many(:liked_posts, Post, join_through: LikePost, on_replace: :delete)
...
schema "posts" do
field :likes_count, :integer, default: 0, virtual: true
belongs_to(:poster, User)
many_to_many(:liked_by, User, join_through: LikePost, on_replace: :delete)
...
schema "like_posts" do
belongs_to :user, User
belongs_to :post, Post
...
When I load the User, I’d like to preload both created_posts and the count of likes for each post (and it works):
def get_user!(id) do
Repo.get!(User, id)
|> Repo.preload(
created_posts: {posts_with_counts(), tags: []}, # this works!
liked_posts: {posts_with_counts(), tags: []} # this results in grouping error
)
end
def posts_with_counts do
from(p in post,
left_join: u in assoc(p, :liked_by),
group_by: p.id,
select_merge: %{likes_count: count(u.id)}
)
end
I also want to preload the liked_posts and the count of likes (by all users) for each post.
However the same query posts_with_counts for the liked_posts throws error:
(Postgrex.Error) ERROR 42803 (grouping_error) column “u2.id” must appear in the GROUP BY clause or be used in an aggregate function
So, there’s some subtle difference between querying associations in has_many and many_to_many relations which I struggle to understand.
What am I missing? Is there a better way to implement this?
Appreciate your advice!
