Kaustubh
1 year ago

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

Posted 1 year ago by Kaustubh

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

Please sign in or create an account to participate in this conversation.