内部結合した重複するデータを除外してデータを取得したい

Question

前提・実現したいこと

内部結合した重複するデータを除外してデータを取得したい

現在PHPで自作アプリを制作しています。

3つのテーブルを内部結合しましたが、id①(リクエストID)、id②(企業ID),id③(リストID)のうち、id③(リストID)が重複しないようにデータを抽出したいです。

以下テーブルの構造になります。
コメントテーブル(comments)

投稿テーブル(posts)

企業テーブル(companies)

以下のSQL文では、
欲しい情報：
commentsテーブルの全データ、
postsのcompany_id,title,category_id,price,tag
companiesテーブルのcompany_name

問題のSQL

  public function getRequestMyShow($id)
  {
    $sql = 'SELECT DISTINCT post_id, r.id AS リクエストID, r.company_id AS 企業ID, r.post_id AS リストID, r.text AS リクエストテキスト, r.created_at AS 最新のリクエスト送信日, c.id AS 企業ID, c.company_name, p.id AS リストID, p.title, p.category_id, p.price, p.tag
            FROM comments r 
            INNER JOIN companies c ON r.company_id = c.id 
            INNER JOIN posts p ON r.post_id = p.id 
            WHERE r.company_id = :id';
    
    $stmt = connect()->prepare($sql);
    $stmt->bindValue(':id', $id, PDO::PARAM_INT);
    $stmt->execute();
    $result = $stmt->fetchall(PDO::FETCH_ASSOC);
    return $result;
  }

###実行結果

###実現したいこと
リストID(ここでは113)が重複したデータとなるので、113のデータは重複しないようにデータを抽出したい。

つまり、現状リストIDで113が3つ抽出されているが、1つだけでいい。

###試したこと
上記のようにdistinctを使ってみたが、post_idのカラムが結合されただけで重複したデータを除外できない。

SELECT DISTINCT リストID

のようにすると、定義されていないと出る。

副問合せで、

SELECT r.id AS リクエストID, r.company_id AS 企業ID, r.post_id AS リストID, r.text AS リクエストテキスト, r.created_at AS 最新のリクエスト送信日, c.id AS 企業ID, c.company_name, p.id AS リストID, p.title, p.category_id, p.price, p.tag
            (SELECT DISTICT リストID FROM comments)
            FROM comments r 
            INNER JOIN companies c ON r.company_id = c.id 
            INNER JOIN posts p ON r.post_id = p.id 
            WHERE r.company_id = :id

みたいに書いてみたが、SQLの1064エラーが出る。

Comments

[sazi]

コメントテーブルのデータは、投稿テーブルに対して1件だけで良いという事ですか？
それとも、企業テーブルに対して、コメントテーブルおよび投稿テーブルは1件という事でしょうか？

[退会済みユーザー]

前者です。
現在の状況だと、投稿したリストに対してコメントしたもの全て取得となってしまいます。
その結果、投稿IDが同じものの過去のコメントも拾って来てしまう状況です。

[退会済みユーザー]

つまり、投稿ID(例えばID 113)の最新コメント1件だけ欲しいけど、過去のコメントも全て拾って来てしまう現象が起きてます。

[sazi]

じゃあ、軸にするのは投稿テーブルですよね。
> 欲しい情報：commentsテーブルの全データ
は適当な1件のデータですか？
↑最新とは何で判断しますか？

[退会済みユーザー]

あっそうなんですね。一回投稿テーブルを軸にやってみます。
最新を判断するのは、commentsテーブルのcreated_atで判断します。

[m.ts10806]

命名には気を付けた方が良いかと。
予約語ではないですが、取得する際に気を付けるべき用語はあります。
https://dev.mysql.com/doc/refman/5.6/ja/reserved-words.html#reserved-words-5-6-Z
今回ならtext とか。
「引用符で囲まない識別子として使用することを許可しています」とありますが、「じゃあ普通に使えるんだね」と思ってしまうとどこかで大けがします。
もっと意味のある名前をつけることが推奨されます。

[退会済みユーザー]

かしこまりました。ありがとうございます。

[m.ts10806]

エイリアスとしても全角は避けた方がいいでしょうしね。

[退会済みユーザー]

そうなんですね。リストIDとかではなくて、list_idみたいにした方がいいってことですよね。

[sazi]

リストIDとか項目名が重複するのは値を取得しようとしたときはNGです。

Answer 1

元のSQLを生かして、

投稿IDの最新コメント1件だけ

とするには、相関副問合せによるexists条件を追加します。

SELECT  r.id AS リクエストID
      , r.company_id AS 企業ID
      , r.post_id AS リストID
      , r.text AS リクエストテキスト
      , r.created_at AS 最新のリクエスト送信日
      , c.id AS 企業ID
      , c.company_name
      , p.id AS リストID
      , p.title
      , p.category_id
      , p.price
      , p.tag
FROM  comments r 
      INNER JOIN companies c ON r.company_id = c.id 
      INNER JOIN posts p ON r.post_id = p.id 
WHERE not exists(
        select 1 from comments 
        where company_id = r.company_id 
          and post_id = r.post_id 
          and created_at > r.created_at
      )
  and r.company_id = :id

インラインビューで、commentsを最新のものだけにしたものと結合する方法もあります。

SELECT  r.id AS リクエストID
      , r.company_id AS 企業ID
      , r.post_id AS リストID
      , r.text AS リクエストテキスト
      , r.created_at AS 最新のリクエスト送信日
      , c.id AS 企業ID
      , c.company_name
      , p.id AS リストID
      , p.title
      , p.category_id
      , p.price
      , p.tag
FROM  (
        select * from comments r
        WHERE not exists(
                select 1 from comments 
                where company_id = r.company_id 
                  and post_id = r.post_id 
                  and created_at > r.created_at
              )
      ) r
      INNER JOIN companies c ON r.company_id = c.id 
      INNER JOIN posts p ON r.post_id = p.id 
where r.company_id = :id

投稿テーブルを軸にすると、commentsテーブルで取得するのはtextのみですから、それだけサブクエリーで取得する方法もあります。

Comments

[退会済みユーザー]

ありがとうございます。
こちらで取得したい内容ができました。
slectのタイポがございましたが、WHERE以下の方法についてまだ理解が足りないので、勉強して知識をつけていきます。

[sazi]

手打ちでしたので、失礼しました。
回答は修正しておきました。