人力検索はてな
モバイル版を表示しています。PC版はこちら
i-mobile

データ内の全体のソート番号を取得したい

テーブル
id, name, group_id, sort_each_group_id

1,森羅万象,null,1
2,果物,1,1
3,動物,1,2
4,りんご,2,3
5,ばなな,2,2
6,めろん,2,1
7,ありさん,3,2
8,ぞうさん,3,1

このような木構造のデータがあります。
sort_each_group_idはgroup_id内でのソート順です。

このとき

森羅万象 1
果物 2
動物 3
めろん 4
ばなな 5
りんご 6
ぞうさん 7
ありさん 8

のようにトータルで見たときのソート順を出したいのですが、
どのように書いたら良いでしょうか。

言語はPHPです。

●質問者: timestep
●カテゴリ:ウェブ制作
○ 状態 :終了
└ 回答数 : 4/4件

▽最新の回答へ

1 ● うぃんど
●50ポイント

(1)SQL側で行う場合

下記の様なRDBMSは分析関数の1つとしてrow_number関数を備えています。
ORACLE http://docs.oracle.com/cd/E16338_01/server.112/b56299/functions156.htm|
PostgreSQL http://lets.postgresql.jp/documents/technical/window_functions|
SQL Server http://msdn.microsoft.com/ja-jp/library/ms186734%28v=sql.90%29.aspx|

phpのバックエンドとして組み合わされることの多いMySQLは関数を備えていないため変数を使ってコーディングしたりします。
MySQL ROW_NUMBERで検索すれば簡単に見つかります。例えば下記
http://q.hatena.ne.jp/1192783057

(2)php側で行う場合

SQLで処理できるものはSQLで処理することが多いのですが、
php側で処理したい場合はカウント用の変数を用意して一行分echoが済んだら+1するという処理にします。

$i = 1;
while ($row = $result->fetch_assoc()) {
 echo $row['name'], $i;
 $i++;
}

timestepさんのコメント
質問の趣旨が伝わっていないようです。 質問の例でデータのグループ内のソート順と、トータルのソート順が相関しています。 何かしらのデータを取得してそれについて採番したいという趣旨ではありません。

うぃんどさんのコメント
SQLで ORDER BY group_id, group_idsort_each_group_id でソートしておいて、 その結果を頭から順にphpで受け取って出力してますよね? その時にphpで連番をつけようというのが(2)ですよ。

うぃんどさんのコメント
データベース内で連番をつけてしまいたい場合は(1)ですが、 こちらのサンプルコードを要望されるならデータベースを指定してください。

timestepさんのコメント
ORDER BY group_id, sort_each_group_id では解決しません。 group_idという名前とデータの例がわかりづらいかったかもしれませんが、 group_idというよりもparent_idという名前がふさわしいかもしれません。再帰的なデータ構造になっています。 なので、group_id順にならべてもsort順は逆ということが起こり得ます。

うぃんどさんのコメント
>group_id順にならべてもsort順は逆ということが起こり得ます。 いろいろ考えていて私の頭が混乱してしまっているのですが、 子idが親idより先(=小さい値)になるなんてことがあるということですか?

timestepさんのコメント
わかりにくくて、すみません。 質問に出したデータを少し変えてみます。たとえば以下のように果物と動物のsort順を逆にした場合だと 2,果物,1,2 3,動物,1,1 ありさん 4 ぞうさん 5 と順位付けしたいです。 order by group_id, sort_each_group_idで並べると、果物より動物の方がsort順は上のはずなのに、 めろん 4 ばなな 5 のままになってしまいますよね

うぃんどさんのコメント
以前PostgreSQLを使った質問をしておられたのでPostgreSQLで作ってみました。 再帰処理で始祖にたどり着くまでのid類を配列に蓄積してソートするという流れになっています。 再帰処理の可能なデータベースであれば同様にできるでしょう。 この質問はphpでということですがスマートに解決できる手段を持っておらず、これにて失礼します。 >|| CREATE TEMPORARY TABLE a (id INT, name TEXT, group_id INT, sort_each_group_id INT); INSERT INTO a VALUES(1,'森羅万象',null,1) ,(2,'果物',1,1) ,(3,'動物',1,2) ,(4,'りんご',2,3) ,(5,'ばなな',2,2) ,(6,'めろん',2,1) ,(7,'ありさん',3,2) ,(8,'ぞうさん',3,1) ; WITH RECURSIVE r AS ( SELECT ARRAY[0] n,* FROM a WHERE group_id is null UNION ALL SELECT array_append(array_append(array_append(n, r.id), a.sort_each_group_id), a.id), a.* FROM a, r WHERE a.group_id = r.id ), s AS ( SELECT * FROM r ORDER BY n ) SELECT row_number() over (), * FROM s ; ||< |row_number|n|id|name|group_id|sort_each_group_id| |1|{0}|1|森羅万象|null|1| |2|{0,1,1,2}|2|果物|1|1| |3|{0,1,1,2,2,1,6}|6|めろん|2|1| |4|{0,1,1,2,2,2,5}|5|ばなな|2|2| |5|{0,1,1,2,2,3,4}|4|りんご|2|3| |6|{0,1,2,3}|3|動物|1|2| |7|{0,1,2,3,3,1,8}|8|ぞうさん|3|1| |8|{0,1,2,3,3,2,7}|7|ありさん|3|2|

timestepさんのコメント
おお、Postgresqlでできるのですね。ありがとうございます。この順番でばっちりです。

2 ● tezcello
●33ポイント

> ソート順を設定したい
> トータルのソート順でselectしたい
> 望みどおりのselect順
「ソート順」が何を指しているのか理解できないのですが、要するにDBのデータを「このとき?」の順にソートしたいという事でしょうか?
それならば array_multisort() の出番だと思います。
http://www.php.net/manual/ja/function.array-multisort.php#example-4798

<?php
// DBから素直に取得した状態の配列を
// ご提示の例に倣って作ります
$data = 
array(
array(1,'森羅万象',null,1),
array(2,'果物',1,1),
array(3,'動物',1,2),
array(4,'りんご',2,3),
array(5,'ばなな',2,2),
array(6,'めろん',2,1),
array(7,'ありさん',3,2),
array(8,'ぞうさん',3,1),
);

// 実際にはカラム名のキーで取得できるでしょうが
// 上の配列に書き込むのが面倒だったので...
define('ID', 0);
define('NAME', 1);
define('GROUP_ID', 2);
define('SORT_EACH_GROUP_ID', 3);


// 実際の作業はここから

// マニュアルにある例そのまま
foreach ($data as $key => $row) {
 $groupId[$key] = $row[GROUP_ID];
 $sortEachGroupId[$key] = $row[SORT_EACH_GROUP_ID];
}

array_multisort($groupId, $sortEachGroupId, $data);
var_dump($data);
?>


> 動物と果物のsort順を入れ替えた時(sort_each_group_idの値を逆にしたとき)にありさんが4番目、ぞうさんが5番目になりません。
順序を入れ替えると、ガイドとなる値が変化するようにするわけだからこんな感じ。
親と子の関係が事前に分かっていないとダメですから無理やり感が否めませんが...

<?php
$ref = array(0=>0); // 先頭にダミー値を
foreach ($data as $key => $row) {
 $ref[$row[ID]] = $ref[$row[GROUP_ID]]*100 + $row[SORT_EACH_GROUP_ID];
}
unset($ref[0]); // 先頭の値(ダミー)は削除しておく

array_multisort($ref, $data);

チャンとやる為には iterator クラスなどを駆使して...って事になるのでは?


timestepさんのコメント
わかりにくくてすみません。 こちらも上の回答と同じなのですが、動物と果物のsort順を入れ替えた時(sort_each_group_idの値を逆にしたとき)にありさんが4番目、ぞうさんが5番目になりません。 ソート順というのをうまく説明するのが難しいのですが、group_idが2のりんご、ばなな、めろんはid 2の果物に紐付いています。そして果物のsort_each_group_idが動物より後に来るような場合、果物に紐づくりんご、ばなな、めろんは、動物に紐づくありさん、ぞうさんよりも後に来る、という順番になります。

timestepさんのコメント
なるほど。こちらでも、多少微調整して使えそうな気がします。ありがとうございました

3 ● cno
●0ポイント

windofjulyさんやtezcelloさんの書かれている内容で
データベースから取り出した内容の格納方法はあっていると思います。

お困りの点は、SQLを発行してデータベースから取り出した時
思った通りの順番になっていないということでしょうか?

その場合、order by句の指定方法が影響しているのではないかと推測します。

ソートの条件が
・group_idの昇順でソート
・group_idの値が同じ場合はその中でさらにsort_each_group_idの昇順でソート
である場合、例えば

select * from テーブル
order by group_id,sort_each_group_id


で解決しないでしょうか?


timestepさんのコメント
こちらだと解決しません

4 ● suenaga3
●33ポイント ベストアンサー

実際に木構造でデータを作成して、幅優先探索で取得していくのはどうでしょう。
単純というか愚直な方法ですが、、、。

初期設定部分はtezcelloさんのコードを真似させていただきました。

<?php
$data = 
array(
array(1,'森羅万象',null,1),
array(2,'果物',1,1),
array(3,'動物',1,2),
array(4,'りんご',2,3),
array(5,'ばなな',2,2),
array(6,'めろん',2,1),
array(7,'ありさん',3,2),
array(8,'ぞうさん',3,1),
);

// 実際にはカラム名のキーで取得できるでしょうが
// 上の配列に書き込むのが面倒だったので...
define('ID', 0);
define('NAME', 1);
define('GROUP_ID', 2);
define('SORT_EACH_GROUP_ID', 3);

//***************************************
// 実際の作業はここから
//***************************************

// 木構造作成
$chktree = mkTree($data);

// 幅優先探索
$res = habaYusenTansa($chktree, array($chktree[NAME]));

// 結果出力
var_export($res);

//******************
// 木構造作成
//******************
function mkTree($data) {
 $root = null;
 $index = array();

 //****************************************
 // 自分の情報を作成して親に関連付けさせる
 //****************************************
 foreach ($data as $val) {
 $self = &$index[$val[ID]];
 // $indexに自身を登録
 $self[NAME] = $val[NAME];

 // 親に登録
 $parent_id = $val[GROUP_ID];
 if($parent_id == null) {
 // 親が無い場合=>ROOT
 $root = &$self;
 }
 else {
 // 親に自分を登録
 $index[$parent_id]['children'][$val[SORT_EACH_GROUP_ID]] = &$self;
 }
 }
 // ROOTを返す
 return $root;
}

//***************************************
// 幅優先探索
// (自身の分はあらかじめresに登録しておく)
//***************************************
function habaYusenTansa($node, $res) {
 // 子ノード取得
 $children = $node['children'];
 if(!isset($children)) { return $res; }

 // 子ノードのソート
 ksort($children);

 // 子を登録
 foreach($children as $child) {
 $res[] = $child[NAME];
 }

 // 子を走査
 foreach($children as $child) {
 $res = habaYusenTansa($child, $res);
 }

 return $res;
}
?>

timestepさんのコメント
おお。ありがとうございます。汎用的に使えそうな方法ですね。こちらでやってみたいと思います。
関連質問

●質問をもっと探す●



0.人力検索はてなトップ
8.このページを友達に紹介
9.このページの先頭へ
対応機種一覧
お問い合わせ
ヘルプ/お知らせ
ログイン
無料ユーザー登録
はてなトップ