By steveadamo on

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! :)

⋅ Categories: Drupal 6.x

Comments

steveadamo’s picture

steveadamo commented

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... :)