Problem to get every comment count(likes) of a post Ecto

Questions / Help
,
1

Hello, I have read this post, but I cant get every comment count user likes.

my comment Schema:

  schema "comments" do
    field(:description, :string, null: false)
    field(:section_id, :binary_id, null: false)
    belongs_to :users, User, foreign_key: :user_id, type: :binary_id
    has_many :comments_likes, CommentLike, foreign_key: :comment_id
    timestamps(type: :utc_datetime)
  end

and my comment like Schema:

  schema "comments_likes" do

    belongs_to :users, User, foreign_key: :user_id, type: :binary_id
    belongs_to :comments, Comment, foreign_key: :comment_id, type: :binary_id

    timestamps(type: :utc_datetime)
  end

now I want to show all the comments with their likes counted, the query I have written:

  def comments(section_id, condition: %{paginate: {page, page_size}}) do
    from(com in Comment,
    where: com.section_id == ^section_id,
    join: user in assoc(com, :users),
    left_join: like in subquery(likes()),
    select: %{
      id: com.id,
      description: com.description,
      ......
      user_username: user.username,
      like: like
    })
    |> MishkaDatabase.Repo.paginate(page: page, page_size: page_size)
  end 

  def likes() do
    from like in CommentLike,
    group_by: like.user_id,
    select: %{count: count(like.id)}
  end

As you can see in the code above, I create a subquery to count comment likes, but the result I have is duplicate and the counter is no true:

[
  %{
    description: "this is a test comment we need",
    id: "e81a61db-6fa2-4e3c-b592-71a8341ef1e7",
    like: %{count: 1},
    ...
    user_username: "user1"
  },
  %{
    description: "this is a test comment we need",
    id: "e81a61db-6fa2-4e3c-b592-71a8341ef1e7",
    like: %{count: 1},
    priority: :none,
    ...
    user_username: "user1"
  },
  %{
    description: "test 2",
    id: "930f3efd-05d5-4d3f-b9df-a69812856184",
    like: %{count: 1},
    ...
    user_username: "user2"
  },
  %{
    description: "test 2",
    id: "930f3efd-05d5-4d3f-b9df-a69812856184",
    like: %{count: 1},
    ...
    user_username: "user2"
  }
]

I am using scrivener_ecto.

Thanks

1 Like
2

Poooof … after 2 days :frowning:

I needed to change two things:

at first I should add a new parameter into my subquery:

before:

  def likes() do
    from(like in CommentLike,
    group_by: like.comment_id,
    select: %{count: count(like.id)})
  end

after:

  def likes() do
    from(like in CommentLike,
    group_by: like.comment_id,
    select: %{count: count(like.id), comment_id: like.comment_id})
  end

comment_id was added

and now I should edit my main qeury:

left_join: like in subquery(CommentLike.likes()),
on: com.id == like.comment_id,

I added on into my query

If I’m wrong please help.

1 Like