Most efficient way to query the newest 20 posts per category on the sme page

Question

I am using PHP and mySQL. I have a table of photographs. in the photographs table I have: a link to the photo, a category_id, date.

What would be the best way to list all the categories on the page with the newest 20 photos under each?

Right now I am selecting all the photos and then sorting them out after in PHP. If there gets to be 500 photos in one category this would seem very inefficient. Any better ideas for the SQL end of it?

The only other way I thought of was to loop a 20 limit query for each category, but if there are 100 categories that seems even worse!

pseudo output

[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',
      }
     }
    }
}

pseudo code

$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

Just a suggestion, but how 'bout running one query for the list of categories and use the results to create a query for the items using a combination of LIMIT and UNION? That way you're only sending two queries; but, it's possible that that's not much more efficient than the second option you described depending on how much overhead each database call requires and how much optimization mySql will do when it sees the UNION (e.g. parallel processing the statement).

I don't know enough about it to recommend it, but it's something i would try.

Answer 2

It can be done in a single query.

Assuming this is the table schema:

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 ;

You can get an ordered list of the 20 most recent photos per category with this query:

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;

The PHP loop to create something similar to your desired array structure is:

$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']));
}