質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
87.20%
MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

phpMyAdmin

phpMyAdminはオープンソースで、PHPで書かれたウェブベースのMySQL管理ツールのことです。

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

解決済

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

ikatako
ikatako

総合スコア268

MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

phpMyAdmin

phpMyAdminはオープンソースで、PHPで書かれたウェブベースのMySQL管理ツールのことです。

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

2回答

0評価

1クリップ

553閲覧

投稿2020/02/05 08:07

編集2020/02/05 12:02

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

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

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

が問題となっています。

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

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

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

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

以下CREATE TABLEです。

SQL

# 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') ;

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

実行サンプル

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

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

宜しくお願い致します。

良い質問の評価を上げる

以下のような質問は評価を上げましょう

  • 質問内容が明確
  • 自分も答えを知りたい
  • 質問者以外のユーザにも役立つ

評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

気になる質問をクリップする

クリップした質問は、後からいつでもマイページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

  • プログラミングに関係のない質問
  • やってほしいことだけを記載した丸投げの質問
  • 問題・課題が含まれていない質問
  • 意図的に内容が抹消された質問
  • 過去に投稿した質問と同じ内容の質問
  • 広告と受け取られるような投稿

評価を下げると、トップページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

Orlofsky

2020/02/05 10:19

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

2020/02/05 11:13

まったく仰る通りかと思います。お恥ずかしい限りです。 あれからずっと取り組んでいるのですがなんとか条件分岐の方は(たぶん)できまして、残るはaction_tableのデータの問題だけになりました。 改めてその点だけに絞って質問を編集させて頂きますので、少々お時間頂戴できましたら幸いです。いつもご指摘ありがとうございます。
ikatako

2020/02/05 12:05

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

まだ回答がついていません

会員登録して回答してみよう

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
87.20%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問

同じタグがついた質問を見る

MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

phpMyAdmin

phpMyAdminはオープンソースで、PHPで書かれたウェブベースのMySQL管理ツールのことです。

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。