php - Getting the recent row by JOIN & GROUP BY? -

this table album , it's structure , data is,

this table photos , it's structure , data is,

i list album name, total photos corresponding album, last uploaded image join , group by.

this codeigniter query i'm using right now. works except fetches first row of image column value instead of latest one.

 $query = $this->db->select('albums.name, photos.image, albums.created, albums.id,      count(photos.id) total')    ->from('photos')    ->join('albums', 'albums.id = photos.album_id')      ->order_by('photos.id', 'desc')    ->group_by('photos.album_id')                           ->limit($limit, $offset)    ->get(); 

the generated sql query above is,

select `albums`.`name`, `photos`.`image`, `albums`.`created`, `albums`.`id`, count(photos.id) total `photos` join `albums` on `albums`.`id` = `photos`.`album_id` group `photos`.`album_id` order `photos`.`id` desc limit 10 

for both query result is,

[{"name":"movie stills","image":"assets/images/albums/2016/01/84786b91857b45fa9391943e9a468ac6.jpg","created":"2016-01-19 23:41:53","id":"3","total":"3"},{"name":"firstlook","image":"assets/images/albums/2016/01/6e90cdfdec444dfc53f4a7c30a2ac31e.jpg","created":"2016-01-19 23:16:05","id":"1","total":"2"}]

if image key, shows value of first 1 instead of last row.

i'm open of both query solution (codeigniter/sql).

you grouping photos.album_id, created date field not subject agrregate function, therefore mysql picks random row's value according documentation (well, it's not random, picks 1st corresponding value encounters while scanning data)

use max() on created date field retrieve latest date:

    select max(`albums`.`name`) name, `photos`.`image`, max(`albums`.`created`) latest_date, `albums`.`id`, count(photos.id) total `photos` join `albums` on `albums`.`id` = `photos`.`album_id` group `photos`.`album_id` order `photos`.`id` desc limit 10 

you have same issues photos.image. image associated latest created date need subquery.

select a.name, p.image, t.mdate albums inner join (select album_id, max(created_date) mdate photos group album_id) t on a.id=t.album_id inner join photos p on a.id=p.album_id , t.mdate=p.created_date