How to create a query to load all posts and theirs each counted likes with the user id liked or not

Hello, I want to load all posts of my database and count each record likes with group_by and finally I need each user enters to my site, check the user is seeing this records if liked or not , then show a parameter in my select true or false.

my current query I have written

from(post in Post,
      join: cat in assoc(post, :blog_categories),
      left_join: like in assoc(post, :blog_likes)
      group_by: [,, like.post_id],
      select: %{
        title: post.title,
        category_title: cat.title,
        like_count: count(

but I need a select like this:

select: %{
  title: post.title,
  category_title: cat.title,
  like_count: count(,
  user_liked: true

I do not want to filter my records with where, because I need to load all of those, I just need to know if my user like this post or not like twitter when you like a tweet the heart icon fill with red color

my relations:

posts -> likes
categories -> posts
users -> likes

I made this with subquery

left_join: liked_user in subquery(UserLiked.user_liked),
      on: liked_user.user_id == ^user_id and liked_user.post_id ==

the query I added into subquery

from(like in BlogLike,
    select: %{post_id: like.post_id, user_id: like.user_id})

it works for now

1 Like