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

Kaustubh's avatar

Select Table and count rows from another table whose seen is 0

Here i want to fetch post table and i want to count records on postConversation table where post.id = postConversation.PostId where seen = 0. but in below query i get the record of all whose seen is 0 & 1, but in my case i want to fetch those row whose seen is 0

Post Table (post) id | title | text | hasImage | img_path | mentorid | menteeid 1 Title1 ABC 0 1113.png 12 11

Post Conversation Table (postConversation) id | PostId | conv | sender | receiver | seen | sen_del | rec_del 1 1 ABC 11 12 1 0 0 2 1 XYZ 12 11 1 0 0 3 1 ZCX 11 12 0 0 0 4 1 PAQ 11 12 0 0 0

$discussPosts = DB::table('post')->leftJoin('postConversation', 'post.id', '=', 'postConversation.Did') ->select('post.*', DB::raw('count(postConversation.Did) as postConversationCount')) ->groupBy('post.id') ->where('mentorid',Auth::user()->id) ->where('menteeid',$userid) ->get(); dd($discussion) //Result

Collection {#478 ▼ #items: array:4 [▼ 0 => {#476 ▼ +"id": 1 +"title": "Title1" +"text": "ABC" +"hasImage": "1" +"img_path": "1113.png" +"mentorid": "12" +"menteeid": "11" +"status": "1" +"created_at": "2017-11-16 11:16:13" +"updated_at": "2017-11-16 11:16:13" +"postConversationCount": 4

0 likes
1 reply
tisuchi's avatar

@Kaustubh

There are many ways to achieve this. However, I just follow your convention.

Here is the code that you can follow-

$discussPosts = DB::table('post')
                ->join('postConversation', 'post.id', '=', 'postConversation.PostId') //you used Did, I am not sure which one is a foreign key, but it seems that PostId is a foreign key after checking your table structure. Adjust your foreign key if it is not working.
                ->select('post.*', DB::raw('count(postConversation.*) as postConversationCount'))
                ->where('post.mentorid', Auth::user()->id)
                ->where('post.menteeid', $userid)
                ->where('postConversation.seen', '!=', 1)
                ->get(); 

dd($discussion) //Result
1 like

Please or to participate in this conversation.