How to suggest communities that a user may want to follow - SQL

How to suggest communities that a user may want to follow in SQL

Hello everyone! This article is for those that have atleast the basics in SQL programming language. In this article of mine, I will first start by creating the database structure for our project, then will create a query to get the communities suggestions for Users. Note: It get the communities that the users are more involved. Like community A could have (pen, pencil) community B could have (pen, book), and community C could have (book, pencil). So You can see that, the system will propose you pen and pencil, since they are the most joined by the users.

Okay let's start!

Database Structure.

---

       CREATE TABLE users ( id INT, name VARCHAR(50) );
       CREATE TABLE users_communities ( user_id INT, community_id INT );
       CREATE TABLE communities (id INT, name VARCHAR(50) );
 
      INSERT INTO users VALUES
(1, 'User A'),
(2, 'User B'),
(3, 'User C'),
(4, 'User D');

INSERT INTO communities VALUES
(1, 'facebook'),
(2, 'google'),
(3, 'selfconsult'),
(4, 'twitter'),
(5, 'bootstrap'),
(6, 'blogger'),
(7, 'orange');

INSERT INTO users_communities VALUES
(1, 1), (1, 2), (1, 3),
(2, 1), (2, 4), (2, 5),
(3, 1), (3, 6), (3, 7),
(4, 1), (4, 6), (4, 4);

The Query.

---

         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;

You could edit the "1" with the current user on the system. This tuto of mine is made in sql programming language but if you want assistance or help in that, you can contact me.
And for the PHP Prgramming language of this, you could comment if you need a tutorial on php part of that.
Hope you enjoyed. :)