CODEWITHSUNDEEP
phpmysqllaravel
radiis
radiis

Reputation: 179

Get Count in mysql query in laravel

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();

Upvotes: 1

Views: 749

Answers (3)

shalini
shalini

Reputation: 1290

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();

Upvotes: 0

Vishal
Vishal

Reputation: 553

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();

Upvotes: 1

Imtiaz Pabel
Imtiaz Pabel

Reputation: 5443

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();

Upvotes: 0

Related Questions