MySQL4.0 でのクエリーの作り方を教えて下さい。


掲示板の投稿一覧と各投稿への返信コメントと返信を書いたユーザ名を SELECTにて取得したいです。

データは以下のような3つのテーブルに入っています。(簡略化しています。)

postテーブル
post_id, user_id, post_comment, ...
投稿ID,ユーザID,投稿内容, ...

commentテーブル
post_id, user_id, res_comment, ...
返信した投稿ID, ユーザID, 返信コメント, ...

userテーブル
user_id, user_name, ...
ユーザID, ユーザ名, ...


MySQL4.0なのでサブクエリが利用できません。
できれば、効率的に一つのクエリにて取得したいです。

回答の条件
  • 1人2回まで
  • 登録:2007/04/05 23:15:27
  • 終了:2007/04/06 02:28:49

ベストアンサー

id:nandedarou No.1

nandedarou回答回数230ベストアンサー獲得回数342007/04/06 00:20:55

ポイント100pt
SELECT
A.post_id,
A.user_id,
post_comment,
IF(B.user_id IS NULL,0,1) AS res_flg,
B.user_id AS res_user_id,
D.user_name AS res_user_name,
res_comment
FROM post AS A
LEFT JOIN comment AS B
USING(post_id)
LEFT JOIN user AS C
ON A.user_id = C.user_id
LEFT JOIN user AS D
ON B.user_id = D.user_id
ORDER BY post_id;

でいかがでしょうか?

次のようなテーブルを取得できると思います。

post_id,user_id,post_comment,res_flg,res_user_id,res_user_name,res_comment

投稿ID,投稿ユーザ名,投稿内容,返信の有無,返信ユーザID,返信ユーザ名,返信コメント

※res_flagカラムには、返信が有ると1、無ければ0が入ります。

※投稿一覧を表示する際、res_flgが1のときに、返信ユーザID,返信ユーザ名,返信コメント等を表示する処理をするとよいと思います。

※上記SQLの動作チェックをしておりませんので、エラー等不都合な点があったら、教えて下さい。

id:p_question

実際やってみました。

結果は取得できています。

ちゃんと伝えていなかったので申し訳ないのですが、1つの投稿にコメントが複数投稿されます。

つまり、結合した時に postテーブルのpost_idはユニークですが、その結果に対して複数のcommentテーブルのpost_idが一致し、res_commentが複数になるはずですが、多分、配列が作れないSQLの結果では、表示されていないように思いますが、このあたりはどうなっているのでしょうか?

#ポイントを差し上げたいので、コメントではなく、返答として書いて下さればと思います。

2007/04/06 01:03:44

その他の回答(1件)

id:nandedarou No.1

nandedarou回答回数230ベストアンサー獲得回数342007/04/06 00:20:55ここでベストアンサー

ポイント100pt
SELECT
A.post_id,
A.user_id,
post_comment,
IF(B.user_id IS NULL,0,1) AS res_flg,
B.user_id AS res_user_id,
D.user_name AS res_user_name,
res_comment
FROM post AS A
LEFT JOIN comment AS B
USING(post_id)
LEFT JOIN user AS C
ON A.user_id = C.user_id
LEFT JOIN user AS D
ON B.user_id = D.user_id
ORDER BY post_id;

でいかがでしょうか?

次のようなテーブルを取得できると思います。

post_id,user_id,post_comment,res_flg,res_user_id,res_user_name,res_comment

投稿ID,投稿ユーザ名,投稿内容,返信の有無,返信ユーザID,返信ユーザ名,返信コメント

※res_flagカラムには、返信が有ると1、無ければ0が入ります。

※投稿一覧を表示する際、res_flgが1のときに、返信ユーザID,返信ユーザ名,返信コメント等を表示する処理をするとよいと思います。

※上記SQLの動作チェックをしておりませんので、エラー等不都合な点があったら、教えて下さい。

id:p_question

実際やってみました。

結果は取得できています。

ちゃんと伝えていなかったので申し訳ないのですが、1つの投稿にコメントが複数投稿されます。

つまり、結合した時に postテーブルのpost_idはユニークですが、その結果に対して複数のcommentテーブルのpost_idが一致し、res_commentが複数になるはずですが、多分、配列が作れないSQLの結果では、表示されていないように思いますが、このあたりはどうなっているのでしょうか?

#ポイントを差し上げたいので、コメントではなく、返答として書いて下さればと思います。

2007/04/06 01:03:44
id:nandedarou No.2

nandedarou回答回数230ベストアンサー獲得回数342007/04/06 01:41:01

ポイント100pt

例えば、一つのpost_idの投稿に対し、2つのコメントが有る場合

commentテーブル

post_id, user_id, res_comment

1,2,コメントA

1,3,コメントB


post_id,user_id,user_name,post_comment,res_flg,res_user_id,res_user_name,res_comment

1,1,投稿ユーザ名1,投稿内容,1,2,返信ユーザ名2,コメントA

1,1,投稿ユーザ名1,投稿内容,1,3,返信ユーザ名3,コメントB


このように、結果のテーブルのデータが2行になります。

つまり、複数の返信コメントのある投稿に対しては、そのpost_idに対応する行が複数になって、結果が返ってきます。

id:p_question

ありがとうございます。

とてもよく理解できました。

PHPで掲示板を作成しているのですが、以下のどちらの方がよく使われるのでしょうか。

1. この一部重複した結果をそのまま配列に入れて、表示側で重複しているpost_id,user_id,user_name,post_commentまでの値も入れてしまう(post_commentが大きいとメモリーを食う?)

2 クエリーを2回叩いて、PHP側でpost_idを使って連想配列を作る。(クエリーが増える分だけ遅くなる)

2007/04/06 02:04:03
  • id:nandedarou
    訂正します。

    >ORDER BY post_id;
    ORDER BY A.post_id;

    postテーブルのpost_id順に並びますが、
    投稿された日付などのカラムがあり、
    それが、post_dateだとしたら、次のようにして下さい。
    ORDER BY A.post_date;

    また、commentテーブル内に、返信の順番がわかるカラムがあり、
    それが、post_dateだとしたら、次のようにして下さい。
    ORDER BY A.post_date, B.post_date;
  • id:nandedarou
    また、訂正。何度も書き直して、すみません。
    >A.user_id,

    A.user_id, A.user_name,

    >post_id,user_id,post_comment,res_flg,res_user_id,res_user_name,res_comment
    >投稿ID,投稿ユーザ名,投稿内容,返信の有無,返信ユーザID,返信ユーザ名,返信コメント

    post_id,user_id,user_name,post_comment,res_flg,res_user_id,res_user_name,res_comment
    投稿ID,投稿ユーザID,投稿ユーザ名,投稿内容,返信の有無,返信ユーザID,返信ユーザ名,返信コメント
  • id:kurukuru-neko
    >IF(B.user_id IS NULL,0,1) AS res_flg

    RESが0件の場合B-Dは全てNULLになると
    本当にユーザーが削除された場合があるので
    B.post_id is NULLの方が無難

    単に画面に表示するだけなら名前で
    あれば

    SELECT
    A.post_id,
    A.user_id,
    A.post_comment,
    IF(C.user_id IS NULL,"**登録抹消**",C.user_name) AS post_username,

    B.user_id AS res_user_id,
    B.res_comment,
    IF(D.user_id IS NULL,"*登録抹消*",D.user_name) AS res_username

    以下同じ

    PHPであればNULL値は
    is_null( xxx ) で判定可能なので
    表示側で変更する手もある。

    SELECT
    A.post_id,
    A.user_id,
    A.post_comment,
    C.user_name as post_username,

    B.user_id AS res_user_id,
    B.res_comment,
    D.user_name AS res_username

    以下同じ
  • id:nandedarou
    kurukuru-neko様ご指摘ありがとうございます。

    ●返信ユーザーが、登録抹消のされた場合、私の回答のSQLだと、res_flgが0となります。
    このままでよい場合は、変更の必要がありませんが、コメント自体はあるのだから、res_flgを1にしたいという場合は、つぎのように変更した方がよいと思われます。
    >IF(B.user_id IS NULL,0,1) AS res_flg
    を次のように、変更して下さい。
    IF(B.post_id is NULL,0,1) AS res_flg

    ●投稿ユーザーが抹消された場合と返信ユーザーが抹消された場合、抹消ユーザーの名前は、NULLという値になります。
    この場合、"**登録抹消**"というユーザ名にした場合は、kurukuru-neko様の指摘されたようにSELECTのユーザー名指定部分を変更して下さい。
  • id:p_question
    完璧です。とても良く理解できました。

    親切にPHPのコードまで、ありがとうございました。
  • id:nandedarou
    (間違っていたコメントを抹消しました。)

    >post_commentが大きいとメモリーを食う?
    そのとおりでしょう。どちらの方がよく使われるか分かりません。

    私だったら、別の方法にするかも知れません。
    (1)postテーブルのデータと投稿ユーザ名を取得
    (2)commentテーブルのデータと返信ユーザ名を取得
    foreachでループして、次のように多次元配列$Commentに格納
    $Comment[ $rec['post_id'] ][]=$rec['res_comment'];
    (3).foreachを入れ子にして投稿一覧と返信を表示
    内側のforeachは次のようにします。
    foreach( $Comment[$PostData['post_id']] as $value ){
    print $value['user_id'].'<br>';
    print $value['res_comment'];
    }
  • id:nandedarou
    抹消したコメントに書いたPHPコードは間違えていました。
    同じ投稿に対する複数の返信がある場合、後の返信で上書きされてしまいます。
  • id:nandedarou
    ちょっと、また訂正
    上記(1)(2)で、userテーブルとLEFT JOINして、名前を取得して(3)で返信ユーザー名も表示したほうがいいですね。

    (3).foreachを入れ子にして投稿一覧と返信を表示
    内側のforeachで返信情報を表示します。
    foreach( $Comment[$PostData['post_id']] as $value ){
    print $value['user_id'].'<br>';
    print $value['user_name'].'<br>';
    print $value['res_comment'];
    }
  • id:kurukuru-neko
    [訂正]

    >多分、配列が作れないSQLの結果では、
    >表示されていないように思いますが、
    >このあたりはどうなっているのでしょうか

    SQLの結果は複数行で返されます。
    名前だけの一覧のみが目的なら

    SELECT
    A.post_id,
    A.user_id,
    C.user_name as post_username,
    B.user_id AS res_user_id,
    D.user_name AS res_username
    FROM post AS A
    LEFT JOIN comment AS B
    USING(post_id)
    LEFT JOIN user AS C
    ON A.user_id = C.user_id
    LEFT JOIN user AS D
    ON B.user_id = D.user_id
    group by A.post_id,B.user_id
    ORDER BY A.post_id;

    group_concatを使えば文字列連結が出来ます。
    しかしMySQL4.1からのサポートなので使えません。

    この手のOLAP処理はMS SQL Serverだと
    PIVOT/TRANSFORMがあると簡単らしいですが。


  • id:nandedarou
    また、訂正
    >●返信ユーザーが、登録抹消のされた場合、私の回答のSQLだと、res_flgが0となります。
    と書いたけど、

    抹消ユーザーのIdがcommentテーブルにそのままあるのだから、
    >IF(B.user_id IS NULL,0,1) AS res_flg
    のままでも、res_flgは1となります。
    (※ユーザー抹消の際、commentテーブルの該当IDをNULLにするような処理をしているなら別ですが。)
    >B.post_id is NULLの方が無難
    ではあります。こっちの方が意味がわかり易いので。

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません