php - Laravel，MYSQL 两个聚合表上的完全连接解决方​​法

Question

我有一个transactions使用以下模式调用的表：

TRANSACTIONS
+----+-----------------+-------------------+--------+
| id | to (account_id) | from (account_id) | amount |
+----+-----------------+-------------------+--------+
| 1  | 1               | 3                 | 500    |
+----+-----------------+-------------------+--------+
| 2  | 2               | 1                 | 250    |
+----+-----------------+-------------------+--------+
| 3  | 1               | 2                 | 100    |
+----+-----------------+-------------------+--------+
| 4  | 4               | 2                 | 50     |
+----+-----------------+-------------------+--------+

to并且是on tablefrom的外键。idaccounts

我想从示例帐户中获取subtractionof from of和and 。sumall received transactionsall paid transactionsselected accounts12all the accounts which whom those had transaction with

这是期望的结果：

DESIRED QUERY RESULT
+---+------------+---------------+-----------+---------+
| # | account_id | totalReceived | totalPaid | balance |
+---+------------+---------------+-----------+---------+
| 1 | 1          | 600           | 250       | 350     |
+---+------------+---------------+-----------+---------+
| 2 | 2          | 250           | 150       | 100     |
+---+------------+---------------+-----------+---------+
| 3 | 3          | 0             | 500       | -500    |
+---+------------+---------------+-----------+---------+
| 4 | 4          | 50            | 0         | 50      |
+---+------------+---------------+-----------+---------+

到目前为止我得到了什么：

当我将 Laravel 与查询生成器一起使用时，我按如下方式运行它：

$k = [1, 2];

$receivedTransactions = DB::table('transactions as t1')->selectRaw('t1.to as account_id, sum(t1.amount) as totalReceived')
    ->whereIn('t1.from', $k)->orWhereIn('t1.to', $k)->groupBy('t1.to');

$paidTransactions = DB::table('transactions as t2')->selectRaw('t2.from as account_id, sum(t2.amount) as totalPaid')
    ->whereIn('t2.from', $k)->orWhereIn('t2.to', $k)->groupBy('t2.from');

$result = $receivedTransactions->leftJoinSub($paidTransactions, 't2', function ($join) {
    $join->on('t1.to', 't2.account_id');
})->selectRaw('totalPaid, (COALESCE(sum(t1.amount),0) - COALESCE(totalPaid,0)) as balance');

最大的问题是它没有给我所需结果表中的第 3 行，因为我没有完全连接，而且我没有从正确的表中得到结果，在连接中没有相应的行所以null.

这就是我得到的：

CURRENT QUERY RESULT
+---+------------+---------------+-----------+---------+
| # | account_id | totalReceived | totalPaid | balance |
+---+------------+---------------+-----------+---------+
| 1 | 1          | 600           | 250       | 350     |
+---+------------+---------------+-----------+---------+
| 2 | 2          | 250           | 150       | 100     |
+---+------------+---------------+-----------+---------+
| 4 | 4          | 50            | 0         | 50      |
+---+------------+---------------+-----------+---------+

我没有得到与第 1 号交易3有关的 account_id1

任何帮助将非常感激。它是 Raw MYSQL 格式还是 laravel 查询生成器都没有关系。非常感谢。

标签： phpmysqllaravel

Answer 1

我直接在 mysql 上执行此操作，然后尝试转换为 Laravel 语法

SELECT
  DISTINCT base_accounts.account_id,
  COALESCE(t1.totalReceived, 0) as totalReceived,
  COALESCE(t2.totalPaid, 0) AS totalPaid,
  COALESCE(t1.totalReceived, 0) - COALESCE(t2.totalPaid, 0) as totalBalance
FROM (
     SELECT `to` as account_id FROM transactions UNION SELECT `from` FROM transactions
) as base_accounts
LEFT JOIN (
     SELECT `to` as account_id, sum(amount) as totalReceived FROM transactions GROUP BY `to`
) as t1 on base_accounts.account_id = t1.account_id
LEFT JOIN (
     SELECT `from` as account_id, sum(amount) as totalPaid FROM transactions GROUP BY `from`
) as t2 on base_accounts.account_id = t2.account_id
ORDER BY base_accounts.account_id

 \DB::table(\DB::raw('(SELECT `to` as account_id FROM transactions UNION SELECT `from` FROM transactions) as base_accounts'))
        ->select(DB::raw('
        DISTINCT base_accounts.account_id,
        COALESCE(t1.totalReceived, 0) as totalReceived,
        COALESCE(t2.totalPaid, 0) AS totalPaid,
        COALESCE(t1.totalReceived, 0) - COALESCE(t2.totalPaid, 0) as totalBalance
        '))
        ->leftJoin(
            \DB::raw('(SELECT `to` as account_id, sum(amount) as totalReceived FROM transactions GROUP BY `to`) as t1'),
            'base_accounts.account_id',
            '=',
            't1.account_id'
        )
        ->leftJoin(
            \DB::raw('(SELECT `from` as account_id, sum(amount) as totalPaid FROM transactions GROUP BY `from`) as t2'),
            'base_accounts.account_id',
            '=',
            't2.account_id'
        )->orderBy('base_accounts.account_id')->get();

我测试了查询并且工作正常。