The Arcive of Official vBulletin Modifications Site.It is not a VB3 engine, just a parsed copy! |
|
#1
|
|||
|
|||
mysql: sort before group
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: Code:
$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 "); Thank you! |
#2
|
||||
|
||||
You can do this with a nested query
[sql] 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 [/sql] 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. |
#3
|
||||
|
||||
+1 for storing the ID you want first and joining on that
|
#4
|
|||
|
|||
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: Code:
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 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. |
#5
|
||||
|
||||
Quote:
|
|
|
X vBulletin 3.8.12 by vBS Debug Information | |
---|---|
|
|
More Information | |
Template Usage:
Phrase Groups Available:
|
Included Files:
Hooks Called:
|