如何连接表，同时进行聚合，并将结果转换为SQlite/PDO中的JSON？

Question

我试图将结果聚合从Android外包给php。我很难正确构建一个带有联接表的查询，并在以后用JSON对其进行编码。我正试图在一个英雄最酷的投票中获得一个结果。所以我为英雄们准备了一张桌子，为用户准备了一张桌子，还有一张我投票的桌子。好吧这就是我得到的：

表英雄: id，姓名

表用户: id，名称

表投票:虚票，userId，heroId

我的投票结果是将每个平等的heroIds聚合起来，然后从英雄表中获得合适的英雄名，然后发送一个JSON对象，如下所示：

{"voteid":"1"，"heroCount":"7"，“heroName”：“蝙蝠侠”}，{“voteid”：“1”，"heroCount":"3"，“heroName”：“海王”}}

我有点无知，因为我的知识很有限。这就是我到目前为止在我的.php中得到的：

$db = new PDO('sqlite:voting.sqlite');
$stmt = $db->prepare('SELECT * FROM vote');   //<---- how to do that correctly?
$stmt->execute();

$result['vote']=$stmt->fetchAll(PDO::FETCH_ASSOC);
$json=json_encode($result);

echo $json;

有人能给我一些好的意见吗？我在想这件事已经有相当长的时间了，但没有解决。

Answer 1

您希望使用SQL GROUP BY子句和左联接。如下所示：

SELECT hero.name AS heroName, COUNT(vote.userId) AS heroCount 
    FROM vote LEFT JOIN hero ON vote.heroId = hero.id GROUP BY hero.id

如果在PHP代码中替代SQL，它将产生与您的规范类似的JSON输出。不过，voteid会失踪。如果voteid是vote表的主键，那么在这样的聚合结果中没有任何意义。蝙蝠侠的结果将有7个voteid。