繰り返しになりますが、今のままでは相当効率がわるいです。
1レコードでユーザーidを2個管理するとどうしても主従関係が発生します
検索の際はunionして処理するのでパフォーマンスもSQLの視認性も期待できません。
followテーブルからstatusを切り離し、triggerでデータ投入・削除時に
別テーブルで管理すると良いかもしれません
sample
私の方からの説明が不足しているので以下サンプルをあげておきます
SQL
1create table follow(
2fid int primary key auto_increment,
3userid int not null,
4follow_userid int not null,
5statusid tinyint not null
6);
7create table user_relation (
8rid int primary key auto_increment,/*リレーションテーブル用のid*/
9fid int not null, /*followテーブルのid*/
10uid int not null, /*ユーザーid*/
11sid int not null, /*ステータスid*/
12flg tinyint default 0 /*削除用フラグ*/
13);
SQL
1drop trigger if exists trg_bef_insert_follow;
2drop trigger if exists trg_aft_insert_follow;
3drop trigger if exists trg_delete_follow;
4delimiter //
5create trigger trg_bef_insert_follow before insert on follow
6for each row begin
7update user_relation as t1,user_relation as t2
8set t1.flg=1,t2.flg=1 where t1.fid=t2.fid and
9(t1.uid=new.userid and t2.uid=new.follow_userid or
10t2.uid=new.userid and t1.uid=new.follow_userid);
11end
12//
13create trigger trg_aft_insert_follow after insert on follow
14for each row begin
15insert user_relation(fid,uid,sid) values
16(new.fid,new.userid,new.statusid),
17(new.fid,new.follow_userid,new.statusid);
18delete from user_relation where flg>0;
19end
20//
21create trigger trg_delete_follow after delete on follow
22for each row begin
23delete from user_relation where fid=old.fid;
24end
25//
26delimiter ;
SQL
1insert into follow(userid,follow_userid,statusid) values
2(1,2,1),(1,3,1),(1,7,1),(1,8,1),(4,3,1),
3(4,8,1),(4,7,1),(6,10,1),(7,10,2),(19,20,2),
4(8,10,1),(2,3,1),(15,1,1),(14,1,1),(13,1,1),
5(12,1,1),(11,1,2),(1,9,2),(3,20,2);
※ここまでは命題のまま
test
SQL
1delete from follow where fid=3;
このときuser_relationテーブルを確認するとfid=3のデータが削除されていることがわかります
- userid,follow_useridをひっくり返して投入
テストとして(2,1,3)のデータを投入します
このデータはfid=1のデータのuserid,follow_useridがひっくり返っているものです
SQL
1insert into follow(userid,follow_userid,statusid) values(2,1,3);
user_relationを確認するとfid=1のデータが削除され、新たにケツにデータが2件登録されます
友達の友達
- 上記testをした場合は1度データは元にもどしておいて下さい
SQL
1truncate follow;
2truncate user_relation;
3insert into follow(userid,follow_userid,statusid) values
4(1,2,1),(1,3,1),(1,7,1),(1,8,1),(4,3,1),
5(4,8,1),(4,7,1),(6,10,1),(7,10,2),(19,20,2),
6(8,10,1),(2,3,1),(15,1,1),(14,1,1),(13,1,1),
7(12,1,1),(11,1,2),(1,9,2),(3,20,2);
SQL
1select * from (
2select t1.uid as self,t2.uid as friend,t4.uid as friendoffriend from user_relation as t1
3inner join user_relation as t2 on t1.fid=t2.fid and not t1.rid=t2.rid and t1.sid=1
4inner join user_relation as t3 on t2.uid=t3.uid and not t2.fid=t3.fid and t3.sid=1
5inner join user_relation as t4 on t3.fid=t4.fid and not t3.uid=t4.uid
6) as s1 where not exists(
7select t5.uid as self,t6.uid as friend
8from user_relation as t5
9inner join user_relation as t6 on t5.fid=t6.fid and not t5.rid=t6.rid and t5.sid=1
10where s1.friendoffriend=t6.uid and s1.self=t5.uid
11)
12order by self,friend,friendoffriend;
- (応用)selfに対してfriendoffirendをユニークに表示します
SQL
1select distinct self,friendoffriend from (
2select t1.uid as self,t2.uid as friend,t4.uid as friendoffriend from user_relation as t1
3inner join user_relation as t2 on t1.fid=t2.fid and not t1.rid=t2.rid and t1.sid=1
4inner join user_relation as t3 on t2.uid=t3.uid and not t2.fid=t3.fid and t3.sid=1
5inner join user_relation as t4 on t3.fid=t4.fid and not t3.uid=t4.uid
6) as t7 where not exists(
7select t5.uid as self,t6.uid as friend
8from user_relation as t5
9inner join user_relation as t6 on t5.fid=t6.fid and not t5.rid=t6.rid and t5.sid=1
10where t7.friendoffriend=t6.uid and t7.self=t5.uid
11)
12order by self,friendoffriend
self | friendoffriend |
---|
1 | 4 |
1 | 10 |
2 | 4 |
2 | 7 |
2 | 8 |
2 | 12 |
2 | 13 |
2 | 14 |
2 | 15 |
3 | 7 |
3 | 8 |
3 | 12 |
3 | 13 |
3 | 14 |
3 | 15 |
4 | 1 |
4 | 2 |
4 | 10 |
6 | 8 |
7 | 2 |
7 | 3 |
7 | 8 |
7 | 12 |
7 | 13 |
7 | 14 |
7 | 15 |
8 | 2 |
8 | 3 |
8 | 6 |
8 | 7 |
8 | 12 |
8 | 13 |
8 | 14 |
8 | 15 |
10 | 1 |
10 | 4 |
12 | 2 |
12 | 3 |
12 | 7 |
12 | 8 |
12 | 13 |
12 | 14 |
12 | 15 |
13 | 2 |
13 | 3 |
13 | 7 |
13 | 8 |
13 | 12 |
13 | 14 |
13 | 15 |
14 | 2 |
14 | 3 |
14 | 7 |
14 | 8 |
14 | 12 |
14 | 13 |
14 | 15 |
15 | 2 |
15 | 3 |
15 | 7 |
15 | 8 |
15 | 12 |
15 | 13 |
15 | 14 |
参考
上記SQLはオプティマイザでは以下のように理解されているようです
SQL
1select distinct t1.uid AS self,t4.uid AS friendoffriend
2from user_relation as t1
3,user_relation as t2
4,user_relation as t3
5,user_relation as t4
6where 1
7and t1.sid = 1
8and t2.fid = t1.fid
9and t3.sid = 1
10and t3.uid = t2.uid
11and t4.fid = t3.fid
12and not t2.uid = t4.uid
13and not t1.fid = t3.fid
14and not t1.rid = t2.rid
15and not exists(
16 select 1
17 from user_relation as t5
18 ,user_relation as t6
19 where 1
20 and t5.sid = 1
21 and t6.fid = t5.fid
22 and t4.uid = t6.uid
23 and t1.uid = t5.uid
24 and not t5.rid = t6.rid
25 )
26order by t1.uid,t4.uid
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
2018/12/20 01:16
2018/12/20 02:43
2018/12/20 02:59
2018/12/20 03:14
2018/12/20 03:15
2018/12/20 08:58
2018/12/20 09:13
2018/12/20 10:26
2018/12/20 10:50
2018/12/21 07:48