The Arcive of Official vBulletin Modifications Site.It is not a VB3 engine, just a parsed copy! |
|
#1
|
|||
|
|||
limit left join to 1 record
Maybe I'm going about this all wrong, but basically I'm trying to add to showthread_query to check if the user has any public albums so I can check for the albums using a conditional in the postbit.
I was trying to just LEFT JOIN, but if the user has more than one public album, I end up with too many results. I need to limit the result to just the 1st public album for that user or else I end up with posts duplicating on showthread This is far as I can get For: showthread_query $hook_query_fields Code:
,album.userid AS hasalbums Code:
LEFT JOIN " . TABLE_PREFIX . "album AS album ON(album.userid = post.userid AND album.state = 'public') Or is there a better way to just fine out if the user has any public albums. I don't really need the albumid..i just need to establish if they have any public albums at all. |
#2
|
||||
|
||||
Use an aggregate function like count() instead of album.userid and then group by your primary recordid.
|
#3
|
|||
|
|||
Ok, so I played around with the Group By and found something that does work as intended.
I thought I'd post it up here and see if anyone can think of a more efficient or cleaner way to do it. Basically, I need a conditional to eval to true for use in the postbit template if the user has any public albums. The conditional I'm using: Code:
if (!empty($post['hasalbum'])) { $template_hook['postbit_userinfo_right'] .= '<div><a href="album.php?' . $vbulletin->session->vars['sessionurl'] . 'u=' . $post[userid] . '" rel="nofollow">' . $vbphrase['members_picture_albums'] . '</a></div>'; } And the query I got to work: Hook: showthread_query Code:
$hook_query_fields .= ",album.userid as hasalbum"; $hook_query_joins .= "LEFT JOIN (SELECT state, userid, MAX(albumid) AS hasalbum FROM " . TABLE_PREFIX . "album GROUP BY userid) album ON(user.userid = album.userid AND album.state = 'public')"; As I said, I got this working as intended. But I'm not sure if this is the best/most efficient way to do things since I had to use MAX(albumid) to make sure it only returned on result no matter how many public albums they have. Anyone have any input? |
#4
|
||||
|
||||
this is very useful and should, of course, be standard in vB to help support social and album use. If I had some time I'd play around with the query to help.
|
|
|
X vBulletin 3.8.12 by vBS Debug Information | |
---|---|
|
|
More Information | |
Template Usage:
Phrase Groups Available:
|
Included Files:
Hooks Called:
|