Laravel Eloquent：一对多关联表按时间分组查询交易及总手续费

嘿，我来帮你搞定这个分组查询的问题！你提到的transactions和transaction_charges一对多关联，完全可以直接在查询语句里完成分组+总手续费计算，不用事后遍历模型集合折腾。下面分原生SQL和常见ORM（比如Laravel Eloquent）两种场景给你具体方案：

不管你用哪种数据库，核心思路都是关联两张表，用日期函数提取时间维度（月/年/周），再按这个维度分组，最后聚合计算总手续费和交易数量。

1. 月度统计 #

SELECT
    DATE_FORMAT(t.created_at, '%Y-%m') AS month, -- 格式化为「年-月」，比如2024-05
    COUNT(t.id) AS transaction_count, -- 该月交易总数
    SUM(tc.amount) AS total_charges -- 该月总手续费
FROM transactions t
LEFT JOIN transaction_charges tc ON t.id = tc.transaction_id
GROUP BY DATE_FORMAT(t.created_at, '%Y-%m')
ORDER BY month DESC; -- 按时间倒序排列

小贴士：用LEFT JOIN是为了保证没有手续费的交易也会被统计，如果用INNER JOIN会过滤掉这类交易，按需选择。

2. 年度统计 #

SELECT
    YEAR(t.created_at) AS year, -- 提取年份，比如2024
    COUNT(t.id) AS transaction_count,
    SUM(tc.amount) AS total_charges
FROM transactions t
LEFT JOIN transaction_charges tc ON t.id = tc.transaction_id
GROUP BY YEAR(t.created_at)
ORDER BY year DESC;

3. 周度统计 #

不同数据库的周函数略有差异，这里分两种常用情况：

MySQL/MariaDB #

SELECT
    DATE_FORMAT(t.created_at, '%Y-%u') AS week, -- %u表示周一为一周起始，%v表示周日为起始，按需切换
    COUNT(t.id) AS transaction_count,
    SUM(tc.amount) AS total_charges
FROM transactions t
LEFT JOIN transaction_charges tc ON t.id = tc.transaction_id
GROUP BY DATE_FORMAT(t.created_at, '%Y-%u')
ORDER BY week DESC;

PostgreSQL #

SELECT
    DATE_TRUNC('week', t.created_at) AS week_start, -- 返回该周的起始日期，比如2024-05-20
    COUNT(t.id) AS transaction_count,
    SUM(tc.amount) AS total_charges
FROM transactions t
LEFT JOIN transaction_charges tc ON t.id = tc.transaction_id
GROUP BY DATE_TRUNC('week', t.created_at)
ORDER BY week_start DESC;

如果你用的是Laravel（毕竟你提到了「模型集合」），可以用ORM优雅实现，先确保模型关联正确：

// app/Models/Transaction.php
public function charges()
{
    return $this->hasMany(TransactionCharge::class);
}

1. 月度统计 #

use Illuminate\Support\Facades\DB;

$monthlyStats = Transaction::query()
    ->select([
        DB::raw("DATE_FORMAT(created_at, '%Y-%m') as month"),
        DB::raw('COUNT(id) as transaction_count'),
        DB::raw('SUM(transaction_charges.amount) as total_charges')
    ])
    ->leftJoin('transaction_charges', 'transactions.id', '=', 'transaction_charges.transaction_id')
    ->groupBy('month')
    ->orderBy('month', 'desc')
    ->get();

2. 年度统计 #

$yearlyStats = Transaction::query()
    ->select([
        DB::raw("YEAR(created_at) as year"),
        DB::raw('COUNT(id) as transaction_count'),
        DB::raw('SUM(transaction_charges.amount) as total_charges')
    ])
    ->leftJoin('transaction_charges', 'transactions.id', '=', 'transaction_charges.transaction_id')
    ->groupBy('year')
    ->orderBy('year', 'desc')
    ->get();

3. 周度统计 #

MySQL/MariaDB版本 #

$weeklyStats = Transaction::query()
    ->select([
        DB::raw("DATE_FORMAT(created_at, '%Y-%u') as week"),
        DB::raw('COUNT(id) as transaction_count'),
        DB::raw('SUM(transaction_charges.amount) as total_charges')
    ])
    ->leftJoin('transaction_charges', 'transactions.id', '=', 'transaction_charges.transaction_id')
    ->groupBy('week')
    ->orderBy('week', 'desc')
    ->get();

PostgreSQL版本 #

$weeklyStats = Transaction::query()
    ->select([
        DB::raw("DATE_TRUNC('week', created_at) as week_start"),
        DB::raw('COUNT(id) as transaction_count'),
        DB::raw('SUM(transaction_charges.amount) as total_charges')
    ])
    ->leftJoin('transaction_charges', 'transactions.id', '=', 'transaction_charges.transaction_id')
    ->groupBy('week_start')
    ->orderBy('week_start', 'desc')
    ->get();

• 如果你开启了MySQL的ONLY_FULL_GROUP_BY严格模式，确保SELECT里的非聚合列都在GROUP BY中，上面的写法已经满足这个要求；
• 如果交易表用了软删除，记得在查询中加上->withTrashed()（如果需要包含已删除交易）；
• 要是你需要按其他时间维度（比如季度），只需要替换对应的日期函数即可，逻辑完全一致。

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