User Relationships - display "popular" users
steveadamo - October 19, 2009 - 14:12
I have quicktabs on my main Users page, and one of the tabs I would like to display is "Popular Users". The idea being that it would pull a list of the top 25 users with the most friends (friend being the default, and only, relationship type on the site).
A quick search revealed this thread: http://drupal.org/node/216473
And while the queries are somewhat applicable, I need something a little more generic (instead of the number of relationships for a specific user(s), I need all relationships for all users, sorted by number of accepted relationship types).
Ideally, the result would look someting like:
Name | Friends
--------------
Steve | 21
Jane | 19
Brian | 19
Jeff | 16
Kelly | 12
etc...I would prefer to do this in a View, but if calling some specific SQL is required, thats OK too. I'm open to any ideas! :)
A solution that works for me...
I found this thread: http://drupal.org/node/540372
and used it to adapt the query for my scenario:
$users = db_query("SELECT COUNT(r.rid) count, u.name, u.created, u.picture, u.uid FROM {users} u LEFT JOIN {user_relationships} r ON u.uid = r.requester_id WHERE u.uid != 0 AND u.uid != 1 AND r.requester_id = u.uid AND u.status = 1 AND r.approved = 1 GROUP BY r.requester_id ORDER BY count DESC LIMIT 25");I can then take the resultset and format accordingly.
I was initially concerned about limiting the query using requester_id (as opposed to requestee_id), but my initial tests have proven successful... I've had a user with X friends both request a new friend, and have someone else request to be their friend (so they would be both a requester and requestee). The result of the query above added the correct number of friends (2) to their total friend count.
Hope this helps someone else... :)