How to suggest communities that a user may want to follow in SQLHello 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!
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);
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;