「LEFT OUTER JOIN」で、取得できないケースを解決したい

Question

###前提・実現したいこと
「jp_contents_table」から「comment」を取得しています。

この「attach_id」のカラムの値が0でないとき、
コメントへの添付データとして「attached_xxx」というカラムを連結したいです。

しかし添付データの取得先を分岐させるにあたって、問題が生じています。

###発生している問題
図でご説明します。

まず「comment」である【content_id=11,7,3,1】が取得されています。

このうち「attach_id」のカラムの値が0でないのは【content_id=11,7,3】で、
この３つが添付データを持つということです。

なのでこの３つには「attached_xxx」のカラムに値を入れたいと思っています。

そして【content_id=7】は「attached_xxx」のカラムに値が入っていて問題ありませんが、（図の青枠）

なぜか【content_id=11,3】は「attached_xxx」のカラムの値がnullとなってしまいます。（図の赤枠）

###問題のソースコード

発生している問題を起こすソースコードはこちらです。
（文字数制限の都合上、リンクだけとさせて頂きました。）

###目的の結果
こちらが目的の結果です。図の赤枠でnullとなっている部分は次のように埋めたいです。
このように埋まるロジックは次に記します。

CREATE TABLE result
    (`content_id` int, `author_id` int, `content_date` text, `parent_thread_id` int, `parent_content_id` int, `content_type` text, `content_text` text, `attach_id` int, `attach_type` text, `attach_country_code` text, `attached_id` varchar(6), `attached_type` varchar(6), `attached_country_code` varchar(6), `attached_nick_name` varchar(6), `attached_content_text` varchar(13), `attached_follow_date` varchar(6), `attached_favorite_date` varchar(19))
;
INSERT INTO result
    (`content_id`, `author_id`, `content_date`, `parent_thread_id`, `parent_content_id`, `content_type`, `content_text`, `attach_id`, `attach_type`, `attach_country_code`, `attached_id`, `attached_type`, `attached_country_code`, `attached_nick_name`, `attached_content_text`, `attached_follow_date`, `attached_favorite_date`)
VALUES
    (11, 2, '2020-11-11 11:00:00', 8,  0, 'comment', '米国の映画スレを添付します',       9, 'thread', 'jp', 9,   'thread', 'jp', null,       '米国の映画について語るスレ', null, null),
    (7, 1,  '2020-07-07 07:00:00', 10, 0, 'comment', '日本の田中三郎さんを添付します。', 3, 'user',   'jp', 3,   'user',   'jp', '田中三郎', null,                         null, '2020-03-03 03:00:00'),
    (3, 4,  '2020-03-03 03:00:00', 8,  0, 'comment', '韓国の지안さんを添付します。',     3, 'user',   'ko', 3,   'user',   'ko', null,       '지안',                       null, null),
    (1, 3,  '2020-01-01 01:00:00', 9,  0, 'comment', '米国のお勧め映画は何ですか？',     0, null,     null, null, null,    null, null,       null,                         null, null)
;

###取得のロジック

添付データの取得先は、
『jpのuser』ならば「jp_users_table」のテーブルにあり、
『jpだけどuser以外』なら「jp_contents_table」のテーブルにあり、
『jp以外』なら「xx_contents_table」のテーブルにある
という構造になっています。

（『jpのuser』とは、『attach_country_code='jp' かつ attach_type='user'』ということです。）

レコード	「attach_id」	分類	添付データの取得先
【content_id=11】	9	『jpだけどuser以外』	jp_contents_table
【content_id=7】	3	『jpのuser』	jp_users_table
【content_id=3】	3	『jp以外』	xx_contents_table

そのため取得のロジックは、

【content_id=11】は「jp_contents_table」から「content_id=9」を添付データとして扱いますので、目的の結果では「attached_content_text」のカラムに米国の映画について語るスレが入っています。

【content_id=7】は「jp_users_table」から「user_id=3」を添付データとして扱いますので、目的の結果では「attached_nick_name」のカラムに田中三郎が入っています。

【content_id=3】は「xx_contents_table」から「content_id=3」を添付データとして扱いますので、目的の結果では「attached_content_text」のカラムに지안が入っています。

###試したこと
まず「attached_id」のカラムを定義している、上記SQL fiddleの32~37行目のas attached_idに注目しました。

これが取得できていないから、【content_id=11,3】のレコードの「attached_id」のカラムがnullとなっているのだと思います。

そこでこの32~37行目を下記のように変更してみました。
'テスト値' as attached_id

しかし【content_id=11,3】のレコードの「attached_id」のカラムはnullのままなのです。

「attached_id」というカラムを定義している部分はここしかないので、nullでなくテスト値が入ると思ったのですが、なぜnullのままなのでしょうか。ここを変更してもダメとなると、何を変更したらいいのかわからなくなってしまいました。

###補足情報（FW/ツールのバージョンなど）
phpMyAdmin（4.8.5）で、MySQL（10.0.33-MariaDB）を使っています。

Comments

[yambejp]

create table+insertでデータを提示して、どういうロジックで
どういう結果をほしいか示してください

[Orlofsky]

教授 → 教示

[Orlofsky]

改善方法を書いても無視し続けてどこまで破綻して行く？

[ikatako]

yambejpさん、申し訳ございません。先程質問に追記したとおり、後程質問のコードを修正させて頂くつもりです。出来次第またこちらの「追記・修正の依頼」を通してご報告させて頂きます。宜しくお願い致します。

[ikatako]

Orlofskyさん、ご指摘ありがとうございます。訂正致しました。どうぞ見守って頂ければと思います。

[Orlofsky]

そうやって誤字を直すことしかやらない態度。そろそろ見捨てる時期。

[ikatako]

すみません、ひょっとして「‘」を「`」に直せというご指摘でしたでしょうか？

[Orlofsky]

文字列とnullの違いを理解できない、自分で調べようとも思わないレベル。

[amura]

attach_id=9のNULLになるのは
on timeline_list.attach_id=action_list.attached_content_id
and timeline_list.attach_type=action_list.attached_content_type
and timeline_list.attach_country_code=action_list.attached_country_code
の際にjp_actions_table側に9, 'thread','jp'が無いのでJOINせずNULLとなっています。何故９がないのかはわかりません。

[ikatako]

yambejpさん、遅くなりました。ただいま取得のロジックを追加させて頂きました。

かなり入り組んだ長い文章になってしまいまして、また不備などあるかもしれませんが、その折はまたご指摘いただけましたら幸いです。

尚、create table+insertは文字数の都合上、SQL fiddleの方へ掲載させて頂きました。

[ikatako]

amuraさん、なるほど。ありがとうございます。

「何故９がないのか」ですが、「タイムラインに流すものへの添付データに対して、必ず何かアクションをしているとは限らないから（xx_contents_tableのattach_idは、必ずjp_actions_tableにあるわけではないから）」ということになります。

[ikatako]

yambejpさん、失礼いたしました。目的の結果を失念しておりましたので、改めて質問に掲載させて頂きました。もしお時間ございましたら、どうぞ宜しくお願い致します。

Answer 1

「xx_contents_table」の「content_id=9」を添付データとして扱っており

の部分はxx_contents_tableに9が存在していない理由が分かりませんでした

同じく【content_id=2】のレコードは、

SQLを少し直してみました、行数が多いので下記に記入しました
問題のgroup byはcase結果で行いたく数字としました。(修正しました)

# 最終的な取得
# 最終的な取得
select *
from
  (
    select *
    from jp_contents_table
    where timelines regexp 'thread_3|author_2|tag_2' and content_type='comment' or content_type='reply'
  ) as timeline_list

  left outer join
  (
     # 添付データの取得
     select
          jp_actions.target_id as attached_content_id
        , case when jp_actions.target_type='user' and jp_actions.target_country_code='jp'
               then jp_actions.target_type
               else jp_contents.attach_type end as attached_content_type
        , case when jp_actions.target_type='user' and jp_actions.target_country_code='jp'
               then jp_actions.target_country_code
               else jp_contents.attach_country_code end as attached_country_code
        , case
           when jp_actions.target_type='user' and jp_actions.target_country_code='jp'
           then jp_users.nick_name
           else null
        end as attached_nick_name
        ,case
           when jp_actions.target_country_code='jp' then
               case when jp_actions.target_type='user' then null
               else jp_contents.content_text end
           else xx_contents.content_text
        end as attached_content_text
        # 添付データへのアクション日時
        , max( case
           when jp_actions.action_name='follow'
           then jp_actions.action_date
        end ) as attached_follow_date
        , max( case
           when jp_actions.action_name='favorite'
           then jp_actions.action_date
        end ) as attached_favorite_date

     from  jp_actions_table jp_actions

     # 添付データのテーブルをjoin
     left join jp_users_table jp_users
     on  jp_actions.target_id=jp_users.user_id
     left join (
          select t1.content_id, t1.author_id, t1.content_date, t1.parent_thread_id,
          t1.parent_content_id, t1.content_type,
          case when t1.attach_type='user' and t1.attach_country_code='jp'
               then t1.content_text else t2.content_text end content_text,
          t1.tag_ids,t1.timelines, t1.attach_id, t1.attach_type, t1.attach_country_code
          from jp_contents_table t1 left join jp_contents_table t2
          on t1.attach_id=t2.content_id
     ) jp_contents
     on  jp_actions.target_id=jp_contents.attach_id
     left join xx_contents_table xx_contents
     on  jp_actions.target_id=xx_contents.content_id

     # 彼がactor_idであるもの
     where jp_actions.actor_id=1
     # favorite_dateとfollow_dateが欲しい
     and jp_actions.action_name in ('favorite', 'follow')
     and exists(
          select 0 from jp_actions_table
          where target_id=jp_actions.target_id and action_name in ('favorite','follow')
     )
     # 彼とblock関係にあるjpのuserを除外
     and not exists(
          select 0 from jp_actions_table
          where target_type='user' and target_country_code='jp' and action_name='block'
          and target_id=jp_actions.target_id and jp_actions.target_type='user'
          and actor_id=1
     )

     #group by jp_actions.target_id, jp_actions.target_type, jp_actions.target_country_code
     group by attached_content_id,attached_content_type,attached_country_code

  ) as action_list

on timeline_list.attach_id=action_list.attached_content_id
and timeline_list.attach_type=action_list.attached_content_type
and timeline_list.attach_country_code=action_list.attached_country_code

order by content_date desc
;

Comments

[ikatako]

ありがとうございます。そちらコピペでこうなりました。http://sqlfiddle.com/#!9/3d40de/11

「content_id=11(attach_id=9)」の「attached_content_text」には「米国の映画スレを添付します」が入りましたが、「米国の映画について語るスレ」を入れたいところです。

そして「content_id=2(attach_id=4)」の「attached_content_text」はやはりnullのままのようでした。手ごわいです。

私も引き続き進めてみます。どうもありがとうございます。

尚、質問内容を「フォロー情報を取得」から「commentを取得」という風に少し簡易にしました。
（変えたのは「where timelines regexp 'thread_3|author_2|tag_2'」の部分だけですが、少しはわかりやすくなったかと思います。）

[amura]

groupbyを変更しました結果は変わりませんが、自環境では”Mr.ROBOT ”が表示されるのでが？？？？

[ikatako]

本当ですか、するとSQL fiddleのバグかもしれませんね。今夜わたしも自分のサーバーで試してみます。

では「米国の映画について語るスレ」の方もちゃんと入っていますか？

あと「Mr.ROBOT」の方はfavoriteもしているので、「2020-08-08 08:00:00」が「attached_favorite_date」に表示されてほしいですが、それもありますでしょうか？

[amura]

「content_id=2(attach_id=4)」のところの(2, 'follow', '2020-02-02 02:00:00', 1, ２, 'tag', '')で４が２になったてNULL表示しています？？

[amura]

「米国の映画について語るスレ」を無理やり入れてみました。更新しました。

[ikatako]

ありがとうございます。joinさせる順序がキモだったようですね。頂いたものを参考にもう少し頑張ってみます。

[ikatako]

一晩かかってしまいましたが、頂いたものを参考に、こちら↓までたどり着きました。
http://sqlfiddle.com/#!9/3d40de/116
こちらですとselectの回数が少なくて済むかと思っているのすが、いかが思われますか？
よろしければamura様のご高察を賜りたく存じます。

[amura]

ご苦労さまです。私はゴールを理解せずに結果からの推測にて作成していますので、今回正しく結果が求められれば良いと考えます。
timeline_list側を変えた事は良いと思いました。

[ikatako]

何をいつjoinするか順序が混乱していたのでおかげで助かりましたし、今回も大変勉強になりました。どうもありがとうございます。