php - 如何建立一个查询，该查询汇总发送方和接收方之间的交易余额

Closed. This question needs to be more focused。它当前不接受答案。
                            
                        
                    
                
                            
                                
                
                        
                            
                        
                    
                        
                            想改善这个问题吗？更新问题，使其仅通过editing this post专注于一个问题。
                        
                        去年关闭。
                                                                                            
                
        
我正在设置一个脚本，该脚本仅通过使用事务表来计算用户的余额，而没有使用余额表。现在我想尝试做一个查询来做到这一点，这可能吗？如果可以，我该怎么做？

这适用于使用已经存在的代码库/数据库的内部应用程序。现在，我仅使用php即可完成此操作。

$outgoing = $this->entityManager->getRepository(Transaction::class)->findBy([
    'sender' => $user,
    'contract' => $contract
]);

$incoming = $this->entityManager->getRepository(Transaction::class)->findBy([
    'recipient' => $user,
    'contract' => $contract
]);

$history = $outgoing + $incoming;

foreach ($history as $index => $transaction) {
    if ($transaction->getTokens() > 0) {
        continue;
    }

    unset($history[$index]);
}

usort($history, function ($a, $b) {
    if ($a->getCreatedOn() === $b->getCreatedOn()) {
        return 0;
    }

    return $a->getCreatedOn() > $b->getCreatedOn() ? 1 : -1;
});

$balance = 0;

foreach ($history as $row) {
    if ($row->getSender() === $user) {
        $balance -= $row->getTokens();
    }

    if ($row->getRecipient() === $user) {
        $balance += $row->getTokens();
    }
}

return $balance;

表的结构：

交易


ID
发件人ID
receive_id
contract_id
代币
价钱
创建于


我想有一个整数作为余额号。

最佳答案

我不知道如何将其转换为对您的entitymanager接口的调用，但是下面的一些SQL应该可以完成工作并且很容易阅读：

SELECT credits.total - debits.total AS balance
FROM
 (SELECT SUM(tokens) AS total
  FROM `transaction`
  WHERE sender_id = :user
  AND contract_id = :contract) AS debits
CROSS JOIN
 (SELECT SUM(tokens) AS total
  FROM `transaction`
  WHERE recipient_id = :user
  AND contract_id = :contract) AS credits

您应该具有以下两个索引以最佳地实现此目的：


(sender_id, contract_id, tokens)
(recipient_id, contract_id, tokens)


为了测试，我创建了这个表：

CREATE TABLE `transaction` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `sender_id` int(11) NOT NULL,
  `recipient_id` int(11) NOT NULL,
  `contract_id` int(11) NOT NULL,
  `tokens` int(11) NOT NULL,
  `price` decimal(9,2) NOT NULL,
  `created_on` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `sender_id` (`sender_id`,`contract_id`,`tokens`),
  KEY `recipient_id` (`recipient_id`,`contract_id`,`tokens`)
);

这是一些示例数据。两笔借项和三笔贷项，每笔交易中有3个令牌：

INSERT INTO `transaction` VALUES
(1,10,20,1,3,19.95,'2019-01-20 15:41:47'),
(2,10,20,1,3,19.95,'2019-01-20 15:41:47'),
(3,30,10,1,3,19.95,'2019-01-20 15:41:47'),
(4,30,10,1,3,19.95,'2019-01-20 15:41:47'),
(5,20,10,1,3,19.95,'2019-01-20 15:41:47');

这是查询EXPLAIN报告，显示每个子查询中覆盖索引的使用：

+----+-------------+-------------+--------+---------------+--------------+---------+-------------+------+-------------+
| id | select_type | table       | type   | possible_keys | key          | key_len | ref         | rows | Extra       |
+----+-------------+-------------+--------+---------------+--------------+---------+-------------+------+-------------+
|  1 | PRIMARY     | <derived2>  | system | NULL          | NULL         | NULL    | NULL        |    1 | NULL        |
|  1 | PRIMARY     | <derived3>  | system | NULL          | NULL         | NULL    | NULL        |    1 | NULL        |
|  3 | DERIVED     | transaction | ref    | recipient_id  | recipient_id | 8       | const,const |    3 | Using index |
|  2 | DERIVED     | transaction | ref    | sender_id     | sender_id    | 8       | const,const |    2 | Using index |
+----+-------------+-------------+--------+---------------+--------------+---------+-------------+------+-------------+

用户id = 10和contract_id = 1的查询的输出：

+---------+
| balance |
+---------+
|       3 |
+---------+

关于php - 如何建立一个查询，该查询汇总发送方和接收方之间的交易余额，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/54275989/