思うようにレコードの並び替えができません。

質問

現在、PHP+MySQLで掲示板作成を行っております。色々と調べたものの、どのようなSQL文で実行すれば良いかが分からなかったため質問をさせていただきました。

掲示板の種類は、返信記事がぶら下がっていくタイプです。

親記事1
┣子記事1-1
┗子記事1-2

親記事2
┠子記事2-1
┗子記事2-2

フィールドの内容は以下のようなものを考えていますが、必要があれば追加変更したいと思っています。
テーブル：message
id(ユニーク値)｜parentNo(親記事の番号)｜groupNo(同一親記事に付く子記事の並び順)｜title
1｜1｜0｜親記事1
2｜1｜1｜子記事1-1
3｜2｜0｜親記事2
4｜2｜1｜子記事2-1
5｜1｜2｜子記事1-2
6｜2｜2｜子記事2-2

表示する順番の条件
i)親記事が書き込まれるとその親記事を最上位に表示
ii)ある親記事に対しての返信があると、その親記事と共に最上位に表示
例)6番目のレコード「子記事2-2」が書き込まれると、1番目のレコード「親記事1」よりも上に表示する

select id, parentNo, groupNo, title from message order by parentNo, groupNo;
とすると、
1｜1｜0｜親記事1
2｜1｜1｜子記事1-1
5｜1｜2｜子記事1-2
3｜2｜0｜親記事2
4｜2｜1｜子記事2-1
6｜2｜2｜子記事2-2

と表示されるものの、最後の書き込みの「子記事2-2」を含む「親記事2」が「親記事1」よりも下に来てしまい、意図する順番と異なって表示されてしまいます。

select max(id), parentNo, groupNo from message group by pNo order by id desc;
とすると、
6｜2｜0｜親記事2
5｜1｜0｜親記事1

と表示されるので、それを利用すれば、という考えはあるのですが、フィールドの追加等を行い、MySQLだけを駆使して意図する順番通りにデータを取得することはできますでしょうか？

厳しい場合にはPHPで調整をしようと思っています。
お答えをよろしくお願いします。

回答No.3

komugi100g · ベストアンサー

要するに、こういう出力を得たいのでしょうか？

3｜2｜0｜親記事2
4｜2｜1｜子記事2-1
7｜2｜2｜子記事2-2
6｜3｜0｜親記事3
1｜1｜0｜親記事1
2｜1｜1｜子記事1-1
5｜1｜2｜子記事1-2

サブクエリを含むのでちょっと長くなりますが、

SELECT m.* FROM
(SELECT * FROM message ORDER BY groupNo) m
RIGHT JOIN
(SELECT MAX(id) AS idmax, parentNo
FROM message GROUP BY parentNo ORDER BY idmax DESC) s
ON m.parentNo=s.parentNo;

で実現すると思います。
これと同じものを、予めビューを作っておくことで、

# groupNo でソートした message テーブル
CREATE VIEW v_message AS
SELECT * FROM message ORDER BY groupNo;

# スレッドの順番
CREATE VIEW v_sort AS
SELECT MAX(id) AS idmax, parentNo
FROM message GROUP BY parentNo
ORDER BY idmax DESC;

次のように簡単にすることができます。

SELECT m.* FROM v_message m RIGHT JOIN v_sorted s
ON m.parentNo=s.parentNo;

MySQL のバージョンによってはビューが使えないかも。

ところで、その仕様だと sage 機能を拡張できないような
気がするのですが・・・
あと groupNo の役割を id で代用できそうな気がします。

お礼

まだまだSQLの勉強がたりずに、どのようにするのが効率が良いかなど分からないので、まだまだ勉強したいと思っています。
遅くなり申し訳ありませんが、お答えありがとうございました。

回答No.2

moon_night

親記事番号のなかで最大の子記事番号を取り、
それをユニーク値でソートすればいけそうですね。

SELECT SUBSTRING( MIN(CONCAT(id,parentNo)) , 1 ,1) AS 'oya_id' , SUBSTRING( MAX( CONCAT(parentNo,groupNo,id) ), 3 ,1) AS 'oya', SUBSTRING( MAX( CONCAT(parentNo,groupNo,id) ), 2 ,1) AS 'ko' FROM message GROUP BY parentNo order by 'oya' desc;

要するに、親記事番号と他の番号を連結して番号を取り、必要なものだけを切り出します。
'oya_id'は親記事のIDを取ります。
IDと親記事番号を連結し、最小のもの＝最初に書かれた親記事番号のもの＝親記事 を取る。
'oya'は親記事のなかで最後に書かれた子記事IDです。
親記事番号と子記事番号を連結させて最大のもの＝最新の記事をとります。最後にIDを連結しているのは、このIDを取るために連結をして切り出しています。
最後の'ko'は、親記事に何個の子記事があるか（親記事番号の最大の子記事番号）です。

この場合、一桁ならば問題はありませんが、二桁とかになってくると問題が発生しますので、
zerofillオプションをつけて桁数を合わせる必要があります。

結構強引にやっているので、tmptableやINDEXをうまく使ったほうが処理が早いかもしれません。

お礼

SQLの勉強中であったため、難しいなぁと思ったのですが、そのようなやりやり方が可能なのですね…
遅くなり申し訳ありませんが、お答えありがとうございました！

回答No.1

wakame0729

ツリー構造、2ちゃんねるのようなスレッド、それに質問のような親子構造の場合、単純なソートだけ並べ替えるのは困難です。通常は親記事のみ抽出しループしつつ、親記事毎に子記事を抽出するという事が必要です。

以前僕も同じ構造の掲示板を作った事がありますが、その時はsortというフィールド(int)を作って、データ挿入時に順番を決定してそのフィールドに格納しました。

親記事が投稿された場合はmax(sort)+1です。子記事が投稿された場合、同じparentNoでのmax(sort)+1を求めてこれを$sortとすると、
update message set sort=sort+1 where sort >= $sort;
そしてこの値をsortフィールドにして記事を挿入します。

このような事をする事によって、表示の際はorder by sortで抽出するだけで望みの順番で記事を取得する事ができます。

少し複雑になりますが、この方法はツリー構造にも応用ができます。記事リストの表示速度を重視した場合、こんな方法もアリかな、と思います。

お礼

遅くなり申し訳ありません。
なるほど、そのような手法もありですね。
検討してみたいと思います。
お答えありがとうございました。