teratail header banner
teratail header banner
質問するログイン新規登録

回答編集履歴

6

調整

2018/12/20 04:06

投稿

yambejp
yambejp

スコア117912

answer CHANGED
@@ -210,7 +210,7 @@
210
210
  and not t1.fid = t3.fid
211
211
  and not t1.rid = t2.rid
212
212
  and not exists(
213
- select t5.uid as self,t6.uid as friend
213
+ select 1
214
214
  from user_relation as t5
215
215
  ,user_relation as t6
216
216
  where 1

5

追記

2018/12/20 04:06

投稿

yambejp
yambejp

スコア117912

answer CHANGED
@@ -190,4 +190,35 @@
190
190
  |15|8|
191
191
  |15|12|
192
192
  |15|13|
193
- |15|14|
193
+ |15|14|
194
+
195
+ # 参考
196
+ 上記SQLはオプティマイザでは以下のように理解されているようです
197
+ ```SQL
198
+ select distinct t1.uid AS self,t4.uid AS friendoffriend
199
+ from user_relation as t1
200
+ ,user_relation as t2
201
+ ,user_relation as t3
202
+ ,user_relation as t4
203
+ where 1
204
+ and t1.sid = 1
205
+ and t2.fid = t1.fid
206
+ and t3.sid = 1
207
+ and t3.uid = t2.uid
208
+ and t4.fid = t3.fid
209
+ and not t2.uid = t4.uid
210
+ and not t1.fid = t3.fid
211
+ and not t1.rid = t2.rid
212
+ and not exists(
213
+ select t5.uid as self,t6.uid as friend
214
+ from user_relation as t5
215
+ ,user_relation as t6
216
+ where 1
217
+ and t5.sid = 1
218
+ and t6.fid = t5.fid
219
+ and t4.uid = t6.uid
220
+ and t1.uid = t5.uid
221
+ and not t5.rid = t6.rid
222
+ )
223
+ order by t1.uid,t4.uid
224
+ ```

4

chousei

2018/12/20 03:56

投稿

yambejp
yambejp

スコア117912

answer CHANGED
@@ -115,11 +115,11 @@
115
115
  inner join user_relation as t2 on t1.fid=t2.fid and not t1.rid=t2.rid and t1.sid=1
116
116
  inner join user_relation as t3 on t2.uid=t3.uid and not t2.fid=t3.fid and t3.sid=1
117
117
  inner join user_relation as t4 on t3.fid=t4.fid and not t3.uid=t4.uid
118
- ) as s1 where not exists(
118
+ ) as t7 where not exists(
119
119
  select t5.uid as self,t6.uid as friend
120
120
  from user_relation as t5
121
121
  inner join user_relation as t6 on t5.fid=t6.fid and not t5.rid=t6.rid and t5.sid=1
122
- where s1.friendoffriend=t6.uid and s1.self=t5.uid
122
+ where t7.friendoffriend=t6.uid and t7.self=t5.uid
123
123
  )
124
124
  order by self,friendoffriend
125
125
  ```

3

追記

2018/12/20 02:45

投稿

yambejp
yambejp

スコア117912

answer CHANGED
@@ -77,4 +77,117 @@
77
77
  ```SQL
78
78
  insert into follow(userid,follow_userid,statusid) values(2,1,3);
79
79
  ```
80
- user_relationを確認するとfid=1のデータが削除され、新たにケツにデータが2件登録されます
80
+ user_relationを確認するとfid=1のデータが削除され、新たにケツにデータが2件登録されます
81
+
82
+ # 友達の友達
83
+ - 上記testをした場合は1度データは元にもどしておいて下さい
84
+ ```SQL
85
+ truncate follow;
86
+ truncate user_relation;
87
+ insert into follow(userid,follow_userid,statusid) values
88
+ (1,2,1),(1,3,1),(1,7,1),(1,8,1),(4,3,1),
89
+ (4,8,1),(4,7,1),(6,10,1),(7,10,2),(19,20,2),
90
+ (8,10,1),(2,3,1),(15,1,1),(14,1,1),(13,1,1),
91
+ (12,1,1),(11,1,2),(1,9,2),(3,20,2);
92
+ ```
93
+
94
+ - 友達の友達を表示します
95
+ ```SQL
96
+ select * from (
97
+ select t1.uid as self,t2.uid as friend,t4.uid as friendoffriend from user_relation as t1
98
+ inner join user_relation as t2 on t1.fid=t2.fid and not t1.rid=t2.rid and t1.sid=1
99
+ inner join user_relation as t3 on t2.uid=t3.uid and not t2.fid=t3.fid and t3.sid=1
100
+ inner join user_relation as t4 on t3.fid=t4.fid and not t3.uid=t4.uid
101
+ ) as s1 where not exists(
102
+ select t5.uid as self,t6.uid as friend
103
+ from user_relation as t5
104
+ inner join user_relation as t6 on t5.fid=t6.fid and not t5.rid=t6.rid and t5.sid=1
105
+ where s1.friendoffriend=t6.uid and s1.self=t5.uid
106
+ )
107
+ order by self,friend,friendoffriend;
108
+ ```
109
+
110
+ - (応用)selfに対してfriendoffirendをユニークに表示します
111
+
112
+ ```SQL
113
+ select distinct self,friendoffriend from (
114
+ select t1.uid as self,t2.uid as friend,t4.uid as friendoffriend from user_relation as t1
115
+ inner join user_relation as t2 on t1.fid=t2.fid and not t1.rid=t2.rid and t1.sid=1
116
+ inner join user_relation as t3 on t2.uid=t3.uid and not t2.fid=t3.fid and t3.sid=1
117
+ inner join user_relation as t4 on t3.fid=t4.fid and not t3.uid=t4.uid
118
+ ) as s1 where not exists(
119
+ select t5.uid as self,t6.uid as friend
120
+ from user_relation as t5
121
+ inner join user_relation as t6 on t5.fid=t6.fid and not t5.rid=t6.rid and t5.sid=1
122
+ where s1.friendoffriend=t6.uid and s1.self=t5.uid
123
+ )
124
+ order by self,friendoffriend
125
+ ```
126
+ - 結果
127
+
128
+ |self|friendoffriend|
129
+ |--:|--:|
130
+ |1|4|
131
+ |1|10|
132
+ |2|4|
133
+ |2|7|
134
+ |2|8|
135
+ |2|12|
136
+ |2|13|
137
+ |2|14|
138
+ |2|15|
139
+ |3|7|
140
+ |3|8|
141
+ |3|12|
142
+ |3|13|
143
+ |3|14|
144
+ |3|15|
145
+ |4|1|
146
+ |4|2|
147
+ |4|10|
148
+ |6|8|
149
+ |7|2|
150
+ |7|3|
151
+ |7|8|
152
+ |7|12|
153
+ |7|13|
154
+ |7|14|
155
+ |7|15|
156
+ |8|2|
157
+ |8|3|
158
+ |8|6|
159
+ |8|7|
160
+ |8|12|
161
+ |8|13|
162
+ |8|14|
163
+ |8|15|
164
+ |10|1|
165
+ |10|4|
166
+ |12|2|
167
+ |12|3|
168
+ |12|7|
169
+ |12|8|
170
+ |12|13|
171
+ |12|14|
172
+ |12|15|
173
+ |13|2|
174
+ |13|3|
175
+ |13|7|
176
+ |13|8|
177
+ |13|12|
178
+ |13|14|
179
+ |13|15|
180
+ |14|2|
181
+ |14|3|
182
+ |14|7|
183
+ |14|8|
184
+ |14|12|
185
+ |14|13|
186
+ |14|15|
187
+ |15|2|
188
+ |15|3|
189
+ |15|7|
190
+ |15|8|
191
+ |15|12|
192
+ |15|13|
193
+ |15|14|

2

sample

2018/12/20 02:39

投稿

yambejp
yambejp

スコア117912

answer CHANGED
@@ -3,4 +3,78 @@
3
3
  検索の際はunionして処理するのでパフォーマンスもSQLの視認性も期待できません。
4
4
 
5
5
  followテーブルからstatusを切り離し、triggerでデータ投入・削除時に
6
- 別テーブルで管理すると良いかもしれません
6
+ 別テーブルで管理すると良いかもしれません
7
+
8
+ # sample
9
+ 私の方からの説明が不足しているので以下サンプルをあげておきます
10
+ - テーブル作成
11
+ ```SQL
12
+ create table follow(
13
+ fid int primary key auto_increment,
14
+ userid int not null,
15
+ follow_userid int not null,
16
+ statusid tinyint not null
17
+ );
18
+ create table user_relation (
19
+ rid int primary key auto_increment,/*リレーションテーブル用のid*/
20
+ fid int not null, /*followテーブルのid*/
21
+ uid int not null, /*ユーザーid*/
22
+ sid int not null, /*ステータスid*/
23
+ flg tinyint default 0 /*削除用フラグ*/
24
+ );
25
+ ```
26
+
27
+ - followテーブルにtriggerを仕込みます
28
+ ```SQL
29
+ drop trigger if exists trg_bef_insert_follow;
30
+ drop trigger if exists trg_aft_insert_follow;
31
+ drop trigger if exists trg_delete_follow;
32
+ delimiter //
33
+ create trigger trg_bef_insert_follow before insert on follow
34
+ for each row begin
35
+ update user_relation as t1,user_relation as t2
36
+ set t1.flg=1,t2.flg=1 where t1.fid=t2.fid and
37
+ (t1.uid=new.userid and t2.uid=new.follow_userid or
38
+ t2.uid=new.userid and t1.uid=new.follow_userid);
39
+ end
40
+ //
41
+ create trigger trg_aft_insert_follow after insert on follow
42
+ for each row begin
43
+ insert user_relation(fid,uid,sid) values
44
+ (new.fid,new.userid,new.statusid),
45
+ (new.fid,new.follow_userid,new.statusid);
46
+ delete from user_relation where flg>0;
47
+ end
48
+ //
49
+ create trigger trg_delete_follow after delete on follow
50
+ for each row begin
51
+ delete from user_relation where fid=old.fid;
52
+ end
53
+ //
54
+ delimiter ;
55
+ ```
56
+
57
+ - テストデータ
58
+ ```SQL
59
+ insert into follow(userid,follow_userid,statusid) values
60
+ (1,2,1),(1,3,1),(1,7,1),(1,8,1),(4,3,1),
61
+ (4,8,1),(4,7,1),(6,10,1),(7,10,2),(19,20,2),
62
+ (8,10,1),(2,3,1),(15,1,1),(14,1,1),(13,1,1),
63
+ (12,1,1),(11,1,2),(1,9,2),(3,20,2);
64
+ ```
65
+ ※ここまでは命題のまま
66
+
67
+ # test
68
+ - 仮にfollowからデータを削除します
69
+ ```SQL
70
+ delete from follow where fid=3;
71
+ ```
72
+ このときuser_relationテーブルを確認するとfid=3のデータが削除されていることがわかります
73
+
74
+ - userid,follow_useridをひっくり返して投入
75
+ テストとして(2,1,3)のデータを投入します
76
+ このデータはfid=1のデータのuserid,follow_useridがひっくり返っているものです
77
+ ```SQL
78
+ insert into follow(userid,follow_userid,statusid) values(2,1,3);
79
+ ```
80
+ user_relationを確認するとfid=1のデータが削除され、新たにケツにデータが2件登録されます

1

調整

2018/12/20 01:57

投稿

yambejp
yambejp

スコア117912

answer CHANGED
@@ -1,3 +1,6 @@
1
1
  繰り返しになりますが、今のままでは相当効率がわるいです。
2
2
  1レコードでユーザーidを2個管理するとどうしても主従関係が発生します
3
- 検索の際はunionして処理するのでパフォーマンスもSQLの視認性も期待できません。
3
+ 検索の際はunionして処理するのでパフォーマンスもSQLの視認性も期待できません。
4
+
5
+ followテーブルからstatusを切り離し、triggerでデータ投入・削除時に
6
+ 別テーブルで管理すると良いかもしれません