お気に入りリストのテーブルをSQLで作成する際、取得元テーブルを条件分岐させたい

Question

###実現したいこと
action_tableというテーブルにfavoriteというアクションが記録されています。
サイト閲覧者をuser_id=1と仮定し、彼のfavoriteリストを作りたいです。

###発生している問題
favoriteリストは大体できたのですが、

【問題➀】follow_dateとfavorite_dateがnullとなってしまうこと。
【問題➁】「クジラの生活」の方だけfavorite_tagsが取得できないこと。

が問題となっています。

###該当のテーブル
長くてすみませんが、現在値として仮定したCREATE TABLEを掲載させて頂きます。

テーブルは6つですが、大きく次の3つに分類されます。

分類	テーブル名
『自社ユーザーが実行したアクション』	action_table
『自社ユーザー以外のコンテンツ』	content_tableとcontent_sub_table
『自社ユーザー』	own_user_tableとown_user_sub_table

これらからaction_tableのfavoriteリストを作るわけですが、
favoriteの対象がtarget_type='user'かつtarget_studio='own'のときには『自社ユーザー』のテーブルを参照し、それ以外は『自社ユーザー以外のコンテンツ』を参照する予定です。

以下CREATE TABLEです。

# action_table
# 自社ユーザーが実行したアクション
CREATE TABLE action_table
    (`action_id` int, `action_name` text, `action_date` text, `actor_id` int, `target_id` int, `target_type` text, `target_studio` text, `favorite_tags` text)
;
INSERT INTO action_table
    (`action_id`, `action_name`, `action_date`, `actor_id`, `target_id`, `target_type`, `target_studio`, `favorite_tags`)
VALUES
    (1, 'follow', '2020-01-01 01:01:01', 1, 3, 'user', 'own', ''),
    (2, 'favorite', '2020-02-02 01:02:02', 1, 4, 'documentaly', 'nhk', '"["ドキュメンタリー","クジラ"]"'),
    (3, 'favorite', '2020-03-03 03:03:03', 1, 3, 'user', 'own', '"["田中兄弟"]"'),
    (4, 'block', '2020-04-04 04:04:04', 4, 1, 'user', 'own', ''),
    (5, 'favorite', '2020-05-05 05:05:05', 1, 4, 'user', 'own', ''),
    (6, 'follow', '2020-06-06 06:06:06', 1, 2, 'user', 'own', '')
;

# content_table
# 自社ユーザー（own_user）以外のコンテンツの基本情報
CREATE TABLE content_table
    (`content_id` int, `content_type` text, `content_text` text)
;  
INSERT INTO content_table
    (`content_id`, `content_type`, `content_text`)
VALUES
    (1, 'comment', 'こんにちは'),
    (2, 'review', 'まぁまぁ'),
    (3, 'user', '山田花子です'),
    (4, 'documentaly', 'クジラの生活'),
    (5, 'news', 'ワールドビジネスニュース')
;

# content_sub_table
# 自社ユーザー（own_user）以外のコンテンツの補足情報
CREATE TABLE content_sub_table
    (`sub_id` int, `content_id` int, `sub_key` text, `sub_val` text)
;
INSERT INTO content_sub_table
    (`sub_id`, `content_id`, `sub_key`, `sub_val`)
VALUES
    (1, 1, 'studio_name', 'own'),
    (2, 1, 'content_url', 'own/comment/1'),
    (3, 2, 'studio_name', 'nhk'),
    (4, 2, 'content_url', 'nhk/review/2'),
    (5, 2, 'review_score', '5'),
    (6, 3, 'studio_name', 'fuji'),
    (7, 3, 'content_url', 'fuji/user/hanako'),
    (8, 3, 'unique_name', 'hanako'),
    (9, 3, 'nick_name', '山田花子'),
    (10, 4, 'studio_name', 'nhk'),
    (11, 4, 'content_url', 'nhk/documentaly/クジラの生活'),
    (12, 4, 'airtime', '06:00'),
    (13, 5, 'studio_name', 'asahi'),
    (14, 5, 'content_url', 'asahi/news/ワールドビジネスニュース'),
    (15, 5, 'airtime', '22:00')   
;

# own_user_table
# 自社ユーザーの基本情報
CREATE TABLE own_user_table
    (`user_id` int, `unique_name` text, `nick_name` text)
;  
INSERT INTO own_user_table
    (`user_id`, `unique_name`, `nick_name`)
VALUES
    (1, 'ichiro', '田中一郎'),
    (2, 'jiro', '田中二郎'),
    (3, 'saburo', '田中三郎'),
    (4, 'shiro', '田中四郎')
;

# own_user_sub_table
# 自社ユーザーの補足情報
CREATE TABLE own_user_sub_table
    (`sub_id` int, `user_id` int, `sub_key` text, `sub_val` text)
;
INSERT INTO own_user_sub_table
    (`sub_id`, `user_id`, `sub_key`, `sub_val`)
VALUES
    (1, 1, 'studio_name', 'own'),
    (2, 1, 'content_url', 'own/user/ichiro'),
    (3, 2, 'studio_name', 'own'),
    (4, 2, 'content_url', 'own/user/jiro'),
    (5, 3, 'studio_name', 'own'),
    (6, 3, 'content_url', 'own/user/saburo'),  
    (7, 4, 'studio_name', 'own'),
    (8, 4, 'content_url', 'own/user/shiro')    
;

###試したこと
この質問自体を大きく編集してしまいましたが、いろいろ試した経緯はぐちゃぐちゃとしているので、次のコードまでたどり着いたという現状だけを掲載させて頂きます。

実行サンプル

この実行サンプルの結果が下図で、緑が【問題➀】、赤が【問題➁】です。

# user_id=1のfavoriteリストを取得するのが目標
/*
発生している問題
【問題➀】`follow_date`と`favorite_date`が`null`となってしまうこと。
【問題➁】「クジラの生活」の方だけ`favorite_tags`が取得できないこと。
*/
select
  # 自社ユーザーかそうでないかで取得先テーブルを分岐
  case
      when content_type='user' and own_user_studio_name.sub_val='own'
      then own_user.user_id
      else null
  end as user_id,
  case
      when content_type='user' and own_user_studio_name.sub_val='own'
      then null
      else content.content_id
  end as content_id,  
  case
      when content_type='user' and own_user_studio_name.sub_val='own'
      then own_user.nick_name
      else content.content_text
  end as content_text,
  case
      when content_type='user' and own_user_studio_name.sub_val='own'
      then own_user.unique_name
      else null
  end as content_text2,
  case
      when content_type='user' and own_user_studio_name.sub_val='own'
      then 'user'
      else content_type
  end as content_type,   
  case
      when content_type='user' and own_user_studio_name.sub_val='own'
      then own_user_content_url.sub_val
      else content_content_url.sub_val
  end as content_url, 
  case
      when content_type='user' and own_user_studio_name.sub_val='own'
      then own_user_studio_name.sub_val
      else content_studio_name.sub_val
  end as studio_name,  
  
  # アクション情報を取得
  his_favorite.follow_date,
  his_favorite.favorite_date,
  action.favorite_tags
  
from (
        select favorite.target_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_table favorite
              left join action_table action
              on  favorite.actor_id=action.target_id 
              and action.actor_id=1 
        where favorite.actor_id=1 and favorite.action_name='favorite'
          and favorite.actor_id not in(
                select case when target_id=1 then actor_id else target_id end
                from action_table
                where target_type='user' and action_name='block' and 1 in (target_id, actor_id) 
              )
        group by favorite.target_id   
      ) his_favorite

      # アクション情報
      left join action_table action
      on  his_favorite.target_id=action.action_id

      # 自社ユーザー以外の情報
      left join content_table content
      on  his_favorite.target_id=content.content_id
      left join content_sub_table content_studio_name
      on  his_favorite.target_id=content_studio_name.content_id and content_studio_name.sub_key='studio_name'
      left join content_sub_table content_content_url
      on  his_favorite.target_id=content_content_url.content_id and content_content_url.sub_key='content_url'

      # 自社ユーザーの情報
      left join own_user_table own_user
      on  his_favorite.target_id=own_user.user_id      
      left join own_user_sub_table own_user_studio_name
      on  his_favorite.target_id=own_user_studio_name.user_id and own_user_studio_name.sub_key='studio_name'
      left join own_user_sub_table own_user_content_url
      on  his_favorite.target_id=own_user_content_url.user_id and own_user_content_url.sub_key='content_url'
      left join own_user_sub_table own_user_content_text
      on  his_favorite.target_id=own_user_content_text.user_id and own_user_content_text.sub_key='content_text'

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

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

SQL初心者がネットの見本などを参考に組み合わせたコードにつき、意味不明な部分などがあるかもしれませんがご容赦ください。

宜しくお願い致します。

Comments

[Orlofsky]

CREATE TABLEやINSERTの提示を求めても無視する質問者が多い中、最初からCREATE TABLEやINSERTをきちんと提示できるのは良いことです。
ですが、質問内容があり過ぎて何を質問したいのか読み解く負担が多過ぎです。
質問したい内容に絞ったテーブルや列で提示されては？

[ikatako]

まったく仰る通りかと思います。お恥ずかしい限りです。

あれからずっと取り組んでいるのですがなんとか条件分岐の方は（たぶん）できまして、残るはaction_tableのデータの問題だけになりました。

改めてその点だけに絞って質問を編集させて頂きますので、少々お時間頂戴できましたら幸いです。いつもご指摘ありがとうございます。

[ikatako]

今しがた質問を編集させて頂きました。お手すきの折にご回答いただけましたら幸甚に存じます。

Answer 1

his_favoriteの部分は以前回答をしたものをそのまま流用されているようですが、その際のテーブルとは仕様の違うテーブルですので、適宜変更しなければ求める結果にはなりません。

前回の回答の際「理解できていないままで、適用してしまうと後が大変ですよ。」とコメントしましたが、まさにこの事です。

追記

あてずっぽうですけど、メイン部分のSQLは多分こんな感じ

select target_id
     , max(
          case when action_name='follow' then action_date end
      ) as follow_date
     , max(
            case when action_name='favorite' then action_date end
         ) as favorite_date
from  action_table user
where target_type='user' and action_name in ('favorite', 'follow') 
  and exists(
        select 1 from action_table 
        where target_id =user.target_id and action_name='favorite'
      )
  and actor_id=1
group by target_id   

Comments

[ikatako]

本当にそうですね、全て理解できていたら悩まずに済みましたね。

[ikatako]

ありがとうございます。試してみました。
http://sqlfiddle.com/#!9/0d5f40/2

favuoriteリストがほしいので、3・4のリストを作りたいのですが、2・3のリストになってしまいましたので、どこかで連結させるデータが混ざってしまっているようです。ですがご面倒でしょうから、続きはなんとか自分でひも解いて参ります。

[ikatako]

その後、なんとか取り組んでいる現状が下記です。
http://sqlfiddle.com/#!9/0d5f40/1

maxがある理由がわからなかったので削除し、その他あれやこれやと少し改造（改悪？）しておりましたら、なんとか【問題➀】を解決して日付までは入れることができたみたいです。

あとは【問題➁】のfavorite_tagsを入れるだけの状態なので、あと一晩もあればできるかもしれません。できたらぜひご報告させてください。

[ikatako]

あぁ、自分が書いた05:16の方ですが、日付の問題はできたとか言ってしまいましたが、1はクジラの生活をfollowしていないのにfollow_dateが入っていましたね。SQL本当難しいです。

[sazi]

> maxがある理由がわからなかった
group byで指定している項目以外は、集計(max()とか、count()とか)でないと通常はエラーです。
mysqlでは設定により、忖度しますがその値は不定（保証されない）です。

[sazi]

MAX()を入れない状態で、blockを除外すると、
select * from action_table
where target_type='user' and action_name in ('favorite', 'follow') and actor_id=1
となります。
ここから3、４だけにする条件（２を除外する条件）は何ですか？

[sazi]

2を除外する条件として、いいねをしている人という条件で回答のSQLを修正しました

[ikatako]

返信遅くなりまして申し訳ございません。

問題を１つに絞るところまで来ました。そしてその問題解決のために別質問を立てたりもしたのですが、どうしてもわかりません。

こちらです。
http://sqlfiddle.com/#!9/0d5f40/150
目標と問題を冒頭にコメントアウトしてある通りです。

その問題が生じる原因は、63~65行目の理解にあると思っています。

この理解がどう違い、いかに修正すべきかアドバイスを頂戴できませんでしょうか。

尚、70行目のwhereが別質問の部分にあたりますが、いろいろアレンジしようとしても本件に適用できませんでした。（せっかく別質問のご回答をくださったのに活かせず本当にすみません。）

[ikatako]

別質問の部分（本質問のご回答の追記の部分）ですが、それをすると日付の問題は解消されるのですが、favorite_tagsが入らなくなってしまうという問題が生じてしまいます。

http://sqlfiddle.com/#!9/0d5f40/149
クジラの生活の方のタグが空になってしまっていると思います。

つまり、22:01の方だと日付がおかしいけどタグは入り、ご回答の方だと日付は入るけどタグかおかしい、という状態です。

[ikatako]

すみません、22:01の方ですが、クジラの生活のfavorite_dateは「02-02」のはずが「05-05」が入っており、つまりタグだけでなく日付も違っていました。

[sazi]

同じテーブルのデータで考えているから、整理がつかなくなっているように感じます。
同じテーブルでも目的が違うデータが存在しているんですから、それらを単独で抽出して一つのテーブルとして考えると理解が進むのではないかと思います。

[ikatako]

これだけ混乱してしまった質問なのでご回答は大変困難かと思います。どうぞお気になさらず。しばらく自力で取り組みます。

[ikatako]

コメント入れ違いでした。たしかにまず単独での抽出からですね。どうもありがとうございます。せっかくの頭脳を混乱した質問に使わせてしまい、本当に申し訳ございません。

[sazi]

ん？22:01はこの回答の内容ではありませんよ？

[ikatako]

はい、22:01で「日付は問題なく入り、あとはタグの問題だけになりました」と書きましたが、やはり日付もタグも問題があったというご報告でした。

ただ23:31のご回答の方もこの日付の問題は同様なので、まずはテーブルの整理からとアドバイスを頂いたものと存じます。

この日付の問題は、user_idとcontent_idが同じ4であることで混ざってしまっているのだと思いますので、そのあたりを整理して取り組んでみたいと思います。

[ikatako]

かなりできました。同じidの問題は、一番最初のselectの条件分岐を

when content_type='user' and
own_user_studio_name.sub_val='own'

ではなく

when his_favorite.target_type='user' and his_favorite.target_studio='own'

とすることで解決致しました↓
http://sqlfiddle.com/#!9/c8d863/10

あとは14行目と78行目にあるように、block関係を除外することができない状態です。

[ikatako]

そこでblock関係を除外するために、従来はtarget_idだけだった部分を、target_typeとtarget_studioを追加すればできそうに思いまして、下記のように変更したのですができませんでした。

下記はどのようなselectをすれば除外できそうか、もし何かヒントを頂戴できましたらと思い、再三恐縮ながらコメントさせて頂きました。

/*
and favorite.actor_id not in(
select case when target_id=1 then actor_id else target_id end
from action_table
where target_type='user' and target_studio='own' and action_name='block' and 1 in (target_id, actor_id)
)
*/

↓変更

and favorite.actor_id not in(
select case when target_id=1 then actor_id else target_id end
, target_studio
, target_type
from action_table
where target_type='user' and target_studio='own' and action_name='block' and 1 in (target_id, actor_id)
)

[sazi]

and not exists( # 彼とblock関係にあるownのuserを除外
select 1 from action_table
where action_name='block' and target_id=favorite.target_id
)

[ikatako]

夜分に迅速なご回答感謝致します。
そちらもexistでしたか。使い分けが慣れないとなかなか思いつきません。

見事に5を消せました。
http://sqlfiddle.com/#!9/c8d863/16

僭越ながら一点だけ、消したいのは「山田五郎」で、「ワールドビジネスニュース」は消したくない、というところがあったので

where action_name='block' and target_id=favorite.target_id

の部分を

where action_name='block' and target_id=favorite.target_id
and favorite.target_studio='own'

とさせて頂きました。

つたない質問、混乱したコメントに対してたびたびご返信頂きまして心より感謝申し上げます。どうもありがとうございました。

[sazi]

and not exists( # 彼とblock関係にあるownのuserを除外
select 1 from action_table
where target_type='user' and target_studio='own' and action_name='block'
and target_id=favorite.target_id and favorite.target_type='user'
)

[ikatako]

改めて正確なコードをどうもありがとうございます。今回もベストアンサーにさせて頂きました。

今回教えて頂いたexistsの使い方はしばらく何度も練習しないと理解できなそうですが、前回から続く質問内容の方はやっと７割くらいは理解できたと思います。勉強できたという手ごたえを大変うれしく感じています。

引き続き頑張って参りますので、またお目にかかる機会がございましたらそのときもどうぞ宜しくお願い申し上げます。

ーー
自分用メモ
上記not existsに「するかされるか」のためにorを加え、あと不要な部分を削除して以下が完成形
http://sqlfiddle.com/#!9/ee25c9/2

[sazi]

今はサンプルで行っていますから気にならないでしょうけど、インデックスをきっちり設定しておかないと件数が多くなるとレスポンスが悪くなると思われますので、サンプリング件数を増やし実行計画を行ってチューニングされておくことをお勧めします。

[ikatako]

なるほどこれは…仰る通り、インデックスで大変悩みますね。

action_tableの中で、user_idとaction_nameに複合インデックスを貼っておけば、user_id=1さんのfavoriteリストの取得は早くなる。
しかしユーザー数が増えれば莫大なインデックスになってしまう。

こればっかりは様々なインデックスを試しまくるしかありませんね。早めに知っておいてよかったです。アドバイスありがとうございます。

Answer 2

やはり、質問を絞り込んでいないので、

【問題➀】follow_dateとfavorite_dateがnullとなってしまうこと。

こちらだけ。

follow_dateとfavorite_dateを取得する部分だけ抜き出して実行すると、

        select favorite.target_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_table favorite
              left join action_table action
              on  favorite.actor_id=action.target_id 
              and action.actor_id=1 
        where favorite.actor_id=1 and favorite.action_name='favorite'
          and favorite.actor_id not in(
                select case when target_id=1 then actor_id else target_id end
                from action_table
                where target_type='user' and action_name='block' and 1 in (target_id, actor_id) 
              )
        group by favorite.target_id
;

follow_date, favorite_date 共にnullになってしまいます。
なぜ、こんなSQLを書いているのかわからないので、わたしにはSQLが悪いのか、データが悪いのかわかりません。

さらに絞り込んで

                select case when target_id=1 then actor_id else target_id end
                from action_table
                where target_type='user' and action_name='block' and 1 in (target_id, actor_id) 
;

で実行すると、

case when target_id=1 then actor_id else target_id end

とエラーのようです。http://sqlfiddle.com/ のバグでしょうか？MariaDBで実際にSQLを実行できる環境を用意して実行されては？

テーブル設計で日時情報は通常DATE型やDATETIME型で設計します。
日付型と時刻型(DATE, TIME, DATETIME, TIMESTAMP, YEAR)

テーブル設計やこれまでのSELECTはkatakoさんが最初から作ったものでしょうか？
他の人が作ったものなら、なぜこんな作りになっているのか確認した方がいいです。

テーブル設計の入門で習うことですが、SQLがシンプルにパフォーマンスが良くなるように、ひとつの列には一つの意味のデータしか持たせてはならない、という基本があります。
それを守れていないから無意味にSQLが複雑になっています。

見易さにもちょっと気を使うとコメントが付きやすくなります。

INSERT ...
VALUES
    (1, 'follow'  , '2020-01-01 01:01:01', 1, 3, 'user'       , 'own', ''),
    (2, 'favorite', '2020-02-02 01:02:02', 1, 4, 'documentaly', 'nhk', '"["ドキュメンタリー","クジラ"]"'),
    (3, 'favorite', '2020-03-03 03:03:03', 1, 3, 'user'       , 'own', '"["田中兄弟"]"'),
    (4, 'block'   , '2020-04-04 04:04:04', 4, 1, 'user'       , 'own', ''),
    (5, 'favorite', '2020-05-05 05:05:05', 1, 4, 'user'       , 'own', ''),
    (6, 'follow'  , '2020-06-06 06:06:06', 1, 2, 'user'       , 'own', '')
;

             , 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

                  ↓

             , 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

なお、バックスラッシュ ` で囲むなら、列名だけでなく、テーブル名もCREATE TABLE, INSERT, SELECT などすべてのSQLのテーブル名、列名を囲みます。

中途半端ですが。

Comments

[ikatako]

やりたいことをまとめて質問するよりも、selectごとに実行して原因を見つけてから質問すべきだったということですね。
見やすいテーブル作りについてもありがとうございます。