· 6 years ago · Jul 23, 2019, 08:00 AM
1DELIMITER $$
2
3USE `someDB`$$
4
5DROP PROCEDURE IF EXISTS `AAB`$$
6
7CREATE PROCEDURE `AAB`(IN `feed_setting_user_id` BIGINT)
8BEGIN
9DECLARE v_friendID, FoFID BIGINT; # use datatype which is used for u.ID used int based on ur result
10DECLARE exit_loop, exit_loop1 BOOLEAN DEFAULT FALSE;
11
12DECLARE friend_cursor CURSOR FOR
13
14 SELECT `u`.`ID` FROM `users` `u`
15 WHERE `u`.`ID` IN (SELECT `u1`.`ID` FROM users `u1`
16 WHERE `u1`.`ID` IN (SELECT `uf`.`friendid` FROM user_friends `uf` WHERE `uf`.`status` = '2' AND `uf`.`userid` = feed_setting_user_id )
17 OR `u1`.`ID` IN (SELECT `uf2`.`userid` FROM `user_friends` `uf2` WHERE `uf2`.`status` = '2' AND `uf2`.`friendid` = feed_setting_user_id)
18 AND `u1`.`ID` != feed_setting_user_id);
19
20DECLARE CONTINUE HANDLER FOR NOT FOUND SET exit_loop = TRUE;
21
22DROP TEMPORARY TABLE IF EXISTS unique_tbl; #precaution: when sp stops with error
23CREATE TEMPORARY TABLE unique_tbl (user_id BIGINT UNIQUE); #to avoid duplicate added unique
24
25INSERT INTO unique_tbl VALUES (feed_setting_user_id); # added input from sp
26
27 OPEN friend_cursor;
28 friend_loop: LOOP
29
30 FETCH friend_cursor INTO v_friendID;
31
32 IF exit_loop THEN
33 LEAVE friend_loop;
34 ELSE
35
36 REPLACE INTO unique_tbl VALUES (v_friendID); # since we need all unique id's using replace if exists
37
38 fof: BEGIN
39
40 DECLARE friend_of_friend_cursor CURSOR FOR
41
42
43 SELECT `u`.`ID` FROM `users` `u` WHERE `u`.`ID` NOT IN (feed_setting_user_id) AND `u`.`ID`
44 IN (SELECT `u1`.`ID` FROM `users` `u1`
45 WHERE `u1`.`ID` IN (SELECT `uf`.`friendid` FROM `user_friends` `uf` WHERE `uf`.`status` = '2' AND `uf`.`userid` = v_friendID AND `uf`.`friendid` != v_friendID)
46 OR `u1`.`ID` IN (SELECT `uf2`.`userid` FROM `user_friends` `uf2` WHERE `uf2`.`status` = '2' AND `uf2`.`friendid` = v_friendID AND `uf2`.`userid` != v_friendID)
47 AND `u1`.`ID` != v_friendID );
48
49 DECLARE CONTINUE HANDLER FOR NOT FOUND SET exit_loop1 = TRUE;
50
51 OPEN friend_of_friend_cursor;
52 friend_of_friend_loop: LOOP
53
54 FETCH friend_of_friend_cursor INTO FoFID;
55
56 IF exit_loop1 THEN
57 LEAVE friend_of_friend_loop;
58 END IF;
59
60 REPLACE INTO unique_tbl VALUES (FoFID);
61
62 END LOOP friend_of_friend_loop;
63 CLOSE friend_of_friend_cursor;
64 END;
65 END IF;
66 END LOOP friend_loop;
67 CLOSE friend_cursor;
68
69SELECT GROUP_CONCAT(user_id) FROM unique_tbl; #this will show result in comma seperated 2,34,56,78
70
71#cleanup
72DROP TEMPORARY TABLE IF EXISTS unique_tbl;
73
74END$$
75
76DELIMITER ;
77
78+-----------------------------------+
79| GROUP_CONCAT(user_id) |
80+-----------------------------------+
81| 1,2,3,5,6,7,8,9,11,14,15,19,20,26 |
82+-----------------------------------+
831 row in set (0,01 sec)
84
85Query OK, 0 rows affected (0,01 sec)
86
87+--------------------------------------+
88| GROUP_CONCAT(user_id) |
89+--------------------------------------+
90| 1,2,3,5,6,7,8,9,11,14,15,19,20,25,26 |
91+--------------------------------------+
921 row in set (0,01 sec)
93
94Query OK, 0 rows affected (0,01 sec)
95
96SELECT
97 `u`.`ID`
98FROM
99 `users` `u`
100WHERE `u`.`ID` IN
101 (SELECT
102 `u1`.`ID`
103 FROM
104 users `u1`
105 WHERE `u1`.`ID` IN
106 (SELECT
107 `uf`.`friendid`
108 FROM
109 user_friends `uf`
110 WHERE `uf`.`status` = '2'
111 AND `uf`.`userid` = feed_setting_user_id)
112 OR `u1`.`ID` IN
113 (SELECT
114 `uf2`.`userid`
115 FROM
116 `user_friends` `uf2`
117 WHERE `uf2`.`status` = '2'
118 AND `uf2`.`friendid` = feed_setting_user_id)
119 AND `u1`.`ID` != feed_setting_user_id);
120
121+----+
122| ID |
123+----+
124| 1 |
125| 14 |
126| 26 |
127+----+
1283 rows in set (0,00 sec)
129
130SELECT
131 `u`.`ID`
132FROM
133 `users` `u`
134WHERE `u`.`ID` NOT IN (feed_setting_user_id)
135 AND `u`.`ID` IN
136 (SELECT
137 `u1`.`ID`
138 FROM
139 `users` `u1`
140 WHERE `u1`.`ID` IN
141 (SELECT
142 `uf`.`friendid`
143 FROM
144 `user_friends` `uf`
145 WHERE `uf`.`status` = '2'
146 AND `uf`.`userid` = v_friendID
147 AND `uf`.`friendid` != v_friendID)
148 OR `u1`.`ID` IN
149 (SELECT
150 `uf2`.`userid`
151 FROM
152 `user_friends` `uf2`
153 WHERE `uf2`.`status` = '2'
154 AND `uf2`.`friendid` = v_friendID
155 AND `uf2`.`userid` != v_friendID)
156 AND `u1`.`ID` != v_friendID);
157
158+----+
159| ID |
160+----+
161| 2 |
162| 3 |
163| 5 |
164| 6 |
165| 7 |
166| 8 |
167| 9 |
168| 11 |
169| 19 |
170| 20 |
171+----+
17210 rows in set (0,00 sec)
173
174+----+
175| ID |
176+----+
177| 15 |
178| 25 |
179+----+
1802 rows in set (0,00 sec)
181
182Empty set (0,00 sec)