teratail
回答率
85.47%
質問するログイン新規登録
トップMySQLに関する質問SQLの検索によるFBの共通の友達特定機能

編集履歴

回答編集履歴

6

調整

2018/12/20 04:06

投稿

yambejp
yambejp

スコア114876

test CHANGED
@@ -422,7 +422,7 @@
422
422
 
423
423
  and not exists(
424
424
 
425
- select t5.uid as self,t6.uid as friend
425
+ select 1
426
426
 
427
427
  from user_relation as t5
428
428
 

5

追記

2018/12/20 04:06

投稿

yambejp
yambejp

スコア114876

test CHANGED
@@ -383,3 +383,65 @@
383
383
  |15|13|
384
384
 
385
385
  |15|14|
386
+
387
+
388
+
389
+ # 参考
390
+
391
+ 上記SQLはオプティマイザでは以下のように理解されているようです
392
+
393
+ ```SQL
394
+
395
+ select distinct t1.uid AS self,t4.uid AS friendoffriend
396
+
397
+ from user_relation as t1
398
+
399
+ ,user_relation as t2
400
+
401
+ ,user_relation as t3
402
+
403
+ ,user_relation as t4
404
+
405
+ where 1
406
+
407
+ and t1.sid = 1
408
+
409
+ and t2.fid = t1.fid
410
+
411
+ and t3.sid = 1
412
+
413
+ and t3.uid = t2.uid
414
+
415
+ and t4.fid = t3.fid
416
+
417
+ and not t2.uid = t4.uid
418
+
419
+ and not t1.fid = t3.fid
420
+
421
+ and not t1.rid = t2.rid
422
+
423
+ and not exists(
424
+
425
+ select t5.uid as self,t6.uid as friend
426
+
427
+ from user_relation as t5
428
+
429
+ ,user_relation as t6
430
+
431
+ where 1
432
+
433
+ and t5.sid = 1
434
+
435
+ and t6.fid = t5.fid
436
+
437
+ and t4.uid = t6.uid
438
+
439
+ and t1.uid = t5.uid
440
+
441
+ and not t5.rid = t6.rid
442
+
443
+ )
444
+
445
+ order by t1.uid,t4.uid
446
+
447
+ ```

4

chousei

2018/12/20 03:56

投稿

yambejp
yambejp

スコア114876

test CHANGED
@@ -232,7 +232,7 @@
232
232
 
233
233
  inner join user_relation as t4 on t3.fid=t4.fid and not t3.uid=t4.uid
234
234
 
235
- ) as s1 where not exists(
235
+ ) as t7 where not exists(
236
236
 
237
237
  select t5.uid as self,t6.uid as friend
238
238
 
@@ -240,7 +240,7 @@
240
240
 
241
241
  inner join user_relation as t6 on t5.fid=t6.fid and not t5.rid=t6.rid and t5.sid=1
242
242
 
243
- where s1.friendoffriend=t6.uid and s1.self=t5.uid
243
+ where t7.friendoffriend=t6.uid and t7.self=t5.uid
244
244
 
245
245
  )
246
246
 

3

追記

2018/12/20 02:45

投稿

yambejp
yambejp

スコア114876

test CHANGED
@@ -157,3 +157,229 @@
157
157
  ```
158
158
 
159
159
  user_relationを確認するとfid=1のデータが削除され、新たにケツにデータが2件登録されます
160
+
161
+
162
+
163
+ # 友達の友達
164
+
165
+ - 上記testをした場合は１度データは元にもどしておいて下さい
166
+
167
+ ```SQL
168
+
169
+ truncate follow;
170
+
171
+ truncate user_relation;
172
+
173
+ insert into follow(userid,follow_userid,statusid) values
174
+
175
+ (1,2,1),(1,3,1),(1,7,1),(1,8,1),(4,3,1),
176
+
177
+ (4,8,1),(4,7,1),(6,10,1),(7,10,2),(19,20,2),
178
+
179
+ (8,10,1),(2,3,1),(15,1,1),(14,1,1),(13,1,1),
180
+
181
+ (12,1,1),(11,1,2),(1,9,2),(3,20,2);
182
+
183
+ ```
184
+
185
+
186
+
187
+ - 友達の友達を表示します
188
+
189
+ ```SQL
190
+
191
+ select * from (
192
+
193
+ select t1.uid as self,t2.uid as friend,t4.uid as friendoffriend from user_relation as t1
194
+
195
+ inner join user_relation as t2 on t1.fid=t2.fid and not t1.rid=t2.rid and t1.sid=1
196
+
197
+ inner join user_relation as t3 on t2.uid=t3.uid and not t2.fid=t3.fid and t3.sid=1
198
+
199
+ inner join user_relation as t4 on t3.fid=t4.fid and not t3.uid=t4.uid
200
+
201
+ ) as s1 where not exists(
202
+
203
+ select t5.uid as self,t6.uid as friend
204
+
205
+ from user_relation as t5
206
+
207
+ inner join user_relation as t6 on t5.fid=t6.fid and not t5.rid=t6.rid and t5.sid=1
208
+
209
+ where s1.friendoffriend=t6.uid and s1.self=t5.uid
210
+
211
+ )
212
+
213
+ order by self,friend,friendoffriend;
214
+
215
+ ```
216
+
217
+
218
+
219
+ - (応用)selfに対してfriendoffirendをユニークに表示します
220
+
221
+
222
+
223
+ ```SQL
224
+
225
+ select distinct self,friendoffriend from (
226
+
227
+ select t1.uid as self,t2.uid as friend,t4.uid as friendoffriend from user_relation as t1
228
+
229
+ inner join user_relation as t2 on t1.fid=t2.fid and not t1.rid=t2.rid and t1.sid=1
230
+
231
+ inner join user_relation as t3 on t2.uid=t3.uid and not t2.fid=t3.fid and t3.sid=1
232
+
233
+ inner join user_relation as t4 on t3.fid=t4.fid and not t3.uid=t4.uid
234
+
235
+ ) as s1 where not exists(
236
+
237
+ select t5.uid as self,t6.uid as friend
238
+
239
+ from user_relation as t5
240
+
241
+ inner join user_relation as t6 on t5.fid=t6.fid and not t5.rid=t6.rid and t5.sid=1
242
+
243
+ where s1.friendoffriend=t6.uid and s1.self=t5.uid
244
+
245
+ )
246
+
247
+ order by self,friendoffriend
248
+
249
+ ```
250
+
251
+ - 結果
252
+
253
+
254
+
255
+ |self|friendoffriend|
256
+
257
+ |--:|--:|
258
+
259
+ |1|4|
260
+
261
+ |1|10|
262
+
263
+ |2|4|
264
+
265
+ |2|7|
266
+
267
+ |2|8|
268
+
269
+ |2|12|
270
+
271
+ |2|13|
272
+
273
+ |2|14|
274
+
275
+ |2|15|
276
+
277
+ |3|7|
278
+
279
+ |3|8|
280
+
281
+ |3|12|
282
+
283
+ |3|13|
284
+
285
+ |3|14|
286
+
287
+ |3|15|
288
+
289
+ |4|1|
290
+
291
+ |4|2|
292
+
293
+ |4|10|
294
+
295
+ |6|8|
296
+
297
+ |7|2|
298
+
299
+ |7|3|
300
+
301
+ |7|8|
302
+
303
+ |7|12|
304
+
305
+ |7|13|
306
+
307
+ |7|14|
308
+
309
+ |7|15|
310
+
311
+ |8|2|
312
+
313
+ |8|3|
314
+
315
+ |8|6|
316
+
317
+ |8|7|
318
+
319
+ |8|12|
320
+
321
+ |8|13|
322
+
323
+ |8|14|
324
+
325
+ |8|15|
326
+
327
+ |10|1|
328
+
329
+ |10|4|
330
+
331
+ |12|2|
332
+
333
+ |12|3|
334
+
335
+ |12|7|
336
+
337
+ |12|8|
338
+
339
+ |12|13|
340
+
341
+ |12|14|
342
+
343
+ |12|15|
344
+
345
+ |13|2|
346
+
347
+ |13|3|
348
+
349
+ |13|7|
350
+
351
+ |13|8|
352
+
353
+ |13|12|
354
+
355
+ |13|14|
356
+
357
+ |13|15|
358
+
359
+ |14|2|
360
+
361
+ |14|3|
362
+
363
+ |14|7|
364
+
365
+ |14|8|
366
+
367
+ |14|12|
368
+
369
+ |14|13|
370
+
371
+ |14|15|
372
+
373
+ |15|2|
374
+
375
+ |15|3|
376
+
377
+ |15|7|
378
+
379
+ |15|8|
380
+
381
+ |15|12|
382
+
383
+ |15|13|
384
+
385
+ |15|14|

2

sample

2018/12/20 02:39

投稿

yambejp
yambejp

スコア114876

test CHANGED
@@ -9,3 +9,151 @@
9
9
  followテーブルからstatusを切り離し、triggerでデータ投入・削除時に
10
10
 
11
11
  別テーブルで管理すると良いかもしれません
12
+
13
+
14
+
15
+ # sample
16
+
17
+ 私の方からの説明が不足しているので以下サンプルをあげておきます
18
+
19
+ - テーブル作成
20
+
21
+ ```SQL
22
+
23
+ create table follow(
24
+
25
+ fid int primary key auto_increment,
26
+
27
+ userid int not null,
28
+
29
+ follow_userid int not null,
30
+
31
+ statusid tinyint not null
32
+
33
+ );
34
+
35
+ create table user_relation (
36
+
37
+ rid int primary key auto_increment,/*リレーションテーブル用のid*/
38
+
39
+ fid int not null, /*followテーブルのid*/
40
+
41
+ uid int not null, /*ユーザーid*/
42
+
43
+ sid int not null, /*ステータスid*/
44
+
45
+ flg tinyint default 0 /*削除用フラグ*/
46
+
47
+ );
48
+
49
+ ```
50
+
51
+
52
+
53
+ - followテーブルにtriggerを仕込みます
54
+
55
+ ```SQL
56
+
57
+ drop trigger if exists trg_bef_insert_follow;
58
+
59
+ drop trigger if exists trg_aft_insert_follow;
60
+
61
+ drop trigger if exists trg_delete_follow;
62
+
63
+ delimiter //
64
+
65
+ create trigger trg_bef_insert_follow before insert on follow
66
+
67
+ for each row begin
68
+
69
+ update user_relation as t1,user_relation as t2
70
+
71
+ set t1.flg=1,t2.flg=1 where t1.fid=t2.fid and
72
+
73
+ (t1.uid=new.userid and t2.uid=new.follow_userid or
74
+
75
+ t2.uid=new.userid and t1.uid=new.follow_userid);
76
+
77
+ end
78
+
79
+ //
80
+
81
+ create trigger trg_aft_insert_follow after insert on follow
82
+
83
+ for each row begin
84
+
85
+ insert user_relation(fid,uid,sid) values
86
+
87
+ (new.fid,new.userid,new.statusid),
88
+
89
+ (new.fid,new.follow_userid,new.statusid);
90
+
91
+ delete from user_relation where flg>0;
92
+
93
+ end
94
+
95
+ //
96
+
97
+ create trigger trg_delete_follow after delete on follow
98
+
99
+ for each row begin
100
+
101
+ delete from user_relation where fid=old.fid;
102
+
103
+ end
104
+
105
+ //
106
+
107
+ delimiter ;
108
+
109
+ ```
110
+
111
+
112
+
113
+ - テストデータ
114
+
115
+ ```SQL
116
+
117
+ insert into follow(userid,follow_userid,statusid) values
118
+
119
+ (1,2,1),(1,3,1),(1,7,1),(1,8,1),(4,3,1),
120
+
121
+ (4,8,1),(4,7,1),(6,10,1),(7,10,2),(19,20,2),
122
+
123
+ (8,10,1),(2,3,1),(15,1,1),(14,1,1),(13,1,1),
124
+
125
+ (12,1,1),(11,1,2),(1,9,2),(3,20,2);
126
+
127
+ ```
128
+
129
+ ※ここまでは命題のまま
130
+
131
+
132
+
133
+ # test
134
+
135
+ - 仮にfollowからデータを削除します
136
+
137
+ ```SQL
138
+
139
+ delete from follow where fid=3;
140
+
141
+ ```
142
+
143
+ このときuser_relationテーブルを確認するとfid=3のデータが削除されていることがわかります
144
+
145
+
146
+
147
+ - userid,follow_useridをひっくり返して投入
148
+
149
+ テストとして(2,1,3)のデータを投入します
150
+
151
+ このデータはfid=1のデータのuserid,follow_useridがひっくり返っているものです
152
+
153
+ ```SQL
154
+
155
+ insert into follow(userid,follow_userid,statusid) values(2,1,3);
156
+
157
+ ```
158
+
159
+ user_relationを確認するとfid=1のデータが削除され、新たにケツにデータが2件登録されます

1

調整

2018/12/20 01:57

投稿

yambejp
yambejp

スコア114876

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