How to count a record from another table display it in for loop

@PovilasKorop Is not the same, you can check the difference if you compare these ;)

@MohamedTammam I need to show total likes for each comment.

likes can get from likecom table that there is 'user_id' , 'owner_id', 'comment_id'

I think need this where in the code but I dont know how can make it to equal with comment_id

'likecom' => function ($lq) { 
				$lq->where('comment_id', ???);
}

@johnpapi This part $q->withCount('likecoms') already adds where comment_id = ? into the SQL query.

@MohamedTammam but what I have to add in the << ? >> something to equal with comment_id

I tried to add $lq->where('comment_id', $topic->comment->id); but doesnt work....

@johnpapi Yes, it will not work. It will added in the SQL query in a join statement.

@MohamedTammam so there is no option to show total likes for each comment?

@johnpapi I showed the code already to do that. using my code and in the foreach writing $comment->likecoms_count will show you that.

@MohamedTammam but this code count all likecoms I need to count per comment_id .

@johnpapi That code will count all likecoms for each comment.

@MohamedTammam yes but I need to count all likecoms on each comment where comment_id . check screenshot and you will understand: https://i.imgur.com/haVrlqE.png

@johnpapi My code should do what you're asking for. It will get user comments and the number of likecoms each comment has.

@MohamedTammam but the code is missing. Check screenshot: https://i.imgur.com/iejDQMr.png

$comments = Comment::where('topic_id', $topic->id)->with('user', function ($q) { 
                                $q->withCount([
                                    'comment' => fn($q) => $q->withCount('likecoms'),
                                    'like',
                                    'likecom' => function ($lq) { 
                                    	$lq->where('comment_id', ???);
                                    }
                                    ,'topic' => function ($tq) { 
                                    $tq->where('status', 1);
                                    
                                }]);
                            })
                            ->orderBy('id', 'ASC')->paginate(getPaginate(20));

@johnpapi I do, and I already answered. You need to make a join statement with the condition of comment.comment_id = likecoms.comment_id and you can't do that in PHP, it needs to be in SQL. The code that I submitted, does that.

@MohamedTammam thanks for your reply. Can you please mention this (join) code that I have to put in controller in order to work? Thanks again

@johnpapi 'comment' => fn($q) => $q->withCount('likecoms'),

@mohamedtammam did you check it?

@johnpapi I said it multiple times. If you want to get the number of likecoms for reach comment you need to use withCount after setting up your relationship. Then using $comment->likecoms_count. That straight from the docs.

@MohamedTammam Doesn't make sense, as I have already did this and doesnt work....

Check my model to check the relationship:

class Comment extends Model {
    use Searchable;
    public function user() {
        return $this->belongsTo(User::class, 'user_id');
    }
    public function topic() {
        return $this->belongsTo(Topic::class, 'user_id');
    }
    
    public function likecoms() {
        return $this->belongsTo(LikeComment::class, 'comment_id');
    }
    
}

I really dont know what is the issue in my code but doesnt work your suggestion.... :/

@johnpapi how can a comment belong to a like?

surely you need a like to belong to comment and comment to have many likes

Therefore your likecoms relationship is wrong

@Snapey So I have to change the belongsTo to hasMany ? Like this:

public function likecoms() {
        return $this->hasMany(LikeComment::class, 'comment_id');

@johnpapi Yes

@Snapey

Now I have these:

Model:

class Comment extends Model {
    use Searchable;
    public function user() {
        return $this->belongsTo(User::class, 'user_id');
    }
    public function topic() {
        return $this->belongsTo(Topic::class, 'user_id');
    }
    
    public function likecoms() {
        return $this->hasMany(LikeComment::class);
    }
    
}

Controller:

$comments = Comment::where('topic_id', $topic->id)->with('user', function ($q) { 
                                $q->withCount([
                                    'comment' => fn($q) => $q->withCount('likecoms'),
                                    'like',
                                    'likecom',
                                    'topic' => function ($tq) { 
                                    $tq->where('status', 1);
                                    
                                }]);
                            })
                            ->orderBy('id', 'ASC')->paginate(getPaginate(20));            

and Blade:

 @if($comment->user->likecoms_count) 
                            <i class="white-font size-btn fa fa-thumbs-up"></i> @lang('You Liked!') ({{ $comment->user->likecoms_count }})
                        @else 
                            <i class="white-font size-btn fa fa-thumbs-o-up"></i> @lang('Like') ({{ $comment->user->likecoms_count }})
                        @endif

But doesnt count anything, show empty. Something is wrong/missing....

@johnpapi you are just trying to show if a user liked a comment?

Like this question is the same as your topic? I can comment and I can like other people's responses?

@Snapey yes, correct. then count the likes on each comment.

@johnpapi You have a pivot table joining comments to users? What's the migration?

@Snapey Yes, there is user_id in comment table: https://i.imgur.com/gMvqzpN.png

@Snapey Also here is the like_comments table: https://i.imgur.com/RjmugFt.png

@johnpapi please post code not pictures.

What is the purpose of owner_id in relation to likes?

@Snapey is owner of topic. But doesnt matter this. Did you understand my issue?

@johnpapi create a new relationship in the Comment model

public function likedBy()
{
	return $this->belongsToMany(User::class, 'like_comments);
}

query

$comments = Comment::query()
				->where('topic_id', $topic->id)
				->with('user','likedBy:id')
				->orderBy('id', 'ASC')
				->paginate(getPaginate(20));

view, to display the count of likes

{{ $comment->likedBy->count() }}

view, to display the buttons

 <a href="{{route('user.topic.like.comment',$comment->id)}}" class="btn btn-md btn--gradient py-0 m-1">
                     
			@if($comment->likedBy->contains('id',Auth::id())) 
                            <i class="white-font size-btn fa fa-thumbs-up"></i> @lang('You Liked!') ({{ $comment->user->likecom_count }})
			@else 
                            <i class="white-font size-btn fa fa-thumbs-o-up"></i> @lang('Like') ({{ $comment->user->likecom_count }})
			@endif
</a>

@Snapey but I see in controller code is changed a lot. Can we keep the same code just add the likedBy:id?\

 $comments = Comment::where('topic_id', $topic->id)->with('user', function ($q) { 
                                $q->withCount([
                                    'comment',
                                    'like',
                                    'likecom',
                                    'topic' => function ($tq) { 
                                    $tq->where('status', 1);
                                    
                                }]);
                            })
                            ->orderBy('id', 'ASC')->paginate(getPaginate(20));