Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

johnpapi's avatar

how to count the record from another table display it in for loop

I have 2 tables. topics and comments with topic_id , user_id, comment

My Controller:

public function detail($slug, $id)
    {
        $topic = Topic::available()->withCount('comments')->findOrFail($id);
        
         $comments = Comment::where('topic_id', $topic->id)->with('user')->orderBy('id', 'ASC')->withCount('user','topic')->paginate(getPaginate(20));

Comment Model is:

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');
    }
}

and blade has a foreach

@foreach ($comments as $comment)
    <div class="post-author2 mt-5">
        <div class="post-author-thumb2 text-center">           
            <div class="d-flex align-items-center mb-2 border-pad">
                <div class="col-6">
                   <span id="username" class="white-font">
                         {{ $comment->user->username }} 
                    </span>
                </div>
                <div class="col-6">
                     {{ comment->??? }}  @lang('Comments')
                </div>
            </div>

and I want on each $comment to count total comments by user ( within the foreach )

I appreciated your reply.

0 likes
13 replies
vincent15000's avatar

Please make your post more readable by writing the code between back ticks.

I'm not sure to understand what you need : is it the total number of comments belonging to the user among those inside the foreach loop ?

This can be done by adding the closure to with.

->with(['user' => function ($query) use ($topic) {
	$query->withCount(['comments' => function ($query) use ($topic) {
		$query->where('topic_id', $topic->id);
	}]);
}])

Then you can access the comments count via the user.

@foreach ($comments as $comment)
			{{ $comment->user->comments_count }}
@endforeach
1 like
johnpapi's avatar

@vincent15000 Thanks for your reply. I already fix my coding to be more readable. I didnt know how but now I got it.

with your code appear this error: Column not found: 1054 Unknown column 'topic_id' in 'where clause' (SQL: select users., (select count() from comments where users.id = comments.user_id) as comment_count from users where users.id in (1, 4) and topic_id = 11)

I used @mohamedtammam code and works. Thanks anyway!

1 like
MohamedTammam's avatar
  $comments = Comment::where('topic_id', $topic->id)
						->with('user', fn($q) => $q->withCount('comments'))
						->orderBy('id', 'ASC')
						->paginate(getPaginate(20));

@foreach($comments as $comment)
				{{ $comment->user->comments_count }}
@endfroeach
2 likes
johnpapi's avatar

Endless I use this and works:

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

as I need to count also likes and topics but how can I count the topic where('status', 1) ?

@mohamedtammam do you know about this?

1 like
MohamedTammam's avatar
MohamedTammam
2 years ago
Best Answer
Level 51

@johnpapi

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

@mohamedtammam Do you know how to count likecom( comment likes by comment_id )

So every record in this table count on each comment ( id ) I make this I dont know if its correct as I dont know how to equal the comment_id there is relationship with topic and user.

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

Blade is like this

 <a href="{{route('user.topic.like.comment',$comment->id)}}" class="btn btn-md btn--gradient py-0 m-1">
                     
                        @if($comment->id == $topic->likecom->comment_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>

I have to add the condition IF in blade as it is above or I have to add in controller and then called in blade?

Thanks in advance

Please or to participate in this conversation.