Active records - "smart" select

[eluser]ibnclaudius[/eluser]

Code:

SELECT communities.*
    -- users who share at least one community
    FROM users
    JOIN users_communities ON (
        users_communities.user_id = users.id
        AND user_id <> 1
    )
    JOIN users_communities AS c_u_communities ON (
        c_u_communities.community_id = users_communities.community_id
        AND c_u_communities.user_id = 1
    )
    -- (end of) users who share at least one community
    -- these other users communities
    JOIN users_communities AS o_u_communities ON (
        o_u_communities.user_id = users.id
        AND o_u_communities.user_id <> 1
    )
    JOIN communities ON (
        communities.id = o_u_communities.community_id
    )
    LEFT JOIN users_communities AS c_u_not_communities ON (
        c_u_not_communities.community_id = o_u_communities.community_id
        AND c_u_not_communities.user_id = 1
    )
    WHERE c_u_not_communities.community_id IS NULL
    GROUP BY (communities.id)
    ORDER BY COUNT(communities.id) DESC
    LIMIT 2;