limit left join to 1 record

[h2ojunkie]

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
,album.userid AS hasalbums

And for: showthread_query $hook_query_joins
LEFT JOIN " . TABLE_PREFIX . "album AS album ON(album.userid = post.userid AND album.state = 'public')

How would I limit the join to one result?

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.

[Adrian Schneider]

Use an aggregate function like count() instead of album.userid and then group by your primary recordid.

[h2ojunkie]

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:

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

$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?

[firstimecaller]

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.