Combine count result to join in laravel

Question

i want get something like list of news with number of comment

article ------------- comment
news1 ---------------- 10
news2 ---------------- 2
news3 ---------------- 13
news4 ---------------- 25

i know how to do it with raw sql but can't make it with laravel. laravel can handle raw query but i don't want to use it for the whole query.
here is my current snipped code:

$newslist = News::where('status_id', 2)->orderBy('id', 'desc')->skip(0)->take($itemPerLoad)
        ->join('msuser', 'msuser.id', '=', 'news.user_id')
        ->select(....)->get();

i have tried put count on select method but got error (with join comment table as not written above)

->select('news.id', 'count(mscomment.id) as commented', ...)

because i can't join it, currently i use separate code to count it and set it to json response

$commented = Comment::where('news_id', $news->id)->count();

i think its not good way to get the number because it will request count for each loop

Have you tried using **selectRaw** like this: `->selectRaw('news.id', 'count(mscomment.id) as commented', ...)`. — thefallen, May 27 '16 at 11:57
it give me this error: Argument 2 passed to Illuminate\Database\Query\Builder::selectRaw() must be of the type array, string given — gondai yosuke, May 27 '16 at 12:32
Sorry, this is my mistake. Pass all the columns in a single string like this: `->selectRaw('news.id, count(mscomment.id) as commented')`. — thefallen, May 27 '16 at 12:36
count() method working but in my case for now i prefer huuk answer. — gondai yosuke, May 27 '16 at 13:40

huuuk · Accepted Answer · 2016-05-27T12:45:11.447

0

Use Eloquent relations

News.php

public function comments()
{
    return $this->hasMany('NAMESPACE_TO_YOUR_MODEL\Comment');
}

Then in Controller

$news = News::with('comments')->get();
$summary = [];
foreach($news as $n)
{
    $summary[] = ['article' =>$n->article, 'comments' => $n->comments->count()];
}

or you can iterate in view:

@foreach($news as $n)
    <tr>
        <td> {{ $n->article }} </td>
        <td> {{ $n->>comments->count() }} </td>
    </tr>
@endforeach

I think this way is canonical

edited May 27 '16 at 12:45

answered May 27 '16 at 12:38

huuuk

4,597
2
20
27

this working good as needed. also make me notice about eloquent-relationships feature. thank you – gondai yosuke May 27 '16 at 13:34

Combine count result to join in laravel

1 Answers1