Q&A
ありがとうございmす。
では3についてですが、それは私たちの操作側が
id userid followingid status
＊ 1 2 1
があれば、
id userid followingid status
＊ 2 1 1
という反対の値が起こらないようにすれば良いということですか。
現在SNSサイトの作成をしておりまして、その際のデーターベースの構造が私の求めている物になっているかご教授のほどよろしくお願いします。以下使用中のテーブルとデータの情報です。SNSサイトですのでツイートテーブルもありますが、ここでは関係がないので省略します。
SQL
1CREATE TABLE `user` ( 2 userid INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, 3 name varchar(100) NOT NULL, 4 pass varchar(100) NOT NULL 5) ; 6 7INSERT INTO `user` (`name`, `pass`) VALUES 8('Chandler', 'temp'), ('Ken', 'temp'), ('Ross', 'temp'), ('Joye', 'temp'), ('Tag', 'temp'), 9('Matt', 'temp'), ('Rachel', 'temp'), ('Bob', 'temp'), ('Lisa', 'temp'), ('Hanks', 'temp'), 10('Ben', 'temp'), ('Kazu', 'temp'), ('Tobby', 'temp'), ('David', 'temp'), ('Mike', 'temp'), 11('Gary', 'temp'), ('Ellen', 'temp'), ('Emilly', 'temp'), ('Carol', 'temp'), ('Frank', 'temp'); 12 13CREATE TABLE `follow` ( 14 id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, 15 userid int(11) NOT NULL, 16 followingid int(11) NOT NULL, 17 status int(11) NOT NULL 18) ; 19 20INSERT INTO `follow` (`userid`,`followingid`, `status`) VALUES 21(1,2,1),(1,3,1),(1,7,1),(1,8,1),(4,3,1), 22(4,8,1),(4,7,1),(6,10,1),(7,10,2),(19,20,2), 23(8,10,1),(2,3,1); 24
＊followのstatusの値はとなっております。
1申請許可済(友達)
2申請中
3申請却下
質問❶
id userid followingid status
＊ 1 2 2
のstatusが承認され1となった場合は、userid1とuserid２は友達ということになりますが、
この場合
id userid followingid status
＊ 2 1 1
と言うuseridが2の行は追加する必要はないと言う認識でよろしいでしょうか。
つまり
id userid followingid status
＊ 1 2 1
id userid followingid status
＊ 2 1 1
とする必要はないですよね。
質問❷
『友達かも』リストを作成します。条件は現在使用中ユーザーの友達の、友達を抽出したいのですが以下の文で合っていますか。使用中のユーザはuserid1とします。当然友達ですのでstausは1であるものに限ります。この場合以下のSQL文で合っています。
SQL
1CREATE TABLE `user` ( 2 userid INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, 3 name varchar(100) NOT NULL, 4 pass varchar(100) NOT NULL 5) ; 6 7INSERT INTO `user` (`name`, `pass`) VALUES 8('Chandler', 'temp'), ('Ken', 'temp'), ('Ross', 'temp'), ('Joye', 'temp'), ('Tag', 'temp'), 9('Matt', 'temp'), ('Rachel', 'temp'), ('Bob', 'temp'), ('Lisa', 'temp'), ('Hanks', 'temp'), 10('Ben', 'temp'), ('Kazu', 'temp'), ('Tobby', 'temp'), ('David', 'temp'), ('Mike', 'temp'), 11('Gary', 'temp'), ('Ellen', 'temp'), ('Emilly', 'temp'), ('Carol', 'temp'), ('Frank', 'temp'); 12 13CREATE TABLE `follow` ( 14 id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, 15 userid int(11) NOT NULL, 16 followingid int(11) NOT NULL, 17 status int(11) NOT NULL 18) ; 19 20INSERT INTO `follow` (`userid`,`followingid`, `status`) VALUES 21(1,2,1),(1,3,1),(1,7,1),(1,8,1),(4,3,1), 22(4,8,1),(4,7,1),(6,10,1),(7,10,2),(19,20,2), 23(8,10,1),(2,3,1); 24 25SELECT * FROM follow 26WHERE (userid IN(SELECT followingid FROM follow WHERE userid = 1) OR 27followingid IN(SELECT followingid FROM follow WHERE userid = 1)) 28AND userid NOT IN(1) 29ANd status = 1;
質問❸
質問❶の認識が正しい場合、
id userid followingid status
＊ 1 2 1
という行が存在した場合、
id userid followingid status
＊ 2 1 1
という行は存在してはいけないことになりますが、userid followingid をが逆にしたものは同じなので追加出来ないという制御は、カラムの型の様に設定する方法はないでしょうか。つまり追加int型にvarchar型を追加しようとした際にSQL文エラーが出る様な感じです。
【追記❶】
SQL
1INSERT INTO `follow` (`userid`,`followingidid`, `status`) 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);
の関係で使用中ユーザのidが1の場合に『友達かも』として抽出したい値は以下になります。
useridが4,10このつだけになります。
SQL
1SELECT * FROM user WHERE userid 2IN 3(SELECT userid FROM follow WHERE followingid = 1 and status = 1 4UNION ALL 5SELECT followingid FROM follow WHERE userid = 1 and status = 1);
このSQL文を実行すると、1と友達関係にあるuser2,3,7,8,12,13,14,15
が抽出されます。
次に、このユーザーidと友達関係にあるものを、順に見ていきます。 (ユーザー1を除く)
2と友達関係にあるのは3のみですが、3既に1と友達関係にあるので必要がありません。
次に3と友達関係にあるのは2ですがこれも同様に1と既に友達関係にあるためいりません。
次に7と友達関係にあるのは4,10です。これはどちらとも1とは友達関係にありませんので、『友達かも』として必要です。
これを全ての数字に割り当てたいのです。どうかご教授のほどよろしくお願いします。
回答2件
0
ロジック的にちゃんとやるなら
friendテーブルをつくり、friendIDを発行し1IDごとに2ユーザーIDを割り当てることです
各userID毎に申請、応諾、応諾済みなどstatusを付ければ良いと思います
Aさん（ID=1）、Bさん(ID=2)があったとして
Aさんが、Bさんを友達かもしれないとして申請するとき
friendテーブルに2レコード投入します
friendIDを仮に100として
| friendID | userID | status |
|---|---|---|
| 100 | 1 | 1 |
| 100 | 2 | 0 |
status:0=不明、1=申請
ここでBさんが拒否したときたとえば
status:99=拒否、98=被拒否
| friendID | userID | status |
|---|---|---|
| 100 | 1 | 98 |
| 100 | 2 | 99 |
となり、逆に応諾されたとき
status:2=応諾、3=被応諾
| friendID | userID | status |
|---|---|---|
| 100 | 1 | 3 |
| 100 | 2 | 2 |
とすればよいでしょう。
（応諾と被応諾は一緒でもよいかも）
その他statusとして、保留とかブロック（途中から）とか退会とかいろいろ必要になると思います。
いずれにしてもfollowテーブルのようにuseridとfollowingidのような
構造的な主従関係は付けないほうが良いと思います
（また仮に主従関係があるとしてもstatusはそれぞれに対して必要）
投稿2018/12/17 08:20
総合スコア114839
0
ベストアンサー
1．関係を明確にするために必要とする項目数としては不足は無いと思います。
ですが、ステータスとしては申請取り下げた後だとか退会した後などをどのように表現するかによって不足生じそうな気がします。
2.不足があるんじゃないかと
SQL
1select * from user 2where userid in ( 3 select followingid from follow 4 where userid in ( -- 友達が承認された友達 5 select followingid from follow where status=1 and userid=1 -- 承認された友達 6 union all 7 select userid from follow where status=1 and followingid=1 -- 承認した友達 8 ) 9 and status = 1 10 union all 11 select userid from follow 12 13 where followingid in ( -- 友達が承認した友達 14 select followingid from follow where status=1 and userid=1 -- 承認された友達 15 union all 16 select userid from follow where status=1 and followingid=1 -- 承認した友達 17 ) 18 and status = 1 19 union all 20 select followingid from follow where status=1 and userid=1 -- 承認された友達 21 union all 22 select userid from follow where status=1 and followingid=1 -- 承認した友達 23 ) 24 and userid NOT IN(1)
3.この構造で逆の組み合わせの一意制約は無理ですね、
MySQL 5.7で生成カラムを使ってCHECK制約もどきを実装する
追記
「友達かも」リストについては、事象を分けて考えれば、そんなに難しい事ではありません
SQL
1select * from user 2where userid in ( 3 select followingid from follow 4 where userid in ( -- 友達が承認された友達 5 select followingid from follow where status=1 and userid=1 -- 承認された友達 6 union all 7 select userid from follow where status=1 and followingid=1 -- 承認した友達 8 ) 9 and status = 1 10 union all 11 select userid from follow 12 13 where followingid in ( -- 友達が承認した友達 14 select followingid from follow where status=1 and userid=1 -- 承認された友達 15 union all 16 select userid from follow where status=1 and followingid=1 -- 承認した友達 17 ) 18 and status = 1 19 ) 20 and userid NOT IN(1) -- 起点のユーザー 21 and userid not in ( -- 友達 22 select followingid from follow where status=1 and userid=1 -- 承認された友達 23 union all 24 select userid from follow where status=1 and followingid=1 -- 承認した友達 25 )
ただ、unionしないと駄目な時点で、正規化からは外れています。
投稿2018/12/17 08:55
編集2018/12/18 07:42
総合スコア25195
追記しました。
※なんか同じ質問が上がっているようですけど？？？
中途半端に質問をしめて、同じ内容で聞かれるのって
結局「あなたの意見は参考にならなかったのでもう回答いらない」
という意思表示に見えますよね、なんか残念。
提示して頂いたSQL文がエラーになってしまいます。
自分で修正はしましたが、エラーが消えません。
エラー文
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 8
実行文
select * from user
where userid in (select followingid from follow
where userid in (select followingid from follow where status=1 and userid=1 union all select userid from follow where status=1 and followingid=1) and status = 1
union all
select userid from follow
where followingid in (select followingid from follow where status=1 and userid=1 union all select userid from follow where status=1 and followingid=1 and status = 1) and userid NOT IN(1);
項目名を誤っている箇所は修正しました。修正後ではこちらではエラーなりません。
もう一度確認してみて下さい。
お付き合いありがとうございます。頂いたSQL文は問題ありませんでした。ありがとうございます。
テーブル情報に値を追加いたしました。
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);
現在は、この様な値です。
質問が2つあります
❶
SELECT * FROM user 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);
このSQL文に問題はありますか。同じ値がきちんと返ってきます。
❷
質問文で述べた機能
『友達かも』『友達リクエスト』は現在のfollowテーブル
id userid followingid status
で運用できる気がします。 (退会等はとりあえず置いておきます)
そもそも、友達リクエストも
id userid followingid status
＊ 1 2 1
といのが存在する場合、2は1に対して友達申請が送れない様にSQL文をSELECTとしていくわけですから
id userid followingid status
＊ 2 1 1
この様に2が二重に友達申請をすることはありえない気がします。
そもそもstatusがすでに1の場合はHTMLで表示しない様にするわけですし。
どうでしょうか。現在作成中ですが特に問題は現在はですが、ありません。
１．「友達」の範囲ですね。質問には「友達の友達」とありましたけど？
２．制限付けるなら問題ないのではないでしょうか。
そもそも処理で制約付けるんなら、定義での制約など求める必要ないですね。
ありがとうございます。だいぶ見えてきました。
1.はい友達の友達でございます。
問題ありでしょうか。
データ上、「友達の友達」でしか抽出できないパターンがあった場合、ネストが不足すると抽出できないと思いますけど。
ありがとうございます。
常に
友達の友達で抽出するのではないのでしょうか。
理解不足ですがよろしくお願いします。また、お付き合い頂いておりますこと、感謝しております。
データまで詳しく見てないのですが、userid=1からみて「友達の友達」の関係となるfollowのデータはどれですか？
私の勘違いでした。私のだと単なる「友達」の抽出ですね。
「友達かも」の定義なのですが、
友達の友達であり、かつその友達が自分とすでに友達であってはいけないという定義です。
AB
BC
AC
という友達関係が成立していた場合、CはAからみて
B(友達)の友達に当たりますが、既にAとCは友達ですので、表示させません。
ご回答いただいたSQL文では
既にuserid1の友達である、2,3,14,15などが表示されています。
ご教授の程よろしくお願いいたします。
条件としているのは関係があるかどうかです。途中の組み合わせを求める必要があるのですか？
私の提示しているSQLはfollowは表示していませんが？
ありがとうございます。
頂いたのは、ユーザーの情報です。
先程、回答いたしました条件を満たすユーザー番号が必要になると考えております。
友達の友達から友達を除くという事ですか？
イメージ合わせのために、抽出したい結果のデータサンプルを質問に追記して下さい。
質問更新致しました。
【❶追記】としてあります。
いつの間に「友達かも」が主題になったのでしょう？
質問のSQLでは掠りもしていませんけど・・・
「友達かも」のみが必要なんでしょうか？
「友達かも」の条件は、「友達の友達」かつ「友達でない」ですよね？
左様でございます。友達の友達」かつ「友達でない」です。
お疲れ様です。抽出できたと思います。
試験的に行ったので、NOT IN句には実数（ユーザー1と友達のユーザーid）を入れております。
理論的にはあっておりますか。
SELECT * FROM user WHERE userid IN(
SELECT userid 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 (2,3,7,8,12,13,14,15)
UNION ALL
SELECT followingid 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 (2,3,7,8,12,13,14,15)
)
AND userid NOT IN (1);
動作確認済みでございます。
UNIONとINは効率悪いですが大丈夫ですか？
ちょっと記述間違えているようですし、条件としては別物でしょう。
実際に展開すると冗長な記述になりますし、statusとかもう無視してますし。
結果的に同じに過ぎないだけだと思います。
> yambeさん
union はallですからそれほどではないと思いますけど、not inはexistsにした方が良さそうですね。
一番効率良いのはCTEと再帰だと思いますけど、8.0以降ですしね。
statusとかもう無視してますし。
statusは無視してないと思うのですが。
よろしくお願いします。
提示したSQLと見比べてください。
お付き合いありがとうございます。感謝です。
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 = 1 UNION ALL SELECT followingid FROM follow WHERE userid = 1 and status = 1) 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 = 1 UNION ALL SELECT followingid FROM follow WHERE userid = 1 and status = 1) AND status =1
)
AND userid NOT IN (1);
こちらでどうですか
そのSQLの条件を文章で表してみてもらえます？
友達がフォローしている友達で、友達でないユーザー
と
友達にフォローされていない友達が、フォローしているユーザー
となるので、followでみると同じデータを指す事がある気がします。
ありがとうございます。
SQLの条件は
友達の友達」かつ「友達でない」。
で同じでございます。
現在のところは問題が生じてはいませんが、発生次第確認していきます。
すみません。追記で確認したいことがございます。
userid1がuserid4に友達申請を送った時、挿入は
userid 1、 followingid 4、status2 となる訳ですが、
この場合、リクエストの判定を下すのは、userid4のユーザーですので
4の画面にのみ、その通知を出します。
userid 1、 followingid 4の場合は１が4に申請をした。逆に
userid 4、 followingid1の場合はは４が1に申請をしたという風に運用していけば問題ないでしょうか。
逆の組合せはチェックで弾くと言ってませんでしたか？
整理できていない断片的な内容で質問して回答貰っても、余計に混乱するだけだと思いますよ。
少し腰を落ち着けて考えてみて下さい。
それから、項目の名称からは、useridに対してフォローしているのがfollowingidとした方が、直感的だと思えるのですが。
ありがとうございます。
逆の組み合わせに関しては、
PHPの表示非表示の段階でチェックができると考えております。
ですので、このテーブル設計で進めた場合、
私の直近の質問の考え方で大丈夫かどうか、少し意見を頂戴したく思いました。
一旦これにて質問を締めさせていただきます。有難うございました。
あなたの回答
tips
プレビュー
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
2018/12/17 08:25
2018/12/17 08:31
2018/12/17 08:32
2018/12/17 08:36
2018/12/17 09:04
2018/12/17 09:09
2018/12/17 09:26
2018/12/17 12:14 編集
2018/12/17 12:09
2018/12/17 12:13
2018/12/17 12:26
2018/12/18 07:27
2018/12/18 16:42
2018/12/19 07:28