Getting the recent row by JOIN & GROUP BY?

Question

This is table album and it's structure and data is,

This is another table photos and it's structure and data is,

I would like to list all the album name, total photos for that corresponding album, last uploaded image by JOIN and GROUP BY.

This is the Codeigniter query I'm using right now. Everything works except it fetches the 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) as 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 for above is,

SELECT `albums`.`name`, `photos`.`image`, `albums`.`created`, `albums`.`id`, COUNT(photos.id) as total FROM `photos` JOIN `albums` ON `albums`.`id` = `photos`.`album_id` GROUP BY `photos`.`album_id` ORDER BY `photos`.`id` DESC LIMIT 10

For both the query the 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 you look the image key, it shows the value of first one instead of last row.

I'm open to any of both query solution (Codeigniter/SQL).

Maybe this answer can help you http://stackoverflow.com/questions/15390303/how-to-group-by-desc-order — K1N6, Jan 21 '16 at 06:54
@K1N6 There column in integer type. Here, its alphanumeric. So, MAX doesn't work. — user3289108, Jan 21 '16 at 07:01

Shadow · Answer 1 · 2016-01-21T11:36:29.247

2

You are grouping by photos.album_id, but the created date field is not subject to an agrregate function, therefore mysql picks up a random row's value according to the documentation (well, it's not that much random, it picks up the 1st corresponding value it encounters while scanning the data)

Use max() on the created date field to retrieve the latest date:

    SELECT max(`albums`.`name`) as name, `photos`.`image`, max(`albums`.`created`) as latest_date, `albums`.`id`, COUNT(photos.id) as total
FROM `photos` JOIN `albums` ON `albums`.`id` = `photos`.`album_id`
GROUP BY `photos`.`album_id`
ORDER BY `photos`.`id` DESC LIMIT 10

You will have the same issues with photos.image. To get the image associated with the latest created date you will need a subquery.

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

edited Jan 21 '16 at 11:36

answered Jan 21 '16 at 07:01

Shadow

33,525
10
51
64

Thank you but my only concern is getting recent `image`. How can i achieve using sub query? – user3289108 Jan 21 '16 at 07:04
Empty result. BTW it is `created` not `created_date`. You need anything from me, I can give it out. – user3289108 Jan 21 '16 at 07:14
Provide me with the exact query you run. You may have mixed up the date columns in the join – Shadow Jan 21 '16 at 07:29
Can you help?? I couldn;t get. – user3289108 Jan 21 '16 at 07:58
@Strawberry perhaps you could provide some pointers as to what's wrong with it. The only place for improvement I can see is to use max(photo.id) instead of max(created) to do the join on the subquery. However, as far as I can see even the current version should return the latest photo's details by album_id, provided the albums have at least 1 corresponding image. – Shadow Jan 21 '16 at 09:35
@Strawberry Oh, - Thanks – Shadow Jan 21 '16 at 11:37

score 2 · Answer 2 · edited May 23 '17 at 12:23

2

$query = $this->db->select('albums.name, ph.image, albums.created, albums.id, 
    COUNT(ph.id) as total')
                ->from('photos as ph') // added prefix
                ->join('albums', 'albums.id = ph.album_id')
                ->where('ph.id = (SELECT MAX(ph1.id) FROM photos as ph1 WHERE ph1.album_id=ph.album_id)',NULL,FALSE) // subquery to get latest record
                ->order_by('ph.id', 'desc')
                ->group_by('ph.album_id')
                ->limit($limit, $offset)
                ->get();

The NULL,FALSE in the where() tells CodeIgniter not to escape the query, which may mess it up Sub query in CodeIgniter

Alternatively, SQL Query is

SELECT `albums`.`name`, `ph`.`image`, `albums`.`created`, `albums`.`id`, COUNT(ph.id) as total FROM `photos` as `ph` JOIN `albums` ON `albums`.`id` = `ph`.`album_id` WHERE ph.id IN (SELECT MAX(ph1.id) FROM photos as ph1 WHERE ph1.album_id=ph.album_id) GROUP BY `ph`.`album_id` ORDER BY `ph`.`id` DESC LIMIT 10

edited May 23 '17 at 12:23

Community

1
1

answered Jan 21 '16 at 07:07

Disha V.

1,834
1
13
21

`You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as `ph1 WHERE ph1`.`album_id=ph`.`album_id`) GROUP BY `ph`.`album_id` ORDER BY `' at line 4` – user3289108 Jan 21 '16 at 07:15
BTW I mainly need to get latest `image` value. – user3289108 Jan 21 '16 at 07:16
Thanks to your query. I just need to add some extra parameter in `WHERE` condition to work. – user3289108 Jan 21 '16 at 07:25
@user3289108 Welcome, I have run my query and it was working fine though. Glad if i could have help you! – Disha V. Jan 21 '16 at 07:29
Checkout my edits in your answer. May be Codeigniter V3 has a strict condition. – user3289108 Jan 21 '16 at 07:31
You are running a subquery for each of the image records. This is not too effective! – Shadow Jan 21 '16 at 07:35
But, the total is not working well. It shows `1` for all result :( – user3289108 Jan 21 '16 at 07:44
@Shadow Right! I couldn't get the current total value. It shows Subquery returns more than one row. – user3289108 Jan 21 '16 at 07:54

score -1 · Accepted Answer · answered Jan 21 '16 at 08:33

-1

After many try, I got a solution to my problem. Here's the query which is simple,

$query = $this->db->query("SELECT p.`image`, COUNT(p.`id`) as total, a.`id`, a.`created`, a.`name` FROM `albums` as a JOIN (SELECT * FROM `photos` as p1 ORDER BY id DESC) p ON a.`id` = p.`album_id` GROUP BY p.`album_id` ORDER BY p.`id` DESC LIMIT 10");

I used sub query as said by @Shadow, and i just did ORDER BY for sub query's column Id. Through this, everything works as expected.

answered Jan 21 '16 at 08:33

user3289108

770
5
10
29

And when mysql silently changes how it behaves when you donot use full group by or the code is migrated to a mysql server where full group by is a requirement, then xou will be surprised why your code does not work. – Shadow Jan 26 '16 at 23:58

Getting the recent row by JOIN & GROUP BY?

3 Answers3