COALESCE in laravel

Question

Is it possible to use COALESCE in laravel query.

 $multipleitems = DB::table('votes')
                       ->select('masterItemId',DB::raw('sum(votes) as voteSum'))
                       ->whereBetween('voteDate',array($startDate,$endDate))
                       ->where($condition)
                       ->groupBy('masterItemId')
                       ->get();

It's my code and i wants to get each item and its total votes. If there is no vote i want to get '0'.

But in above code it returns items that have atleast 1 vote. Is there any method to get this done in laravel?

Answer 1

Well, the OP was not too helpful, but I will give it a shot! I assume, that the votes table contains actual votes cast by users on the items. This means, that if an item did not receive any vote, then that item id (masterItemId) does not exist in the votes table.

This means that the votes table has to be left joined on the main items table on the masterItemId field. I will call the main items table: items, and assume that it has an itemId field that matches the masterItemId field in the votes table. In SQL terms:

select items.itemId, ifnull(sum(votes.votes),0) as votesSum
from items left join votes on items.itemId=votes.masterItemId
where votes.voteDate between ... and ... and <other conditions>
group by items.itemId

I'm not familiar with Laravel, but you will need something like this, however do not treat is as copy-paste code:

$multipleitems = DB::table('items')
                 ->leftJoin('votes','items.itemId','=','votes.masterItemId')
                 ->select('items.itemId',DB::raw('ifnull(sum(votes.votes),0) as voteSum'))
                       ->whereBetween('votes.voteDate',array($startDate,$endDate))
                       ->where($condition)
                       ->groupBy('items.temId')
                       ->get();