mysql: sort before group

[jerx]

I wanted to modify a mysql query, which displays the latest album pictures. Since many users upload many pictures at once, this would only show pictures from one user/album. Therefore I added a group query to the statement, but this does not show the latest updates any more.

This does not work:
$lpictures = $db->query_read("
SELECT albumpicture.*, album.*, user.username, user.usergroupid,
IF(displaygroupid=0, user.usergroupid, displaygroupid) AS displaygroupid
FROM " . TABLE_PREFIX . "albumpicture AS albumpicture
LEFT JOIN " . TABLE_PREFIX . "album AS album ON(album.albumid = albumpicture.albumid)
LEFT JOIN " . TABLE_PREFIX . "user AS user ON(user.userid = album.userid)
WHERE album.state = 'public'
GROUP BY album.userid
ORDER BY $orderby DESC LIMIT 0, $tot
");


Does anyone know how you can first sort the data and then group it?

Thank you!

[Eikinskjaldi]

You can do this with a nested query


SELECT albumpicture.*, album.*, user.username, user.usergroupid,
IF(displaygroupid=0, user.usergroupid, displaygroupid) AS displaygroupid
FROM " . TABLE_PREFIX . "albumpicture AS albumpicture
LEFT JOIN " . TABLE_PREFIX . "album AS album ON(album.albumid = albumpicture.albumid)
LEFT JOIN " . TABLE_PREFIX . "user AS user ON(user.userid = album.userid)
WHERE album.state = 'public'
AND albumid in (select max(albumid) from album group by userid)
ORDER BY $orderby DESC LIMIT 0, $tot



This assume the highest albumid for a user is the latest album.

This is probably quite slow, if this is a query you are running often then I would create a new latestalbum table and set up a trigger so that it updates when the album table updates.

[Adrian Schneider]

+1 for storing the ID you want first and joining on that

[jerx]

Thank you!

I think this won' t work. The album with the highest id is the latest album, but it does not necessarily hold the latest uploaded pictures of that user.

I tried your code anyway. First I ran into several error messages (table not found or subquery is ambiguous). In the end I had this:

SELECT albumpicture.*, album.*, user.username, user.usergroupid,
IF(displaygroupid=0, user.usergroupid, displaygroupid) AS displaygroupid
FROM " . TABLE_PREFIX . "albumpicture AS albumpicture
LEFT JOIN " . TABLE_PREFIX . "album AS album ON(album.albumid = albumpicture.albumid)
LEFT JOIN " . TABLE_PREFIX . "user AS user ON(user.userid = album.userid)
WHERE album.state = 'public'
AND album.albumid in (select max(album.albumid) from vb_album group by album.userid)
ORDER BY $orderby DESC LIMIT 0, $tot

It does not work. It gives me pictures of the same album. The result looks like the original code, but I think if you update an old album, this will give you wrong results.

But you suggest going another route anyway. This will be shown on the index page of the domain and therefore will be running very often. Unfortunetely I have no clue how to accomplish that. I was hoping that this change would be as easy as switching from dateline sort order to random display.

I just looked at vb 3.8 beta 1. It almost has a this feature. They created a new table for the album updates, too. But it only shows the coverpicture, not the latest picture added to that album. Nonetheless I would prefer this kind of display over displaying multiple pictures of the same albums.

[Eikinskjaldi]

Thank you!

I think this won' t work. The album with the highest id is the latest album, but it does not necessarily hold the latest uploaded pictures of that user.

I tried your code anyway. First I ran into several error messages (table not found or subquery is ambiguous). In the end I had this:

SELECT albumpicture.*, album.*, user.username, user.usergroupid,
IF(displaygroupid=0, user.usergroupid, displaygroupid) AS displaygroupid
FROM " . TABLE_PREFIX . "albumpicture AS albumpicture
LEFT JOIN " . TABLE_PREFIX . "album AS album ON(album.albumid = albumpicture.albumid)
LEFT JOIN " . TABLE_PREFIX . "user AS user ON(user.userid = album.userid)
WHERE album.state = 'public'
AND album.albumid in (select max(album.albumid) from vb_album group by album.userid)
ORDER BY $orderby DESC LIMIT 0, $tot

It does not work. It gives me pictures of the same album. The result looks like the original code, but I think if you update an old album, this will give you wrong results.

But you suggest going another route anyway. This will be shown on the index page of the domain and therefore will be running very often. Unfortunetely I have no clue how to accomplish that. I was hoping that this change would be as easy as switching from dateline sort order to random display.

I just looked at vb 3.8 beta 1. It almost has a this feature. They created a new table for the album updates, too. But it only shows the coverpicture, not the latest picture added to that album. Nonetheless I would prefer this kind of display over displaying multiple pictures of the same albums.

What field does hold the info per user you need? i.e. what query would you run to get the latest picture(s) or what ever it is you want for just one user (e.g. you)