-1

Please someone should help me convert this SQL Query to Laravel 5.4 Query Builder syntax. I have searched for solutions and found some but not exactly as I wanted.

I have 3 tables:

Feeds: - feed_id - user_id - feed_title

Users: - id - username

Commments: - comment_id - feed_id - text - user_id

On my view returned, I want to see the feed title, user's user name, and a count of all comments to each feed. Like what you see on facebook: displaying the amount of comments on each post displayed. Please I really need this to be done:

This is the SQL code I tried in MySQL Database, it kinda works there but returns an error when I try to implement the same in Laravel

select *, count(comments.comment_id) as comment_count 
from `feeds` 
inner join `users` on 
`feeds`.`user_id` = `users`.`id` 
inner join `comments` on 
`feeds`.`feed_id` = `comments`.`comment_feed_id` 
group by `comments`.`comment_feed_id`
Bhaumik Pandhi
  • 2,655
  • 2
  • 21
  • 38
Peter Perez
  • 36
  • 6
  • Can you post your models relations? – Web Artisan Jul 05 '17 at 13:34
  • I just want to use SQL to achieve this... but talking about relationships; user_id in feeds is a foreign key which is a primary key (id) in users. and feed_id is a primary key in feeds and a foreign key in comments table as (feed_id) – Peter Perez Jul 05 '17 at 14:28

2 Answers2

0

This should do it, assuming the name of your model is "Feed":

Feed::selectRaw('feeds.*, count(comments.comment_id) as comment_count')
->join('users', 'users.id', '=', 'feeds.user_id')
->join('comments', 'feeds.feed_id', '=', 'comments.comment_feed_id')
->groupBy('comments.comment_feed_id')->get();
Eric Antoine Scuccimarra
  • 375
  • 2
  • 7
0
DB::table('feeds')
    ->selectRaw('*, count(comments.comment_id) as comment_count')
    ->join('users', 'users.id', '=', 'feeds.user_id')
    ->join('comments', 'feeds.id', '=', 'comments.comment_feed_id')
    ->groupBy('comments.comment_feed_id')
    ->get();
Idob
  • 1,620
  • 4
  • 16
  • 27
  • it returns this error: SQLSTATE[42000]: Syntax error or access violation: 1055 'behiind.feeds.feed_id' isn't in GROUP BY (SQL: select *, count(comments.comment_id) as comment_count from `feeds` inner join `users` on `users`.`id` = `feeds`.`user_id` inner join `comments` on `feeds`.`feed_id` = `comments`.`comment_feed_id` group by `comments`.`comment_feed_id`) – Peter Perez Jul 06 '17 at 13:30
  • I got it, there is no feeds.feed_id.. See the updated query above – Idob Jul 06 '17 at 13:33
  • Please this is the working SQL, Please help convert to Laravel Query Builder. `select feeds.feed_category, feeds.title, users.username, feeds.feed_created_at, feeds.feed_views, count(comments.comment_id) as comment_count from `comments` right join `feeds` on `feeds`.`feed_id` = `comments`.`comment_feed_id` left join `users` on `users`.`id` = `feeds`.`user_id` group by `feeds`.`feed_id`` – Peter Perez Jul 17 '17 at 10:21
  • Let's talk in the chat, I'll help you. But why did you remove the acceptance? – Idob Jul 17 '17 at 10:46