如何构建通过交易历史记录提取用户拥有的所有当前合约的查询

Question

我正在设置一个脚本，该脚本需要通过交易历史记录提取用户拥有的所有当前合约。

以下是事务表和一些测试数据：

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,
  `created_on` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `sender_id` (`sender_id`,`contract_id`,`tokens`),
  KEY `recipient_id` (`recipient_id`,`contract_id`,`tokens`)
);
INSERT INTO `transaction` VALUES 
(1,10,20,1,'2019-01-20 15:41:47'),
(2,10,20,2,'2019-01-20 15:41:47'),
(3,30,10,1,'2019-01-20 15:41:47'),
(4,30,10,3,'2019-01-20 15:41:47'),
(5,20,10,2,'2019-01-20 15:41:47');

作为输出，我想要一个包含给定用户所有合约 ID 的数组。

用户 ID = 10 的查询输出：

+-------------+
| contract_id |
+-------------+
|           1 |
+-------------+
|           2 |
+-------------+
|           3 |
+-------------+

这是我必须实现此目的的当前代码。但是，如果它是一个查询，那就太好了。

$query = 'SELECT DISTINCT contract_id
    FROM transaction
    WHERE tokens IS NULL
    ORDER BY created_on DESC';
$statement = $this->entityManager
    ->getConnection()
    ->prepare($query);
$statement->execute();
$contracts = [];
foreach ($statement->fetchAll() as $row) {
    $query = 'SELECT *
        FROM transaction
        WHERE contract_id = :contract
        AND tokens IS NULL
        ORDER BY created_on DESC';
    $statement = $this->entityManager
        ->getConnection()
        ->prepare($query);
    $statement->execute([
        'contract' => $row['contract_id']
    ]);
    $result = $statement->fetch();
    if ((int) $result['recipient_id'] !== $user->getId()) {
        continue;
    }
    $contracts[] = $result['contract_id'];
}
return $contracts;

Answer 1

取尽可能小的答案，使用 DISTINCT 来防止输出中出现重复。 ORDER BY是可选的。

SELECT DISTINCT contract_id
FROM transactions
WHERE recipient_id = :id
ORDER BY contract_id

您的recipient_id索引将有助于此查询。

Answer 2

首先，

您没有根据数据库字段提供有关合同所有者是谁的信息。分不清是sender_id还是recipient_id。你的问题也有点模糊，因为我无法确定您是否想要基于数据的大量关联数组。

SELECT contract_id
FROM transactions
WHERE sender_id = ?
GROUP BY contract_id, created_on DESC

请记住转义您的输入值。