查询中小企业网页上每个类别的最新20个帖子的最有效方法

Question

我使用的是PHP和mySQL。我有张照片表。在照片表中我有:一个链接到照片，一个category_id，日期。

，有最新的20张照片在页面上列出所有类别的最好方法是什么？

现在我正在选择所有的照片，然后在PHP中对它们进行排序。如果有500张照片在一个类别，这似乎是非常低效的。对于它的SQL结束有什么更好的想法吗？

我唯一想到的其他方法是为每个类别循环一个20个限制查询，但是如果有100个类别，那就更糟了！

伪输出

[category_list] => {
    [0]=> {
        'category_title' => 'photos at sunset',
        'posts' => {
            [0] => {
                'photo_link' = '1.jpg',
            }
            [1] => {
                'photo_link' = '2.jpg',
            }
        }
    }
    [1]=> {
        'category_title' => 'photos at sunrise',
        'posts' => {
            [0] => {
                'photo_link' = '1.jpg',
            }
        }
    }
}

伪码

$query =  
"
SELECT 
    photographs.category_id, photographs.photo_link, categories.title
FROM 
     photographs
INNER JOIN 
     categories
ON 
     category.id = photographs.categories.id
ORDER BY
     category.id DESC
";

$result = $this->pdo->prepare($query);
$result->execute();

while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
    $post[] = $row;
}
$result = null;

$count = sizeof($post);
//get a list of the categories
for($i=0; $i < $count; $i++) { 
    $categories[$i] = $post[$i]['title'];
}
$categories = array_unique($categories);

//sort categories alphabetically
sort($categories);

//add the newest 20 photos to each category
$categories_count = count($categories);
$posts_count = count($post);
for($i=0; $i < $categories_count; $i++) { 
    $category_list[$i]['post_count'] = 0;
    for($k=0; $k < $posts_count; $k++) { 
        if ($categories[$i] == $post[$k]['category_title']) {
            if ($category_list[$i]['count'] == 19) {
                break;
            }
            $category_list[$i]['category_title'] = $post[$k]['category_title'];
            $category_list[$i]['post'][] = $post[$k];
            $category_list[$i]['post_count']++;
        }
    }
}

Answer 1

它可以在一个查询中完成。

假设这是表模式：

CREATE TABLE `parkwhiz_demo`.`test` (
`photo_id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`category_id` INT UNSIGNED NOT NULL ,
`date` DATETIME NOT NULL
) ENGINE = MYISAM ;

您可以通过以下查询获得每个类别的20张最新照片的有序列表：

select photo_id, category_id, date
from test
where (
   select count(*) from test as t
   where t.category_id = test.category_id and t.date >= test.date
) <= 20
order by category_id, date desc;

创建类似于所需数组结构的PHP循环如下：

$output = Array();
$prevRow = false;
$i=-1;
while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
    if (!$prevRow || $row['category_id'] != $prevRow['category_id']) {
        $i++;
        $output[$i]['category_id'] = $row['category_id'];
        $output[$i]['posts'] = Array();
    }

    array_push($output[$i]['posts'], Array('image_id'=>$row['image_id']));
}

Answer 2

只是一个建议，但是对类别列表运行一个查询，然后使用这些结果创建一个查询，使用限制和UNION的组合，怎么样?这样，您只需要发送两个查询；但是，这可能并不比您描述的第二个选项更有效，这取决于每个数据库调用所需的开销以及mySql在看到UNION时将执行多少优化(例如，并行处理语句)。

我对它不太了解，不能推荐它，但我会尝试的。