Active records - "smart" select

[eluser]vitoco[/eluser]
I don't use active records, so i can only help you with plain sql :

Code:

$user_id = 1 ;
$sql = "
SELECT
  c.id AS community__id ,
  c.name AS community__name ,
  COUNT( DISTINT( c.user_id ) AS num_users_per_community
FROM
  communities AS c
LEFT JOIN
  user_communities AS uc
ON
  (
   uc.community_id = c.id AND
   (
    user_id <> ".$user_id." OR
    user_id IS NULL
   )
  )
GROUP BY
  c.id
ORDER BY
  num_users_per_community DESC
LIMIT
  0 , 2
";

Saludos