CODEWITHSUNDEEP
mysqllaraveleloquent
hayumi kuran
hayumi kuran

Reputation: 431

Laravel Eloquent mutiple counts with different conditions in one query

I have code like below:

$sendto_hikkou1 = Guest::where('mypage_id', $mypage)
                ->where('snd_to_hikkou_id', 1)->count();
$sendto_hikkou2 = Guest::where('mypage_id', $mypage)
                ->where('snd_to_hikkou_id', 2)->count();
$sendto_hikkou3 = Guest::where('mypage_id', $mypage)
                ->where('snd_to_hikkou_id', 3)->count();

How can I write this in one query?

Upvotes: 1

Views: 129

Answers (3)

hayumi kuran
hayumi kuran

Reputation: 431

Here the solution i found, but @C2486's answer is much better.

        $sendto_hikkou = Guest::where('mypage_id', $mypage)
        ->select(
          \DB::raw('COUNT(IF( snd_to_hikkou_id = 1, snd_to_hikkou_id, NULL)) AS item1'),
          \DB::raw('COUNT(IF( snd_to_hikkou_id = 2, snd_to_hikkou_id, NULL)) AS item2'),
          \DB::raw('COUNT(IF( snd_to_hikkou_id = 3, snd_to_hikkou_id, NULL)) AS item3'),
        )
       ->get();

Upvotes: 0

Niklesh Raut
Niklesh Raut

Reputation: 34914

Use groupBy to get all counts in one query, like below

$sendto_hikkou = Guest::where('mypage_id', $mypage)
    ->whereIn('snd_to_hikkou_id',[1,2,3])
    ->groupBy('snd_to_hikkou_id')
    ->select('snd_to_hikkou_id',DB::raw("count(*) as hikkou_count"))
    ->get();

Upvotes: 2

r0ulito
r0ulito

Reputation: 487

You'll have to use DB facade instead of the model. Select the id field then count , group by id then get. I cant test at this time !

Upvotes: 0

Related Questions