php - 在 while 循环中从每篇文章的评论和图片表中选择

Question

我有一个文章表，我在一个 while 循环中将它显示在主页上。在 while 循环中，我想显示每篇文章的评论数和图片数。

它现在对我有用，但总共是三个查询，我试图将它组合到第一个查询中，然后在一个 while 循环中显示所有这些查询。这是我想要实现的目标:Articles page

我遵循的当前格式:

//a represents articles table, c represents comments table, i represents image table

$query = mysqli_query($conn, "SELECT a.a_id, a.title, a.datetime, a.user_id FROM a ORDER BY a.datetime DESC");

while($fetch = mysqli_fetch_assoc($query){
   $imageQ = msqli_query($conn, "SELECT COUNT(image_path), image_path FROM i WHERE a_id = '$fetch['a_id']'");
$imageFetch = mysqli_fetch_assoc($imageQ);
$commentQ = mysqli_query($conn, "SELECT COUNT(comment_id) FROM c WHERE a_id = '$fetch['a_id']'");
$commentFetch = mysqli_fetch_assoc($commentQ);
}

我想将所有这些查询塞进一个查询中，该查询获取每篇文章和第一张图片的文章和评论计数以及图像计数。

Answer 1

图像和评论是数据的独立维度。所以，你必须小心如何将它们组合在一起。在您的情况下，您可以在进行连接之前聚合这些值:

SELECT a.a_id, a.title, a.datetime, a.user_id,
       i.num_images, c.num_comments
FROM a LEFT JOIN
     (SELECT a_id, COUNT(image_path) as num_images
      FROM i
      GROUP BY a_id
     ) i
     ON i.a_id = a.a_id LEFT JOIN
     (SELECT a_id, COUNT(comment_id) as num_comments
      FROM c
      GROUP BY a_id
     ) c
     ON c.a_id = a.a_id
ORDER BY a.datetime DESC;