How to make a selection by the latest date?

Question

PHP

Hello! Guys, I have 2 tables. In the first albums - id, user_id, title, file, create_date The second table is photos - id, album_id, title, file, create_date. I want to list all records (from the albums table) so that the result is create_date and file from the photos table. That is, I display albums that will have their own thumbnail, that is, the latest photo by date from Photos for this album. My request looks like this:

SELECT a.*, p.file, max(p.date) as pdate, COUNT(p.id) as photo_count
            FROM
                `albums` a
            left JOIN `photos` p ON p.album_id = a.id
            WHERE
                a.`user_id` = ?
            GROUP BY a.id
            ORDER BY
                a.`date` DESC
            LIMIT ?

Answer 1

You have an incomplete GROUP BY in your query, you don't need to do that.
I would do it with subqueries, like this:

SELECT a.*,
  album_photos.file,
  album_photos.date,
  (select COUNT(id) from photos where album_id = a.id) as photo_count
FROM
  `albums` as a
  left join photos as album_photos on	a.id = album_photos.album_id
                                  and album_photos.`date` = ( select max(`date`) from `photos` where album_id = a.id)
WHERE
  a.`user_id` = 1
ORDER BY
  a.`date` DESC
LIMIT 10

Subqueries are harder to get wrong.