SQLテーブルの連結について

Question

前提・実現したいこと

こちらの画像に質問をまとめさせていただきました。

最終的な目標としては
➀main_datas
➁sub_datas
➂action_datas
という３つのテーブルから「スレッド20のフォロワーリスト」というHTMLを出力することです。

そのために
➃thread_followers
が必要と考えているのですが、これを作成するSQLで躓いています。

発生している問題

➀➁➂から➃が作れないことが問題です。

該当のテーブル（➀➁➂）

冒頭の画像の値を入れたテーブルをご用意いたしました。
SQL fiddle

※Orlofsky様からご指摘を受け、上記SQL fiddleのコードを下記に掲載させて頂きます。

# main_datas の作成
CREATE TABLE main_datas
    (`main_id` int, `unique_name` varchar(5), `nick_name` varchar(2))
;  
INSERT INTO main_datas
    (`main_id`, `unique_name`, `nick_name`)
VALUES
    (1, 'one', '一郎'),
    (2, 'two', '次郎'),
    (3, 'three', '三郎')
;

# sub_datas の作成
CREATE TABLE sub_datas
    (`sub_id` int, `user_id` int, `sub_key` varchar(7), `sub_val` varchar(3))
;
INSERT INTO sub_datas
    (`sub_id`, `user_id`, `sub_key`, `sub_val`)
VALUES
    (1, 1, 'age', '10歳'),
    (2, 1, 'address', '東京'),
    (3, 2, 'age', '20歳'),
    (4, 2, 'address', '神奈川'),
    (5, 3, 'age', '30歳'),
    (6, 3, 'address', '千葉')
;

# action_datas の作成
CREATE TABLE action_datas
    (`action_id` int, `action_name` varchar(8), `action_date` varchar(4), `actor_id` int, `target_id` int, `target_type` varchar(6))
;
INSERT INTO action_datas
    (`action_id`, `action_name`, `action_date`, `actor_id`, `target_id`, `target_type`)
VALUES
    (1, 'follow', '1月1日', 1, 2, 'user'),
    (2, 'follow', '2月2日', 1, 20, 'thread'),
    (3, 'follow', '3月3日', 2, 20, 'thread'),
    (4, 'favorite', '4月4日', 3, 2, 'user'),
    (5, 'follow', '5月5日', 3, 20, 'thread'),
    (6, 'favorite', '6月6日', 1, 3, 'user')
;

試したこと

まずは➃のテーブルを作る原型となる➀➁の連結はできました。

あとは「#ここにfollow_date」と「#ここにfavorite_date」を追加できれば➃のテーブルが出来そうです。

# ➃のテーブル
SELECT
m.main_id,
m.unique_name AS unique_name,
m.nick_name AS nick_name,
(select sub_val from sub_datas where user_id = m.main_id and sub_key = 'age' ) as age,
(select sub_val from sub_datas where user_id = m.main_id and sub_key = 'address' ) as address
#ここにfollow_date
#ここにfavorite_date
FROM main_datas m;

問題の部分ですが、下記のようにサブクエリの中で「# サブクエリを使いthread_id=20をfollowした人を取得」として、上の「#ここにfollow_date」と「#ここにfavorite_date」に入れてみたのですが、このあたりかなりわかっておらず、お目汚しかとは存じますがこのレベルです。

# ➃のテーブル
SELECT
m.main_id,
m.unique_name AS unique_name,
m.nick_name AS nick_name,
(select sub_val from sub_datas where user_id = m.main_id and sub_key = 'age' ) as age,
(select sub_val from sub_datas where user_id = m.main_id and sub_key = 'address' ) as address,
(select action_date from followers where actor_id = u.ID ) as follow_date,
(select action_date from followers where actor_id = u.ID ) as favorite_date,
FROM main_datas m;

# サブクエリを使いthread_id=20をfollowした人を取得
WHERE
m.main_id IN
(SELECT 
actor_id, action_date,
FROM action_datas
WHERE action_name = "follow"
AND target_type = "thread"
AND target_id = 20
) AS followers

そして上の「# サブクエリを使いthread_id=20をfollowした人を取得」の部分がよくわからないので次は下記のように「# ビューを作りthread_id=20をfollowした人を取得」という感じで試してみたのですが、やはり理解が追い付ておらずできませんでした。

# ➃のテーブル
# 同上

# ビューを作りthread_id=20をfollowした人を取得
CREATE VIEW followers actor_id, action_time,
AS SELECT 
actor_id, action_time,
FROM action_datas
WHERE action_name = "follow"
AND target_type = "thread"
AND target_id = 20

サブクエリなのかビューなのか、また別のものが相応しいのか、そういった点がそもそもわからないという状況であれこれ試したわけですがどうしてもできず、今回質問サイトを利用させて頂くことになりました。

どのようにすれば➃のテーブルが作成できるでしょうか。

またそもそも、「スレッド20のフォロワーリスト」というHTMLを出力するためには➃のテーブルよりももっと良いものがございますでしょうか。

そのあたりにつきましてご回答いただけましたら幸甚に存じます。
どうぞ宜しくお願い致します。

補足情報（FW/ツールのバージョンなど）

phpMyAdmin（4.8.5）で、MySQL（10.0.33-MariaDB）を使っています。

Comments

[Orlofsky]

>冒頭の画像の値を入れたテーブルをご用意いたしました。
https://teratail.com/help/question-tips#questionTips3-7 の [コード] に修正してください。

掲示板の中でまとめられる内容は掲示板の中に提示してください。

[ikatako]

失礼いたしました。「※Orlofsky様からご指摘を受け、上記SQL fiddleのコードを下記に掲載させて頂きます。」と、質問本文に掲載させて頂きました。ご指摘ありがとうございます。以後気を付けます。

[Orlofsky]

テーブル設計の掟がいろいろあります。
今回の設計で気になるのが、一つの列にひとつの意味(用途)しか持たせてはならない、って決まりがあります。これを守らないとSQLがとっても複雑になったり、極端にパフォーマンスが落ちます。UNION ALL を多用しなければならない場合もデータが増えるとパフォーマンスが落ちる原因になります。
テーブル数が多いシステムでしたら、それなりのスキルのある人を助っ人に呼んだほうが良いです。

[ikatako]

>今回の設計で気になるのが、一つの列にひとつの意味(用途)しか持たせてはならない、って決まりがあります。
と仰いますと、どのテーブルのことでしょうか？基本的には一つのセルに一つの値という構造にすべきだと学んだのですが、まずかったでしょうか。
それが気になるということは、より良い構造のお考えがございましたらご意見賜れましたら幸いです。

[sazi]

アンチパターンである EAVを採用しているからだと思いますよ。
https://www.ketancho.net/entry/2018/03/07/080000
sub_datas が該当しています。

[ikatako]

なるほど、ありがとうございます。こういったマニュアルに全く目を通しておらず、本一冊片手に持っているだけの状態でした。楽だと思っていたのですがデメリットも多いんですね。

[sazi]

私としては、EAVもナイーブツリーも多用しているので、何とも言えませんけどね。
尤もアンチパターンに挙げられるような内容を素で使っている訳ではありませんが。

Answer 1

main_datasやsub_datasは抽出対象が確定してから取得するようにした方が効率が良いし、混乱しなくて済みます。
先ずは、action_datasからthread_followerとして必要なデータを取り出しすように考えましょう。
それからselectで取り出せるものを敢えてテーブルデータとして格納する必要はありません。
逆に、元データに更新が掛かった場合に更新が必要になりますので。

select  main.*
      , age.sub_val as age, address.sub_val as address
      , thread_follower.follow_date, thread_follower.favorite_date
from (
        select follow.actor_id
             , max(
                  case when action.action_name='follow' then action.action_date end
               ) as follow_date
             , max(
                  case when action.action_name='favorite' then action.action_date end
               ) as favorite_date
        from  action_datas follow
              left join action_datas action
              on  follow.actor_id=action.target_id 
              and action.actor_id=1 and action.target_type='user'
        where follow.target_type='thread' and follow.target_id=20
        group by follow.actor_id      
      ) thread_follower
      left join main_datas main
      on  thread_follower.actor_id=main.main_id
      left join sub_datas age
      on  main.main_id=age.user_id and age.sub_key='age'
      left join sub_datas address
      on  main.main_id=address.user_id and address.sub_key='address'

Comments

[ikatako]

SQL総合1位の方からのご回答とは大変光栄です。どうもありがとうございます。

ほぼできている印象でしたが、「スレッド20のフォロワーリスト」を表示したい考えでおります。
ご回答のSQLですと、スレッド20のフォロワー以外も➃行にリストアップされてしまうようでした。
見にくい図説ですみません。

[sazi]

「スレッド20のフォロワーリスト」というのが良く分かりません
サンプルのthread_followerの内容がそれだと思っているんですが、違うのでしょうか？
その内容に合わせて、敢えてID=1,3を取得されるようにしたのですが・・・

[ikatako]

わかりにくくて申し訳ございません。

>サンプルのthread_followerの内容がそれだと思っているんですが、違うのでしょうか？

まずこちらの解釈は違いません。あっています。

ただし、thread_followerに一郎・次郎・三郎という三人のレコードが並んでいるのは、三人とも「スレッド20のフォロワー」だからです。
（action_datasで三人ともthread_id=20をfollowしているからです。）

なのでもし、一郎が「スレッド20のフォロワー」ではない場合（図のF-23セルの値が20じゃなかった場合）、thread_followerのレコードは次郎・三郎という二人しか並ばないことにしたいのです。

[sazi]

変更しました。考え方は変わっていません。
フォローもいいねもしている場合を考慮してgroup byしていますが、どちらかしかないなら外してください

[sazi]

> 一郎が「スレッド20のフォロワー」ではない場合
抽出する際には、誰かを基準にしないと駄目じゃないですか？
ID=1を基準にしないで、action_datasのID=4はどのようにして除外しますか？

[ikatako]

いつも迅速なご返信を頂き感謝いたします。完璧に理想としていたものができました。本当にどうもありがとうございます！

仰るようにフォローもいいねもしている場合があるのでこのままでいきたいと思います。その点につきましても情報不足をお詫びし、ご配慮に感謝申し上げます。

[ikatako]

>抽出する際には、誰かを基準にしないと駄目じゃないですか？
はい、今回の質問では閲覧者を一郎と仮定したので、一郎がフォローしているかいいねしているか、を基準として下さったご回答になっていると思います。

なので一郎でなく次郎が閲覧者だと仮定した場合は、ご回答の15行目を
and action.actor_id=1
↓変更
and action.actor_id=2
としようと思っています。

>ID=1を基準にしないで、action_datasのID=4はどのようにして除外しますか？
すみません、こちらは理解が及びませんでした。「action_datasのID=4の除外」とはなんのことでしょうか？

[sazi]

> and action.actor_id=1
これが、ID=1を基準にしているという事です。
on 句でのaction.actor_id=1を外して、where 条件でfollow.actor_id=1とすると違った結果になります。

[ikatako]

>これが、ID=1を基準にしているという事です。
何か問題があるのでしょうか？そこのところは閲覧者のIDをPHPの変数として入れて
and action.actor_id=$current_user_id
のようにする予定なので、閲覧者が1なら1が基準になりますし、閲覧者が2なら2が基準になるという風になって、問題がないよう作ってくださったのだと思っているのですが。

>on 句でのaction.actor_id=1を外して、where 条件でfollow.actor_id=1とすると違った結果になります。
それを外すケースが思いつかないですし、where に変更をするケースも思いつかないレベルです。どのようなケースを想定なさってのお話ですか？

夜分にたびたび申し訳ございません。またお時間ございましたらご返信お待ちしております。

[sazi]

説明をしているだけで、変更しないと駄目なケースがあるという事では無いです。
action_id=4は２が３にいいねをしたものですから、除外するためにon 句でaction.actor_id=1の指定をしています。
その条件が結果的に抽出条件にもなっているという事です。

[ikatako]

コードを理解できていないのでせっかくのご説明にも追いつかず、大変申し訳ない思いです。ひとまず駄目そうなわけではないということで、安心致しました。初心者へのご説明は大変でしたでしょうに、丁寧に最後までありがとうございました。

[sazi]

サンプルデータの範囲でしか検証していないわけですから、理解できていないままで、適用してしまうと後が大変ですよ。
自助努力はされて下さい。

[ikatako]

なんとか読み解く努力はしているのですがやはり完全には理解できず、not inの実装をアレンジしようとして戸惑っているところです汗
相当勉強になりましたので、引き続き頑張って参ります。ありがとうございます。

[sazi]

では、質問はクローズしておいて下さいね。

[ikatako]

これは大変申し訳ございません！昨夜「ベストアンサーにする」はクリックしたつもりでおりました。以後気を付けます。