質問編集履歴

リンクと画像を微調整

2020/02/05 12:02

投稿

ikatako

スコア270

test CHANGED Viewed

File without changes

test CHANGED Viewed

@@ -242,13 +242,13 @@
-[実行サンプル](http://sqlfiddle.com/#!9/2f19d3/46)
+[実行サンプル](http://sqlfiddle.com/#!9/e2353/3)
 この実行サンプルの結果が下図で、緑が【問題➀】、赤が【問題➁】です。
-![イメージ説明](e586e2a6b4e7d4b73bd7f6e3224e94cf.png)
+![イメージ説明](b10748172efa6e0cab1b7c7c3b32899d.png)

試しているうちに解決したことなどがあるので内容を大きく修正致しました

2020/02/05 12:02

投稿

ikatako

スコア270

test CHANGED Viewed

File without changes

test CHANGED Viewed

@@ -1,52 +1,60 @@
 ###実現したいこと
-後述するように、下図の黄色いテーブルがある状況です。
+`action_table`というテーブルに`favorite`というアクションが記録されています。
-図にあるように取得し、ピンク色のテーブルのような「favoriteリスト」を作りたいです。
+サイト閲覧者を`user_id=1`と仮定し、彼の`favorite`リストを作りたいです。
-![イメージ説明](3c5413eedc1903c017b9949937829461.jpeg)
-特にネックになっているのは
-content_type=user && studio_name=own（自社ユーザー）の場合
-→`own_user_table`を元にしてピンク色のテーブルを作る。
-それ以外（自社ユーザー以外のコンテンツ）の場合
-→`content_table`を元にしてピンク色のテーブルを作る。
-という条件分岐です。
 ###発生している問題
-やはり上の条件分岐がうまくいかないという問題と、
+`favorite`リストは大体できたのですが、
-あとは、follow_date、favorite_date、favorite_tagsなど`action_table`に属する値が取得できないことが問題です。
+【問題➀】`follow_date`と`favorite_date`が`null`となってしまうこと。
+【問題➁】「クジラの生活」の方だけ`favorite_tags`が取得できないこと。
+が問題となっています。
 ###該当のテーブル
-こちらが上図の黄色いテーブルになります。
-長くてすみませんが、SQL fiddleを後述してあるのでよろしければそちらでご覧くさい。
+長くてすみませんが、現在値として仮定した`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`です。
 ```SQL
@@ -66,8 +74,6 @@
 VALUES
-    # サイト閲覧者としてuser_id=1を与え、彼のfavoriteリストを作りたい
     (1, 'follow', '2020-01-01 01:01:01', 1, 3, 'user', 'own', ''),
     (2, 'favorite', '2020-02-02 01:02:02', 1, 4, 'documentaly', 'nhk', '"["ドキュメンタリー","クジラ"]"'),
@@ -100,10 +106,6 @@
 VALUES
-    # commentやnewsなど様々なcontent_typeが混在している
-    # content_typeとcontent_textが欲しい
     (1, 'comment', 'こんにちは'),
     (2, 'review', 'まぁまぁ'),
@@ -134,10 +136,6 @@
 VALUES
-    # content_typeに応じてこのサブ情報がそれぞれ異なるが
-    # studio_nameとcontent_urlを取得したい
     (1, 1, 'studio_name', 'own'),
     (2, 1, 'content_url', 'own/comment/1'),
@@ -188,10 +186,6 @@
 VALUES
-    # nick_nameをcontent_textとして取得して
-    # unique_nameをcontent_text2として取得したい
     (1, 'ichiro', '田中一郎'),
     (2, 'jiro', '田中二郎'),
@@ -220,8 +214,6 @@
 VALUES
-    # studio_nameとcontent_urlを取得したい
     (1, 1, 'studio_name', 'own'),
     (2, 1, 'content_url', 'own/user/ichiro'),
@@ -244,37 +236,127 @@
 ```
-###試したこと 1/3
+###試したこと
-まず`own_user_table`か`content_table`かという条件分岐をいったん無視して、`own_user_table`だけを対象としてfavoriteリストを作ろうとしたのがこちらです。
+この質問自体を大きく編集してしまいましたが、いろいろ試した経緯はぐちゃぐちゃとしているので、次のコードまでたどり着いたという現状だけを掲載させて頂きます。
+[実行サンプル](http://sqlfiddle.com/#!9/2f19d3/46)
+この実行サンプルの結果が下図で、緑が【問題➀】、赤が【問題➁】です。
+![イメージ説明](e586e2a6b4e7d4b73bd7f6e3224e94cf.png)
 ```SQL
+# 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'
-select  own_user.user_id as mix_id
+      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
-      , own_user.nick_name as content_text
+      else content.content_text
+  end as content_text,
+  case
+      when content_type='user' and own_user_studio_name.sub_val='own'
-      , own_user.unique_name as content_text2
+      then own_user.unique_name
+      else null
-      , content_url.sub_val as content_url
+  end as content_text2,
+  case
+      when content_type='user' and own_user_studio_name.sub_val='own'
+      then 'user'
+      else content_type
-      , content_type as 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
-      , studio_name.sub_val as studio_name
+  end as studio_name,
+  # アクション情報を取得
-      , his_favorite.follow_date
+  his_favorite.follow_date,
-      , his_favorite.favorite_date
+  his_favorite.favorite_date,
-      , his_favorite.favorite_tags
+  action.favorite_tags
 from (
-        select favorite.*
+        select favorite.target_id
              , max(
@@ -294,9 +376,9 @@
               on  favorite.actor_id=action.target_id
-              and action.actor_id=1
+              and action.actor_id=1
-        where favorite.actor_id=1
+        where favorite.actor_id=1 and favorite.action_name='favorite'
           and favorite.actor_id not in(
@@ -308,174 +390,68 @@
               )
-        group by favorite.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'
-      # favorite情報
+      # 自社ユーザーの情報
       left join own_user_table own_user
-      on  his_favorite.target_id=own_user.user_id
+      on  his_favorite.target_id=own_user.user_id
-      # 自社ユーザー以外の情報
-      left join content_table content
-      on  his_favorite.target_id=content.content_id
-      # 自社ユーザー情報
-      left join own_user_sub_table studio_name
+      left join own_user_sub_table own_user_studio_name
-      on  his_favorite.target_id=studio_name.user_id and studio_name.sub_key='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 content_url
+      left join own_user_sub_table own_user_content_url
-      on  his_favorite.target_id=content_url.user_id and content_url.sub_key='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 content_text
+      left join own_user_sub_table own_user_content_text
-      on  his_favorite.target_id=content_text.user_id and content_text.sub_key='content_text'
+      on  his_favorite.target_id=own_user_content_text.user_id and own_user_content_text.sub_key='content_text'
 ```
-これを SQL fiddle で試したのが下図でして、赤枠の部分の
-`action_table`に属する値の取得がうまくいきません。
-[SQL fiddle で試す](http://sqlfiddle.com/#!9/248ea/5)
-![イメージ説明](71b01b1b5898676349d23337dc47f446.png)
-###試したこと 2/3
-そして上の問題は無視して条件分岐を実装しようと試みたのが下記コードになります。
-`case when then end`で「自社ユーザーの場合」という条件分岐を書けばできると考えたのですが。
-```SQL
-select
-    # 自社ユーザーの場合はown_user_tableから取得
-    case when content_type='user' and studio_name='own' then
-        own_user.user_id as mix_id
-      , own_user.nick_name as content_text
-      , own_user.unique_name as content_text2
-      , content_url.sub_val as content_url
-      , content_type as content_type,
-      , studio_name.sub_val as studio_name
-    # 自社ユーザー以外の場合はcontent_tableから取得
-    else
-        content.content_id as mix_id
-      , content.content_text as content_text
-      , '' as content_text2
-      , content_url.sub_val as content_url
-      , content_type as content_type,
-      , studio_name.sub_val as studio_name
-    end
-    # いずれの場合もaction_tableから以下を取得
-      , his_favorite.follow_date
-      , his_favorite.favorite_date
-      , his_favorite.favorite_tags
-from (
-    /* 同上 */
-```
-###試したこと 3/3
-`case when then end`以外の条件分岐として`if end if`があるようで下記のように試しましたがこれもできずで、この時点で質問させて頂くことに致しました。
-長いコードで大変申し訳ございませんが、どうぞ宜しくお願い致します
-```SQL
-select
-    # 自社ユーザーの場合はown_user_tableから取得
-    if content_type='user' and studio_name='own'
-      /* 同上 */
-    # 自社ユーザー以外の場合はcontent_tableから取得
-    else
-      /* 同上 */
-    end if
-    # いずれの場合もaction_tableから以下を取得
-      /* 同上 */
-from (
-    /* 同上 */
-```
 ###補足情報（FW/ツールのバージョンなど）
 phpMyAdmin（4.8.5）で、MySQL（10.0.33-MariaDB）を使っています。
+SQL初心者がネットの見本などを参考に組み合わせたコードにつき、意味不明な部分などがあるかもしれませんがご容赦ください。
+宜しくお願い致します。