Get Count in mysql query in laravel

Question

I wants to select the count of votes got for each items.Following query works fine but there is a problem if there is no vote for an item then that item is not showing in result.I actually wants details of each items and votes.If there is no vote for an item it should be shown count zero. How can i achieve it?

DB::table('wonders')
        ->leftjoin('vote','votes.wonderId','=','wonders.id')
        ->select('wonders.id','wonders.title',DB::raw('count(votes.vote) as votes'))
        ->get();

Vishal · Accepted Answer · 2016-01-20 05:11:08Z

1

Try this

DB::table('wonders')
            ->leftjoin('vote','votes.wonderId','=','wonders.id')
            ->select('wonders.id','wonders.title',DB::raw('ifnull(count(votes.vote),0) as votes'))
            ->groupBy('wonders.id')
            ->get();

answered Jan 20, 2016 at 5:11

Vishal

5531 gold badge6 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Imtiaz Pabel · Accepted Answer · 2016-01-14 10:21:10Z

0

maybe you need to use iffnull,try this

DB::table('wonders')
        ->leftjoin('vote','votes.wonderId','=','wonders.id')
        ->select('wonders.id','wonders.title',DB::raw('ifnull(count(votes.vote),0) as votes'))
        ->get();

answered Jan 14, 2016 at 10:21

Imtiaz Pabel

5,4451 gold badge21 silver badges24 bronze badges

1 Comment

radiis Over a year ago

No effect the same result is getting .It only returns the items having atleat 1 vote

Pang · Accepted Answer · 2016-07-04 06:51:38Z

0

Try this

DB::table('wonders')
    ->leftjoin('vote','votes.wonderId','=','wonders.id')
    ->selectRaw('wonders.id','wonders.title',count(votes.vote) as votes)
    ->groupBy('wonders.id')
    ->get();

edited Jul 4, 2016 at 6:51

Pang

10.2k146 gold badges87 silver badges126 bronze badges

answered Jan 20, 2016 at 7:10

shalini

1,30011 silver badges13 bronze badges

Collectives™ on Stack Overflow

Get Count in mysql query in laravel

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related