- PR -

スレッド式掲示板について

1
投稿者投稿内容
けん
会議室デビュー日: 2004/10/13
投稿数: 8
お住まい・勤務地: 岡山県
投稿日時: 2006-11-20 20:45
お世話になります。けんといいます。

現在、DBに MySQL4.0 を使用して会員専用のスレッド式掲示板を作成しています。

コード:

-- スレッド

CREATE TABLE Thread(

    id              mediumint unsigned  AUTO_INCREMENT,

    memberId        smallint  unsigned  NOT NULL,

    date            datetime            NOT NULL,

    title           varchar(64)         NOT NULL,

    message         text                NOT NULL,

    

    PRIMARY KEY(id)

);



-- スレッドへのコメント

CREATE TABLE Comment(

    id              mediumint unsigned  AUTO_INCREMENT,

    threadId        mediumint unsigned  NOT NULL,

    memberId        smallint  unsigned  NOT NULL,

    date            datetime            NOT NULL,

    message         text                NOT NULL,

    

    PRIMARY KEY(id),

    INDEX(threadId)

    FOREIGN KEY(threadId) REFERENCES Thread(id)

);


上記のようなテーブル構成で、

コード:

┏━━━━━━━━┯━━━┯━━━━━━━━━━┓

┃スレッドタイトル│返信数│最終投稿日時        ┃

┣━━━━━━━━┿━━━┿━━━━━━━━━━┫

┃スレッド１      │    12│2006-11-20 18:25:00 ┃

┃スレッド２      │     6│2006-11-19 17:34:00 ┃

┃スレッド３      │     0│2006-11-10 18:25:00 ┃

┃スレッド４      │    43│2005-12-31 19:44:00 ┃

┃スレッド５      │    24│2004-09-14 09:15:00 ┃

┗━━┷━━━━━┷━━━┷━━━━━━━━━━┛


といった表を取得したいのですが、これを１回のSQLで得ることは可能なのでしょうか？

各スレッドに対してスレッドIDをもとに Comment テーブルから返信数を取得する方法で
SQL を複数回発行すればできたのですが、これだと表示するスレッドの数が多くなると
その分だけ SQL を発行することになって非効率ですので・・・。

いろいろとやってみたのですが、返信のないスレッドが表示されなかったりと
自分ではできませんでした。。。

どうかアドバイスのほう、よろしくお願いします。

[ メッセージ編集済み 編集者: けん 編集日時 2006-11-22 17:48 ]
でっち６号
大ベテラン
会議室デビュー日: 2005/01/31
投稿数: 176
お住まい・勤務地: Kawasaki
投稿日時: 2006-11-20 21:45
ちょっと仕様が曖昧ですね。
少なくとも『返信がない場合の最終投稿日時には何を表示するのか』は明示しておかないと、そのものずばりの正解は出てこないと思います。

本題ですがCommentテーブルの集計クエリを副問合せで作って、Threadテーブルと外部結合すればできると思います。
ただそのままだと返答がないThreadは返信数と最終投稿日時がnullになってしまうので、coalesceやcase whenで置換して下さい。

・・・と思ったら、MySQL4.0ですか。
確かMySQLで副問合せが実装されたのは4.1からだったので無理ですかね。

SQLでやるのであれば、テンポラリテーブルを作るくらいしか手はないかな？
SQLの発行回数を気にされているのであれば、先にidごとの件数,dateのMax値を取っておいてプログラム側でマッチングするようにすれば、2回SQLを発行するだけで済むので、そちらと合わせて検討してみて下さい。
けん
会議室デビュー日: 2004/10/13
投稿数: 8
お住まい・勤務地: 岡山県
投稿日時: 2006-11-20 23:05
でっち６号様＞
返信ありがとうございます。

引用:
『返信がない場合の最終投稿日時には何を表示するのか』

すいません、これについて記述がありませんでしたね。
この場合は、スレッド作成日時が最終投稿日時、ということになります。

引用:
確かMySQLで副問合せが実装されたのは4.1からだったので無理ですかね。

おっしゃる通り、4.0では副問合せが使えないのです・・・。

引用:
先にidごとの件数,dateのMax値を取っておいて

idごとの件数が取れるのであれば、その１回で終わりの気がするのですがそういう訳ではないのでしょうか？

Thread テーブルに返信数を持って、コメントの投稿・削除のたびに返信数も変更するというのも考えたのですが、
Comment テーブルから返信数が算出できる以上、(Thread テーブルに)そういったカラムを保持するのはよくないのかなぁという気がしますし・・・。
shimix
ぬし
会議室デビュー日: 2004/08/05
投稿数: 512
お住まい・勤務地: 大分市
投稿日時: 2006-11-21 00:23
引用:

けんさんの書き込み (2006-11-20 23:05) より:
idごとの件数が取れるのであれば、その１回で終わりの気がするのですがそういう訳ではないのでしょうか？

返信がないスレッドも表示しないといけないので、１回では無理そうですね。
けん
会議室デビュー日: 2004/10/13
投稿数: 8
お住まい・勤務地: 岡山県
投稿日時: 2006-11-22 12:57
shimix様＞
返信ありがとうございます。

やはり「返信がないスレッドも表示」となると１回では無理ですか・・・。

とりあえず、Thread テーブルに返信数と最新投稿日時を保持して
Thread テーブルのみから表を取得するやり方にしようと思います。

アドバイスありがとうございました。
shimix
ぬし
会議室デビュー日: 2004/08/05
投稿数: 512
お住まい・勤務地: 大分市
投稿日時: 2006-11-22 13:11
引用:

けんさんの書き込み (2006-11-22 12:57) より:
とりあえず、Thread テーブルに返信数と最新投稿日時を保持して
Thread テーブルのみから表を取得するやり方にしようと思います。


私なら・・Threadテーブルはスレッドタイトルと作成日時だけにして（極端な話ThreadIDの管理だけ）commentテーブル→messageテーブルとして、親記事もそちらに保存するかもしれません。親子の判別が出来る項目があればその方が楽な気がします。

＃判別する項目は区分でもいいしレス元ID（親記事は0）でもいいし・・

このあたりは「掲示板の作り方」で変わってくる部分でしょうね。
けん
会議室デビュー日: 2004/10/13
投稿数: 8
お住まい・勤務地: 岡山県
投稿日時: 2006-11-22 22:01
shimix様＞
確かに Thread に対して親・子いずれの記事であれ、保持するデータは似通ってますし、
親だけが持つタイトル(title)にしても、Thread テーブルに押し込んでやればすっきりしますよね。

shimix様の意見も踏まえて、以下のようなテーブル構成を考えました。

コード:
-- スレッド
CREATE TABLE Thread(
    id              mediumint unsigned  AUTO_INCREMENT,
    createDateTime  datetime            NOT NULL,
    lastUpdateTime  datetime            NOT NULL,
    title           varchar(64)         NOT NULL,
    resCount        mediumint unsigned  NOT NULL,
    
    PRIMARY KEY(id)
);

-- スレッドメッセージ(親・子共用)
CREATE TABLE Message(
    id              mediumint unsigned  AUTO_INCREMENT,
    resId           mediumint unsigned,
    threadId        mediumint unsigned  NOT NULL,
    memberId        smallint  unsigned  NOT NULL,
    date            datetime            NOT NULL,
    message         text                NOT NULL,
    
    PRIMARY KEY(id),
    INDEX(threadId),
    INDEX(memberId),
    FOREIGN KEY(threadId) REFERENCES Thread(id),
    FOREIGN KEY(memberId) REFERENCES Member(id)
);

Thread テーブルには最終投稿日時(lastUpdateTime)と返信数(resCount)のカラムを持たせて、
Message テーブルの更新(含削除)のタイミングでそれぞれ更新。
最終投稿日時は、Message テーブルから引くこともできそうですが、Thread テーブルに持たせたほうが
スレッド一覧表示画面で使用するデータは Thread テーブルのみ参照すればよくなるので、
そのほうがスッキリするかなと思いましてこのようにしました。
（ただ、管理の際など、DBを直接触る場合には整合性を取るように注意しないといけませんが）

# Thread, Message, Member のオートナンバーIDは、すべて id で統一してますが、
それぞれ threadId, messageId, memberId にしたほうがよいのかなぁと思ったり・・・。
些細なことかもしれませんが。
けん
会議室デビュー日: 2004/10/13
投稿数: 8
お住まい・勤務地: 岡山県
投稿日時: 2006-11-23 15:33
自己レスです。

スレッド作成日時(createDateTime)は、Message テーブルの親記事の投稿日時が簡単に取れますね。

さらに、Message テーブルの threadId でグループ化してやれば

最終投稿日時：投稿日時(date)のMAX値
返信数：resId が NULL でない(＝親記事でない)ものの COUNT値

で簡単に出せちゃいますよね・・・。

結局、Thread テーブルはスレッドIDとスレッドタイトルのみ保持すればよいということで、
shimix様のおっしゃる通り、シンプルな設計でよさそうです。
1

スキルアップ／キャリアアップ（JOB@IT）