SQLの検索によるFBの共通の友達特定機能

Question

CREATE TABLE  `follow` (
 id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
 userid int(11) NOT NULL,
 followingidid int(11) NOT NULL,
 status int(11) NOT NULL
);

INSERT INTO `follow` (`userid`,`followingidid`, `status`) VALUES
(1,2,1),(1,3,1),(1,7,1),(1,8,1),(4,3,1),
(4,8,1),(4,7,1),(6,10,1),(7,10,2),(19,20,2),
(8,10,1),(2,3,1)(15,1,1),(14,1,1),(13,1,1)
(12,1,1),(11,1,2),(1,9,2),(3,20,2);

現在このテーブルで友達情報を管理しておリます。

★テーブルについて★
１は友達、2は申請中、３は申請却下
１行で２ユーザーの情報を管理している
userid1,followingidid4, status1という行があれば１が４に申請をし４が許可をし友達になったということを示す

現在この運用で特に問題なく機能はしております。

SELECT * FROM user WHERE userid IN(

SELECT userid AS maykonw FROM follow WHERE followingid IN (SELECT userid FROM follow WHERE followingid = 1 and status = 1 UNION ALL SELECT followingid FROM follow WHERE userid = 1 and status = 1)
                                AND userid NOT IN (SELECT userid FROM follow WHERE followingid = 1 and status IN (1,2,3) UNION ALL SELECT followingid FROM follow WHERE userid = 1 and status IN (1,2,3)) AND status =1
UNION ALL

SELECT followingid AS maykonw FROM follow WHERE userid IN (SELECT userid FROM follow WHERE followingid = 1 and status = 1 UNION ALL SELECT followingid FROM follow WHERE userid = 1 and status = 1)
                                AND  followingid NOT IN (SELECT userid FROM follow WHERE followingid = 1 and status IN (1,2,3) UNION ALL SELECT followingid FROM follow WHERE userid = 1 and status IN (1,2,3)) AND status =1
)

AND userid NOT IN (1);

このSQL文はFBの友達かも機能です。
AB
BC
AC
BD

という友達関係が成立していた場合、AからみてCとDはB（友達）の友達にあたりますが表示させるのはDのみです。
すなわち友達の友達であるが自分は友達と友達に関係にないということです。

実装したい機能としましては、Dを表示させる際にDが自分と友達関係にある友達の中で誰の友達かを特定したいです。
表示イメージは

友達かも
D さん（共通の友達 B さん）

のような感じです。よろしくお願いします。

又、提示のSQL文についてはまだまだ未熟と思いますが、現在のところ問題なく機能しておりますのであまり深掘りしないで頂けると助かります。

【追記❶】

select v1.friend
from (
    SELECT case when userid=1 then followingid else userid end as friend
    FROM follow
    WHERE 1 in (userid, followingid) and status = 1
  ) v1 inner join (
    SELECT case when userid=4 then followingid else userid end as friend
    FROM follow
    WHERE 4 in (userid, followingid) and status = 1
  ) v2
  on v1.friend=v2.friend

このSQLをPHPファイルの変数に代入するとSQLとして認識されません。

解決方法をご回答ください。

Answer 1

繰り返しになりますが、今のままでは相当効率がわるいです。
１レコードでユーザーidを２個管理するとどうしても主従関係が発生します
検索の際はunionして処理するのでパフォーマンスもSQLの視認性も期待できません。

followテーブルからstatusを切り離し、triggerでデータ投入・削除時に
別テーブルで管理すると良いかもしれません

sample

私の方からの説明が不足しているので以下サンプルをあげておきます

• テーブル作成

create table follow(
fid int primary key auto_increment,
userid int not null,
follow_userid int not null,
statusid tinyint not null
);
create table user_relation (
rid int primary key auto_increment,/*リレーションテーブル用のid*/
fid int not null, /*followテーブルのid*/
uid int not null, /*ユーザーid*/
sid int not null, /*ステータスid*/
flg tinyint default 0 /*削除用フラグ*/
);

• followテーブルにtriggerを仕込みます

drop trigger if exists trg_bef_insert_follow;
drop trigger if exists trg_aft_insert_follow;
drop trigger if exists trg_delete_follow;
delimiter //
create trigger trg_bef_insert_follow before insert on follow
for each row begin 
update user_relation as t1,user_relation as t2
set t1.flg=1,t2.flg=1 where t1.fid=t2.fid and 
(t1.uid=new.userid and t2.uid=new.follow_userid or
t2.uid=new.userid and t1.uid=new.follow_userid);
end
//
create trigger trg_aft_insert_follow after insert on follow
for each row begin 
insert user_relation(fid,uid,sid) values
(new.fid,new.userid,new.statusid),
(new.fid,new.follow_userid,new.statusid);
delete from user_relation where flg>0;
end
//
create trigger trg_delete_follow after delete on follow
for each row begin 
delete from user_relation where fid=old.fid;
end
//
delimiter ;

• テストデータ

insert into follow(userid,follow_userid,statusid) values
(1,2,1),(1,3,1),(1,7,1),(1,8,1),(4,3,1),
(4,8,1),(4,7,1),(6,10,1),(7,10,2),(19,20,2),
(8,10,1),(2,3,1),(15,1,1),(14,1,1),(13,1,1),
(12,1,1),(11,1,2),(1,9,2),(3,20,2);

※ここまでは命題のまま

test

• 仮にfollowからデータを削除します

delete from follow where fid=3;

このときuser_relationテーブルを確認するとfid=3のデータが削除されていることがわかります

• userid,follow_useridをひっくり返して投入

テストとして(2,1,3)のデータを投入します
このデータはfid=1のデータのuserid,follow_useridがひっくり返っているものです

insert into follow(userid,follow_userid,statusid) values(2,1,3);

user_relationを確認するとfid=1のデータが削除され、新たにケツにデータが2件登録されます

友達の友達

• 上記testをした場合は１度データは元にもどしておいて下さい

truncate follow;
truncate user_relation;
insert into follow(userid,follow_userid,statusid) values
(1,2,1),(1,3,1),(1,7,1),(1,8,1),(4,3,1),
(4,8,1),(4,7,1),(6,10,1),(7,10,2),(19,20,2),
(8,10,1),(2,3,1),(15,1,1),(14,1,1),(13,1,1),
(12,1,1),(11,1,2),(1,9,2),(3,20,2);

• 友達の友達を表示します

select * from (
select t1.uid as self,t2.uid as friend,t4.uid as friendoffriend  from user_relation as t1
inner join  user_relation as t2 on t1.fid=t2.fid and not t1.rid=t2.rid and t1.sid=1
inner join  user_relation as t3 on t2.uid=t3.uid and not t2.fid=t3.fid and t3.sid=1
inner join  user_relation as t4 on t3.fid=t4.fid and not t3.uid=t4.uid
) as s1 where not exists(
select t5.uid as self,t6.uid as friend
from user_relation as t5
inner join  user_relation as t6 on t5.fid=t6.fid and not t5.rid=t6.rid and t5.sid=1
where s1.friendoffriend=t6.uid and s1.self=t5.uid
)
order by self,friend,friendoffriend;

• (応用)selfに対してfriendoffirendをユニークに表示します

select distinct self,friendoffriend from (
select t1.uid as self,t2.uid as friend,t4.uid as friendoffriend  from user_relation as t1
inner join  user_relation as t2 on t1.fid=t2.fid and not t1.rid=t2.rid and t1.sid=1
inner join  user_relation as t3 on t2.uid=t3.uid and not t2.fid=t3.fid and t3.sid=1
inner join  user_relation as t4 on t3.fid=t4.fid and not t3.uid=t4.uid
) as t7 where not exists(
select t5.uid as self,t6.uid as friend
from user_relation as t5
inner join  user_relation as t6 on t5.fid=t6.fid and not t5.rid=t6.rid and t5.sid=1
where t7.friendoffriend=t6.uid and t7.self=t5.uid
)
order 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はオプティマイザでは以下のように理解されているようです

select distinct t1.uid AS self,t4.uid AS friendoffriend
from user_relation as t1
,user_relation as t2
,user_relation as t3
,user_relation as t4
where 1
and t1.sid = 1
and t2.fid = t1.fid
and t3.sid = 1
and t3.uid = t2.uid
and t4.fid = t3.fid
and not t2.uid = t4.uid
and not t1.fid = t3.fid
and not t1.rid = t2.rid
and  not exists(
  select 1
  from user_relation as t5
  ,user_relation as t6
  where 1
  and t5.sid = 1
  and t6.fid = t5.fid
  and t4.uid = t6.uid
  and t1.uid = t5.uid
  and not t5.rid = t6.rid
  )
order by t1.uid,t4.uid

Comments

[sazi]

n:nの関係に対して、マップを作るのは普通です。
今回はそれが自己同士の関係であるという事で、主従関係をどう持たせるかとは別な話だと思うのですが。

[yambejp]

すべてuserとfollowuserの関係性が1レコードで管理されているのが問題だと思いますけどね
その原因が主従関係をもたせることなので、1レコードを別途2つに分割して管理すれば
解決は速いと思います

[sazi]

「１レコードでユーザーidを２個管理する＝主従関係が発生する」
ではないという事を言いたいのです。
問題なのは主従関係の持たせ方によってコストが掛かるようになっているという事なので、
それを解決するアプローチについてのコメントではありません。

[sazi]

実装については、MySQLでの実装経験が豊富なyambejpさんに従った方が確実だと思ってますです。

[yambejp]

自分の文書をよく読み返してみると
「主従関係をもつから非効率的」というような表現になっていて
確かに意味合いがおかしいですね
ようは１レコードをいかに効率よく分解するかが、今回の回答の肝です
ちなみに今回はupdateされたときの処理は省略しています
（やっぱ書いておいたほうがよいかな・・・）

[Chandler_Bing]

ありがとうございます。
行おうとしていることはわかるのですが、
そもそもtriggerの役割はなんののでしょうか。

又
followテーブルにtriggerを仕込みます

の直下のSQL文の実行はendで区切って行っても、全て書いても
エラーになります。

#1064 - SQL構文エラーです。バージョンに対応するマニュアルを参照して正しい構文を確認してください。 : '' 付近 6 行目

[yambejp]

> triggerの役割

followテーブルの構造がこのままでは煩雑になるのでuser_relationというテーブルに
1レコードを2つにわけています。

> エラーになります。
基本的にはすべてコピペで順番に投入していけば動くように載せたのですが
MySQLのバージョンはいくつをご利用でしょうか？

ちなみにMySQLへのインタフェースはphpMyAdminなど利用してませんか？

[Chandler_Bing]

5.7.23 - MySQL Community Server
がバージョンです

[yambejp]

serverが5.7であればtriggerは普通に実装されているはずです。
create tableとかはどいうインタフェースで処理していますか？
phpからプログラムで処理している？
繰り返しになりますがphpMyAdminなど利用してませんか？

[Chandler_Bing]

とりあえず、このトリガー等については、初心者の私のレベルを超越しておりますので一度中級者の参考書等で一度勉強します。
ありがとうございました。

Answer 2

SQLがどうこうと云うより、友達たちの共通の友達かもしれないので、一人に特定はでき無い場合があります。

追記

元のSQLから組み立てるのではなく、元から考えるようにしないと、SQLが肥大化してしまいますよ。
[共通の友人]

select v1.friend 
from (
    SELECT case when userid=1 then followingid else userid end as friend 
    FROM follow 
    WHERE 1 in (userid, followingid) and status = 1 
  ) v1 inner join (
    SELECT case when userid=4 then followingid else userid end as friend 
    FROM follow 
    WHERE 4 in (userid, followingid) and status = 1 
  ) v2
  on v1.friend=v2.friend

Comments

[Chandler_Bing]

ありがとうございます。
複数いる場合は、複数表示したいと考えております。

未だにハッキリは見えませんが、
友達かもの検索で
userid を 取得してそれをPHPの変数に一旦格納し、それを使って再び SQL検索をするという方法ではどうでしょうか。

[sazi]

「友達かも」で求めているSQLでのollow のもう一方の項目の値が質問にある求めたいものです。

[sazi]

１ユーザーに対する「友達かも」に対してなら検索回数は多くならないでしょうから、言われるように再度検索の方がシンプルです。

[Chandler_Bing]

ありがとうございます。
では一度サイド検索で模索し、
分からない場合はまた質問させて頂きます。本当にありがとうございます。

[sazi]

割とコストが高いSQLだと思うので、大量データによる性能はチェックしておいた方が良いですよ。
性能出せない場合には、テーブル設計やり直す場合だってありますから。

[Chandler_Bing]

ありがとうございます。必ずチェック致します。
user1の場合友達かもとして表示するユーザーは
user4とuser10です。どちらでも問題ないですがuser4に焦点を当てます。

SELECT userid FROM follow WHERE followingid = 1 and status = 1 UNION ALL SELECT followingid FROM follow WHERE userid = 1 and status = 1
これで1の友達(2,3,7,8,12,13,14,15)が表示され、

SELECT userid FROM follow WHERE followingid = 4 and status = 1 UNION ALL SELECT followingid FROM follow WHERE userid = 4 and status = 1
これで4の友達(3,7,8)が表示されます。

この二つの共通する数字(ここでは3,7,8)を使用し、userテーブルにIN句を掛けuserテーブルから
ユーザーが欲しいです。

この二つの共通する数字(ここでは3,7,8)を使用し、userテーブルにIN句を掛けuserテーブルから
ユーザーが欲しいです。

『この二つの共通する数字(ここでは3,7,8)を使用し、userテーブルにIN句を掛けuserテーブルから
ユーザーが欲しいです。』の実現方法をご教授お願いします。

[Chandler_Bing]

お疲れ様です。
直近の質問に対する、SQLを思いついたのですが
エラーが出てしまいます。
簡単に解決できそうな問題のような気がしますので、新たに質問を立てますが
引き続きこの質問に対するご教授お願いいたします。

[Chandler_Bing]

select v1.friend
from (
SELECT case when userid=1 then followingid else userid end as friend
FROM follow
WHERE 1 in (userid, followingid) and status = 1
) v1 inner join (
SELECT case when userid=4 then followingid else userid end as friend
FROM follow
WHERE 4 in (userid, followingid) and status = 1
) v2
on v1.friend=v2.friend

の実行は問題なく作動す流のですが、
phpファイルの中で、SQL文として読み込まれていません。
追記に書き込みいたします。画像の方が今回は判断しやすいと思いますので画像で書き込みいたします。