グループチャットのチャット未読一覧を取得する(mysql, SQL)

Question

お世話になります。
グループチャット(既読機能つきで、スタッフとユーザでテーブルが分かれていて、多対多)のテーブルを考えています。
あるスタッフ(staff)の未読のメッセージ一覧を取得したいのですが、何時間か考えてもクエリが思い浮かびません。どのようなクエリを書けば、未読のメッセージを取り出せるでしょうか。

途中までのクエリ(テーブルの結合のみ。条件が思い浮かばない)

select * from chat as c inner join chat_group as cg on c.chat_group_id = cg.id left join chat_read cr on c.id = cr.chat_id

テーブル構造

user: id, name

staff: id, name

chat_group: id, name

chat: id, chat_group_id, user_id, staff_id, comment

chat_read: id, chat_id, user_id, staff_id

データ例

user:
1, hoge_user
2, foo_user

staff:
1, hoge_staff
2, foo_staff

chat_group:
1, test_group

chat:
1, 1, null, 1, "hello hoge_staff"
2, 1, 1, null, "hello hoge_user"

chat_read: (自分で投稿したコメントは既読として登録されない)
1, 1, null, 1
2, 2, 1, null
3, 1, null, 2 <- 既読しかしないfoo_staff
4, 2, null, 2

Answer 1

テーブル結合まではある程度いけてると思うので、後一息かとは思います。

以下ケースごとの例を記載してみるので、どう修正すべきか考えてみてください。

#あるstaffのidが事前に分かる場合
この場合は結合テーブルは質問者さんのやり方でも問題ありません。
ただ結合条件を一部変更する必要があります。

以下はfoo_staff(staff_id = 2)の既読メッセージを取得するクエリです。
(※今回の質問の参考になるよう一部非効率なやり方をしてます)

SELECT
    c.* 
,   cg.*
,   cr.*
FROM
    chat as c 
    INNER JOIN chat_group as cg 
      ON  c.chat_group_id = cg.id 
    LEFT JOIN chat_read as cr
      ON  c.id = cr.chat_id
      AND cr.staff_id = 2 -- 「foo_staff」のidを指定
WHERE
    cr.id IS NOT NULL -- chat_readのid列がNULLか否かでデータ有無を判断できる。

また結合部からchat_readを除き、
下記のようにすることで同じようなことが可能です。
(可読性的にはこちらの方が良さ気か)
同じく既読データを取得するサンプルです。

SELECT
    c.* 
,   cg.*
FROM
    chat as c 
    INNER JOIN chat_group as cg 
      ON  c.chat_group_id = cg.id 
WHERE
    -- 下記は該当レコードある場合に条件成立
    -- レコードがない場合に条件成立する構文に「NOT EXISTS」もある
    EXISTS(
        -- foo_staffの既読情報を取得するクエリ
        SELECT
            *
        FROM
            chat_read as cr
        WHERE
            cr.chat_id = c.id
        AND cr.staff_id = 2
    )

#あるstaffの名前だけが事前に分かる場合
この場合はstaffテーブルも検索対象に含める必要があります。

その場合の検索方法の例を記載します。
以下は同じくfoo_staffのデータを検索してます。

SELECT
    c.* 
,   cg.*
,   cr.*
FROM
    chat as c 
    INNER JOIN chat_group as cg 
      ON  c.chat_group_id = cg.id 
    LEFT JOIN (
        SELECT 
            cr.*
        FROM
           chat_read as cr
            INNER JOIN staff as s
              ON cr.staff_id = s.id
        WHERE
            s.name = 'foo_staff'
    ) as cr
      ON  c.id = cr.chat_id
WHERE
    cr.id IS NOT NULL -- chat_readのid列がNULLか否かでデータ有無を判断できる。

EXISTS版を投下し忘れてたので投下。

SELECT
    c.* 
,   cg.*
FROM
    chat as c 
    INNER JOIN chat_group as cg 
      ON  c.chat_group_id = cg.id 
WHERE
    -- 下記は該当レコードある場合に条件成立
    -- レコードがない場合に条件成立する構文に「NOT EXISTS」もある
    EXISTS(
        -- foo_staffの既読情報を取得するクエリ
        SELECT
            *
        FROM
            chat_read as cr
            INNER JOIN staff as s
              ON cr.staff_id = s.id
        WHERE
            cr.chat_id = c.id
        AND s.name = 'foo_staff'
    )

#最後に
上記を参考に条件部や、クエリを再考してみてください。

いずれにしろ後一押しですので、頑張って挑戦してみてください。
またわからない点あればコメントなどでは回答受け付けます。

Answer 2

select * from chat where id not in ( select chat_id from chat_read where staff_id = 2);
とりあえず解決 というところだとこれでどうでしょうか？
例) staff_id が2の未読の場合