SQLで友達の友達を取得

Question

ユーザーテーブル

ID NAME
1 ヤマダ
2 Chandler
3 Monica
4 Joey

友達申請テーブル
FROM TO
1 　　　2
2 　　　3
4 　　　1

【修正】

のような2つのテーブルがあるとします。
この二つを使って友達の友達を取得する方法を教えていただきたいです。

今回の場合だと1は2,4と友達で
2(1の友達)は3と友達関係にあります。

さらに1と3は友達ではありません。この値がほしいです。

この友達申請に入っているレコードは友達関係にあります。（申請中や、却下はありません）
またこの問題として友達関係になるには、TOでもFROMのどちらからでも可能です。（１と２、４と１)

友達申請にユーザーをLEFT JOINJで2つ結合しました。
この段階で、自分(1)の友人は把握できますが、ここからどうやって友達の友達を取得できますか。

Comments

[m.ts10806]

がんばればできます。
アドバイスを求めるのであればまともな質問にしてください。
「質問テンプレート」を利用し「質問するときのヒント」を熟読して質問を再編してください。
https://teratail.com/help/question-tips
196も質問してきておいてこの質問の仕方はどうなんでしょう。

[yambejp]

> さらに1と3は友達ではありません

２行前で友達って言ってますが・・・

[hihijiji]

相互友達申請があった場合に友達関係とみなすのがいいと思います。

[Orlofsky]

>２行前で友達って言ってますが・・・

片思い、ってヤツでしょう。

[Zuishin]

テーブルが大きくなると友達の友達候補は膨大な人数になると思いますので、それを一つの select で取得しようとすると時間がかかりそうです。
友達の友達テーブルを作り、友達申請や破棄が一件あるたびに更新する方が良いのではないかと思います。それなら多少時間がかかってもユーザーには「更新には時間がかかる可能性があります」と伝えておけば済みます。

[m.ts10806]

「そういうコードを書いてください」という作業依頼　というスタンスは変わってないわけですね。
https://teratail.com/help/question-tips
https://teratail.com/help/avoid-asking

Answer 1

ここからどうやって友達の友達を取得できますか。

もう一度友達申請テーブルをJOINします。
１回目でとれるのが自分と友達のID
２回目に１回目の友達のID（TO）をFROMに結合してあげたらそのTOが友達の友達になります。

FROM 1	TO 1	FROM 2	TO 2
1	2	2	3
2	3	null	null
4	1	1	2

これにそれぞれのIDに対応するNAMEをわりあててあげれば友達の友達の名前が取れます。

SELECT
    u.NAME,
    f2u.NAME
FROM
    ユーザーテーブル u
INNER JOIN
    友達申請テーブル f1
ON
    u.ID = f.FROM
INNER JOIN
    友達申請テーブル f2
ON
    f.TO= ff.FROM
INNER JOIN
    ユーザーテーブル f2u
ON
    ff.TO = f2u.ID

INNER JOINにしているのは友達や友達の友達がいないレコードを省くためです。

注意：このSQLでは１は２の友達ですが２は１の友達ではありません。そこを考慮するともう少し複雑なSQLになります。

Comments

[Chandler_Bing]

ありがとうございます。

Answer 2

ユーザーをLEFT JOINで2つ結合しました。

⇒実際のSQLを記載して欲しかったですね。
色々な結合方法があるので、この文章だけではどういうJOINか分かりません。

とりあえず、FROM-TOが相互と言うことなので、
私の方からは、友達テーブル作る方法で。

友達テーブル
FROM TO
1 　　　2
2 　　　3
4 　　　1
2 　　　1　←FROM TOの逆を追加
3 　　　2　←FROM TOの逆を追加
1 　　　4　←FROM TOの逆を追加

SELECT	T1.ID
	,	T3.FROM
FROM	ユーザーテーブル　T1
			LEFT JOIN 友達テーブル T2 ON(
				T2.FROM	= T1.ID
			)
				LEFT JOIN  友達テーブル T3 ON(
					T3.FROM	= T2.TO
				)
WHERE	NOT EXISTS(
			SELECT	'X'
			FROM	友達テーブル T4
			WHERE	T4.TO		= T1.ID
				AND	T4.FROM		= T3.FROM
		)

どうしても、友達申請テーブルで表現したい場合、
UNION ALL等を使って、
↑のような抽出結果になる友達ビューを作成して、上記SQLにするとかですね。

Answer 3

FROM と TO の両方向があるので面倒になっているので、共通テーブルなどを使って単方向にしてみてはどうでしょうか？

WITH W_F AS (
  SELECT [FROM] F, [TO] T FROM 友達申請テーブル
  UNION ALL
  SELECT [TO] F, [FROM] T FROM 友達申請テーブル
)
SELECT DISTINCT U.*, F2.T
FROM ユーザーテーブル U
LEFT JOIN W_F F1 ON F1.F = U.ID
LEFT JOIN W_F F2 ON F2.F = F1.T
WHERE U.ID <> F2.T

Answer 4

ちょっとヒントになれば。

テーブル設計を
友達になるように申請した人、友達リクエストを受けた人、ステータス(友達申請中、友達成立、友達拒否) で変えて考えてみては？

Comments

[Chandler_Bing]

つまりStatusを増やすということでししょうか。

非現実的的ですが、今回は申請があれば友達としていますので、
Statusはロジックに影響を与えないともうのですが。

[yambejp]

補足にも書きましたが、どういう場合が友達でどういう場合が友達じゃないのかはっきりさせてください

[Orlofsky]

難しいことを後回しにして砂上の楼閣になることは多々あります。

間違ったルールを作ってしまうことは世界中どこでもあることですが、ほどんどの国ではルールが現実にそぐわないとわかるとそのルールを修正します。一度決めた事はどんなに間違ったことであろうと、絶対に変えないのは日本の絶望的な特性です。

[shirokuma4690]

友達申請テーブルの
FROM TO
1 　　　3
のレコードは１と３が友達だからあるんですか？それとも友達の友達だからあるんですか？
ここが一番の混乱の元だと思います。

[Orlofsky]

この訂正は質問の行を削除するではなく、https://teratail.com/help/question-tips#questionTips3-7 の [打ち消し線] に修正してください。