Suggest a better MySQL query

Question

I am a relative noob when it comes to mysql queries so please don't bite my head off. I am trying to make a "Top Poller" module using a poll extension and Jomsocial. I want to display the top 5 users by the amount of polls they have created. Here is the data table structure (well the important parts)

#__users
-id
-name
-username

#_jcp_polls
-created_by (this is the same as #users.id)

#__community_users
-thumb
-avatar

Here is my query

$db = JFactory::getDBO();

$query = "SELECT u.id, u.username, u.name, c.thumb, c.avatar,COUNT(p.created_by) as total
      FROM #__users u, #__community_users c, #__jcp_polls p 
      WHERE u.id = p.created_by
      GROUP by u.id
  ORDER BY total DESC
      LIMIT $user_count
     ";

$db->setQuery($query);
$rows = $db->loadObjectList();

I can display the user table fields in a foreach loop like

foreach($rows as $row){
 echo $row->name
}

I thought I would be able to use $row->avatar but it doesn't work. Can someone suggest a query that will allow me to display fields from the #__community_users table as well as the #__users table? and still keep the rankings from the #__jcp_polls table?

Answer 1

Presently, you have no condition joining #__community_users to #__users. Here is an updated query with the implicit joins swapped out for explicit INNER JOINs, assuming #__community_users has a column user_id which relates to #__users.id. In your table structure above, you have no column relating #__community_users to #__users. Without one, you cannot relate avatars to users.

SELECT 
  u.id, u.username, u.name, c.thumb, c.avatar,COUNT(p.created_by) as total
FROM 
  #__users u 
  /* Supply the correct column name for c.user_id */
  JOIN #__community_users c ON u.id = c.user_id
  /* LEFT JOIN used to list 0 for users who have no polls */
  LEFT JOIN #__jcp_polls p ON u.id = p.created_by
GROUP by u.id
ORDER BY total DESC
LIMIT $user_count

If it is possible for a user not to have an avatar, use a LEFT JOIN against #__community_users:

SELECT 
  u.id, u.username, u.name, c.thumb, c.avatar,COUNT(p.created_by) as total
FROM 
  #__users u 
  /* Supply the correct column name for c.user_id */
  LEFT JOIN #__community_users c ON u.id = c.user_id
  /* LEFT JOIN used to list 0 for users who have no polls */
  LEFT JOIN #__jcp_polls p ON u.id = p.created_by
GROUP by u.id
ORDER BY total DESC
LIMIT $user_count

Your syntax, although valid for MySQL, is not universally valid since you have multiple columns in the SELECT list but only u.id in the GROUP BY. A more portable version of the query would look like:

SELECT
  u.id, u.username, u.name, c.thumb, c.avatar, p.total
FROM
  #__users u 
  LEFT JOIN #__community_users c ON u.id = c.user_id
  /* Left Join against a subquery that returns count per user */
  LEFT JOIN (SELECT created_by, COUNT(*) AS total FROM #__jcp_polls GROUP BY created_by) p ON u.id = p.created_by
ORDER BY total DESC
LIMIT $user_count