SQLで１つのテーブルから２つのデータを並べて取得したい

Question

###実現したいこと
該当のテーブルaction_tableから、
➀user_id=1が実行したfavoriteを取得する。
➁favorite_dateを入れる。
➂follow_dateを入れる。
という風に取得したいです。

###発生している問題
➀と➁はできましたが、➂ができません。
###該当のテーブル
以下action_tableになります。

CREATE TABLE action_table
    (`action_id` int, `action_name` text, `action_date` text, `actor_id` int, `target_id` int, `target_type` text)
;
INSERT INTO action_table
    (`action_id`, `action_name`, `action_date`, `actor_id`, `target_id`, `target_type`)
VALUES
    (1, 'follow',   '2020-01-01 01:01:01', 1, 3, 'user'),
    (2, 'favorite', '2020-02-02 02:02:02', 1, 4, 'user'),
    (3, 'favorite', '2020-03-03 03:03:03', 1, 3, 'user'),
    (4, 'block',    '2020-04-04 04:04:04', 4, 1, 'user'),
    (5, 'follow',   '2020-05-05 05:05:05', 1, 2, 'user'),
    (6, 'favorite', '2020-06-06 06:06:06', 1, 5, 'user')
;

###現状のソースコード
問題が発生するソースコードです。

ソースコードを実行する

実行していただくとfollow_dateがnullになってしまっていると思います。

        select favorite.target_id
             , case when 
                favorite.action_name='follow'
                then favorite.action_date else null end 
                as follow_date
             , case when 
                favorite.action_name='favorite'
                then favorite.action_date else null end 
                as favorite_date
                
        from  action_table favorite
        
        where favorite.actor_id=1 and favorite.action_name='favorite'
        
        group by favorite.target_id 

###試したこと
favoriteだけに限定せず、そしてgroup byをやめるという下記２つの変更を試みました。

変更したソースコードを実行する

必要なレコードがそろっていることが確認できました。
これをうまくgroup byすればよさそうに思えます。

        where favorite.actor_id=1 and favorite.action_name='favorite'
        ↓ 変更
        where favorite.actor_id=1

        group by favorite.target_id 
        ↓ 変更
        # group by favorite.target_id 

これら必要なレコードからnullのものを除外してみたらどうかと思い下記のようにcount()を使ったのですが、

count()を使ったソースコードを実行する

なぜか取得できたのは「3だけ」となってしまいました。（count()をmax()にしても同じでした）

「1」がfavoriteしているのは「3だけ」でなく「4・3・5」なのでそのレコードを取得したいのですが。

        select favorite.target_id
             , case when 
                favorite.action_name='follow'
                then favorite.action_date else null end 
                as follow_date
             , case when 
                favorite.action_name='favorite'
                then favorite.action_date else null end 
                as favorite_date

        ↓ count()を使うように変更

        select favorite.target_id
             , count( case when 
                favorite.action_name='follow'
                then favorite.action_date else null end 
                ) as follow_date
             , count( case when 
                favorite.action_name='favorite'
                then favorite.action_date else null end 
                ) as favorite_date

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

phpMyAdmin（4.8.5）で、MySQL（10.0.33-MariaDB）を使っています。
宜しくお願い致します。

Comments

[Orlofsky]

過去の質問を解決してから進めては？

[ikatako]

過去の質問を解決するための質問がこれなので苦笑
少しずつですが進ませてください。

[yambejp]

想定する結果を例示できますか？

[ikatako]

結果の例示の件、申し訳ございません。今回は解決していただきましたので割愛致しますが、以後気を付けます。

Answer 1

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

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') -- blockは除外
  and exists( -- いいね('favorite')をしているユーザー
        select 1 from action_table 
        where target_id =user.target_id and action_name='favorite'
      )
  and actor_id=1
group by target_id

※2も取得する必要があるなら、exsists()の条件を外してください。

Comments

[ikatako]

あちらこちらでお世話になっております。今回もどうもありがとうございます。

1がfavoriteした相手だけを取得したいと思い
where の対象を action_name='favorite'
と指定したのですが、これでは follow_date が取得できず困っておりました。

その解決策が
whereの対象を action_name in ('favorite', 'follow')
と２つを指定しつつ、exists()を使うことなのだろうと読んだのですが、exist()の中で何が起こっているのか難しいですね。

selectの指定対象は「select カラム名」のようなカラム名だけでなく「select 1」という値でも指定できるんですね。

[sazi]

action_name in ('favorite', 'follow')はblockを除外する条件です。
action_name != 'block'でも良いですが、否定形(!=)はインデックスが使用されない事が多いので、in()で指定しています。

２はaction_name='favorite'がありません。それが除外条件なのだろうと予測して、exists()部分でその判断をしています。

existsは存在するかどうかなので別に実際の項目を必要としないのです。
select * など指定する項目は何でも良いし、値でもよいのです。
項目である必要でないなら敢えて項目は指定せず、値を指定するのが習わしのようになっています。

[ikatako]

言われて初めて除外条件をろくに書いていないことに気づきました。除外条件はその通りです。ありがとうございます。

existsは…使い慣れるまでまだ練習が必要そうですね。いつも周辺の事情なども含めて教えてくださり感謝です。

Answer 2

ちょっと仕様が理解できていないので適当ですが、SelfJoinすれば良いのでは無いでしょうか？
2,3,6行目に1行目のfollowがJoinされると思うのですが。
実際に動かしていないのでご確認ください。
二つ目のCASE文は要らないと思います。

select favorite.target_id
             , case when 
                follow.action_name='follow'
                then follow.action_date else null end 
                as follow_date
--             , case when 
--                favorite.action_name='favorite'
--                then favorite.action_date else null end 
--                as favorite_date
             ,  favorite.action_date
        from  action_table favorite
        left outer join action_table follow on favorite.actor_id = follow.actor_id
                                           and follow.action_name='follow'
        where favorite.actor_id=1 and favorite.action_name='favorite'

Comments

[ikatako]

ありがとうございます。ご指摘のように仕様の提示が不足していたために誤解させてしまったと思います。ご回答ありがとうございます。