如何按州统计每位候选人的no_of_votes总和（Laravel查询）

修改后的查询代码 #

$election = $this->election
    ->where('position_title_id', 1)
    ->whereYear('election_date', now())
    ->first();

$vote_result = DB::table('elections')
    ->join('election_ward', 'elections.id', '=', 'election_ward.election_id')
    ->join('election_ward_candidate', 'election_ward.id', '=', 'election_ward_candidate.election_ward_id')
    ->join('voting_centres', 'election_ward.voting_centre_id', '=', 'voting_centres.id')
    ->join('wards', 'wards.id', '=', 'voting_centres.ward_id')
    ->join('cities', 'cities.id', '=', 'wards.city_id')
    ->join('states', 'states.id', '=', 'cities.state_id')
    ->join('candidates', 'candidates.id', '=', 'election_ward_candidate.candidate_id')
    ->select(
        'states.id as state_id',
        'states.name as state_name',
        'states.abbr as state_abbr',
        'candidates.id as candidate_id',
        'candidates.full_name as candidate_name',
        'candidates.political_party_id',
        DB::raw('sum(election_ward_candidate.no_of_votes) as total_votes')
    )
    ->where('elections.id', $election->id)
    ->groupBy('states.id', 'candidates.id')
    ->orderBy('states.name', 'asc')
    ->orderBy('total_votes', 'desc')
    ->get();

关键修改说明 #

• 字段选择优化：避免使用*选择所有字段，明确指定需要的州和候选人信息，同时通过sum()聚合函数计算总票数，避免分组时出现字段不兼容问题（MySQL严格模式下要求非聚合字段必须出现在GROUP BY中）。
• 分组逻辑调整：通过groupBy('states.id', 'candidates.id')实现按州和候选人分组，确保同一州内的同一候选人的所有投票记录被汇总。
• 排序优化：新增按总票数降序排序，方便直观查看各州内候选人的得票排名。

预期返回结果 #

基于你提供的样本数据，修改后的查询将返回如下结构的结果：

[
    {
        "state_id": "01gssmzgpewx36gx31g1mqx5e0",
        "state_name": "Abia",
        "state_abbr": "Abia",
        "candidate_id": "01gssmzgpewx36gx31g1mqx5e0",
        "candidate_name": "Senate 1",
        "political_party_id": 2,
        "total_votes": 22
    },
    {
        "state_id": "01gssmzgpewx36gx31g1mqx5e0",
        "state_name": "Abia",
        "state_abbr": "Abia",
        "candidate_id": "01gssmzyzk8kkc8f6esbqknt15",
        "candidate_name": "Senate 2",
        "political_party_id": 1,
        "total_votes": 22
    },
    {
        "state_id": "01gssmzyzk8kkc8f6esbqknt15",
        "state_name": "Adamawa",
        "state_abbr": "Adamawa",
        "candidate_id": "01gssmzgpewx36gx31g1mqx5e0",
        "candidate_name": "Senate 1",
        "political_party_id": 2,
        "total_votes": 30
    },
    {
        "state_id": "01gssmzyzk8kkc8f6esbqknt15",
        "state_name": "Adamawa",
        "state_abbr": "Adamawa",
        "candidate_id": "01gssmzyzk8kkc8f6esbqknt15",
        "candidate_name": "Senate 2",
        "political_party_id": 1,
        "total_votes": 26
    }
]

内容的提问来源于stack exchange，提问作者Abhay