Join in laravel 5.5

Question

I am trying to make a query where I get the number of comments on a News in a row in laravel.

Display count of comments in 'Kommentare'

This is the query in mysql.

SELECT news.Id,news.title,news.body,news.created_at, COUNT(news_comments.id) AS Kommentare
FROM news
LEFT JOIN news_comments ON news_comments.fk_news_id = news.id
GROUP BY news.id

Result of this query

How would I write that in laravel 5.5?

Thank you very much!

Please put your code in the question, not in images – Avery Feb 02 '18 at 16:42 — Avery, Feb 02 '18 at 16:42

Sohel0415 · Answer 1 · 2018-02-02T16:36:55.787

0

Make a model for news table name News and another for comments table name Comment. And then in your News model, define a hasMany() relationship method like -

public function comments(){
       return $this->hasMany(Comment::class);
}

And then in your controller, retrieve like this-

$newses = News::with('comments')->get();

This will load all news with corresponding all comments. If you want to load single news with all comments then -

$news  = News::with('comments')->where('id',$news_id)->first();

To get the comment count, you can count() on relationship like -

$count = News::find($news_id)->comments()->count();

edited Feb 02 '18 at 16:36

answered Feb 02 '18 at 16:31

Sohel0415

9,523
21
30

I just tried that, too. Working perfectly fine, helped me a lot!!! Thank you for your time. – pyth Feb 02 '18 at 18:12
@pyth welcome :) – Sohel0415 Feb 02 '18 at 18:36
Just a question for a better understanding. I have in my news_comments table an author_id that is related to the users table. Is it literally the same approach, just ->hasMany to ->hasOne? Then whenever I access the news_comment model i have automatically access to the corresponding user? – pyth Feb 02 '18 at 18:50
Ok, I did it but with belongsTo, i need to check when to use what. Thanks. – pyth Feb 02 '18 at 19:14
yes, almost same but used belongsTo rather than has one – Sohel0415 Feb 02 '18 at 19:15

score 0 · Accepted Answer · answered Feb 02 '18 at 16:33

0

You have to set the relationship between News and NewsComments. In News Model, supposing that news_comments is in NewsComment Model, in App folder. Set the relationship:

public function newsComments()
{
    return $this->hasMany('App\NewsComment', 'fk_news_id');
}

And then, to get the count, you can use count() method:

$count = $news->newsComments()->count();

Or, directly using blade:

{{ $news->newsComments()->count() }}

answered Feb 02 '18 at 16:33

Laerte

7,013
3
32
50

1

Worked perfect. Thank you for your time. – pyth Feb 02 '18 at 17:55

Join in laravel 5.5

2 Answers2