COALESCE in laravel

Question

Is it possible to use COALESCE in a laravel request.

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

This is my code, and I want to get each item and its total number of votes. If there is no vote, I want to get "0".

But in the above code, it returns elements that have at least 1 vote. Is there a way to do this in laravel?

Answer 1

Well, the OP was not very useful, but I will give him a chance! I believe that the table votescontains the actual votes cast by users by item. This means that if an element does not receive any vote, then this element identifier ( masterItemId) does not exist in the table votes.

, masterItemId. : items , itemId, masterItemId votes. SQL:

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

Laravel, - , -:

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