Laravel 中的合并
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/33675277/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
COALESCE in laravel
提问by radiis
Is it possible to use COALESCE in laravel query.
是否可以在 Laravel 查询中使用 COALESCE。
$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'.
这是我的代码，我想获得每个项目及其总票数。如果没有投票，我想得到“0”。
But in above code it returns items that have atleast 1 vote. Is there any method to get this done in laravel?
但在上面的代码中，它返回至少有 1 票的项目。有没有什么方法可以在 Laravel 中完成这项工作？
回答by Shadow
Well, the OP was not too helpful, but I will give it a shot! I assume, that the votestable 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 votestable.
好吧，OP 并没有太大帮助，但我会试一试！我假设该votes表包含用户对这些项目的实际投票。这意味着，如果一个项目没有收到任何投票，那么该项目 id ( masterItemId) 不存在于votes表中。
This means that the votes table has to be left joined on the main items table on the masterItemIdfield. I will call the main items table: items, and assume that it has an itemIdfield that matches the masterItemIdfield in the votestable. In SQL terms:
这意味着投票表必须留在masterItemId字段上的主要项目表上。我将调用 main items table: 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
I'm not familiar with Laravel, but you will need something like this, however do not treat is as copy-paste code:
我对 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();
