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


テーブル
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です。

回答の条件
  • 1人5回まで
  • 13歳以上
  • 登録:2013/03/01 16:52:43
  • 終了:2013/03/05 13:58:33

ベストアンサー

id:suenaga3 No.4

suenaga3回答回数19ベストアンサー獲得回数52013/03/02 19:34:55

ポイント33pt

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

初期設定部分は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;
}
?>
id:timestep

おお。ありがとうございます。汎用的に使えそうな方法ですね。こちらでやってみたいと思います。

2013/03/05 13:57:29

その他の回答(3件)

id:windofjuly No.1

うぃんど回答回数2625ベストアンサー獲得回数11492013/03/01 18:58:35

ポイント50pt

(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++;
}
他6件のコメントを見る
id:windofjuly

以前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_numbernidnamegroup_idsort_each_group_id
1{0}1森羅万象null1
2{0,1,1,2}2果物11
3{0,1,1,2,2,1,6}6めろん21
4{0,1,1,2,2,2,5}5ばなな22
5{0,1,1,2,2,3,4}4りんご23
6{0,1,2,3}3動物12
7{0,1,2,3,3,1,8}8ぞうさん31
8{0,1,2,3,3,2,7}7ありさん32

 

2013/03/02 21:59:02
id:timestep

おお、Postgresqlでできるのですね。ありがとうございます。この順番でばっちりです。

2013/03/05 13:49:14
id:tezcello No.2

tezcello回答回数460ベストアンサー獲得回数692013/03/01 23:19:35

ポイント33pt

> ソート順を設定したい
> トータルのソート順で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 クラスなどを駆使して...って事になるのでは?

id:timestep

わかりにくくてすみません。
こちらも上の回答と同じなのですが、動物と果物のsort順を入れ替えた時(sort_each_group_idの値を逆にしたとき)にありさんが4番目、ぞうさんが5番目になりません。

ソート順というのをうまく説明するのが難しいのですが、group_idが2のりんご、ばなな、めろんはid 2の果物に紐付いています。そして果物のsort_each_group_idが動物より後に来るような場合、果物に紐づくりんご、ばなな、めろんは、動物に紐づくありさん、ぞうさんよりも後に来る、という順番になります。

2013/03/02 00:06:33
id:timestep

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

2013/03/05 13:54:32
id:cno No.3

cno回答回数124ベストアンサー獲得回数122013/03/02 12:29:28

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

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

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

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

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


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

id:timestep

こちらだと解決しません

2013/03/05 13:55:10
id:suenaga3 No.4

suenaga3回答回数19ベストアンサー獲得回数52013/03/02 19:34:55ここでベストアンサー

ポイント33pt

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

初期設定部分は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;
}
?>
id:timestep

おお。ありがとうございます。汎用的に使えそうな方法ですね。こちらでやってみたいと思います。

2013/03/05 13:57:29
  • id:taknt
    http://db.just4fun.biz/MySQL/SELECT%E3%81%AE%E5%87%BA%E5%8A%9B%E3%81%AB%E9%80%A3%E7%95%AA%E3%82%92%E4%BB%98%E3%81%91%E3%82%8B%E6%96%B9%E6%B3%95.html
  • id:timestep
    トータルのソート順を設定したいのはその順番(トータルのソート順)でselectしたいためです。
    なので上記の方法では無理です。
    (そもそも望みどおりのselect順を取得できてないため。)
  • id:taknt
    森羅万象 1
    果物 2
    動物 3
    めろん 4
    ばなな 5
    りんご 6
    ぞうさん 7
    ありさん 8

    この数値は 上から順かと思うが、それ以外は 何順になっているのか わからない。

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

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

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

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