PR

CakePHP 複雑なSQLクエリ

2009.10.31

複雑なクエリを実行したいのだが悩む･･･。

スポンサードリンク

初回に書いたもの

function _getSelectSections() {
return $this->SystemSection->find(
‘list’,
array(
‘fields’ => array(‘SystemSection.id’, ‘SystemSection.name’),
‘conditions’=> array(‘SystemSection.active =’ => 1),
‘order’  => array(‘SystemSection.yomi ASC’),
)
);
}

ちょっと変更した

function _getSelectSections() {
$db =& ConnectionManager::getDataSource(‘default’);
$prefix = $db->config[‘prefix’];
$sql = “SELECT * FROM(
SELECT
IFNULL(LV3.id, IFNULL(LV2.id, LV1.id)) as id,
IFNULL(LV3.name, IFNULL(LV2.name, LV1.name)) as name
FROM
${prefix}system_sections as LV1
LEFT JOIN
${prefix}system_sections as LV2 ON LV2.parent = LV1.id
LEFT JOIN
${prefix}system_sections as LV3 ON LV3.parent = LV2.id
where
LV1.active = 1
order by
LV3.`index` ASC,
id ASC
) as SystemSection”;
$results = $this->SystemSection->query($sql);

// データの整理
foreach ($results as $result){
$rr[$result[‘SystemSection’][‘id’]] = $result[‘SystemSection’][‘name’];
}
// debug($rr);
return $rr;
}

function _getSectionList($mode = null){
// id, name, panName
// 1, hoge, hoge>hogehoge
$db =& ConnectionManager::getDataSource(‘default’);
$prefix = $db->config[‘prefix’];

$sql = ”
SELECT
IFNULL(LV3.id, IFNULL(LV2.id, LV1.id)) as id,
IFNULL(LV3.name, IFNULL(LV2.name, LV1.name)) as name,
concat_ws(\” > \”, LV1.name, LV2.name, LV3.name) as panName
FROM
${prefix}system_sections as LV1
LEFT JOIN
${prefix}system_sections as LV2 ON LV2.parent = LV1.id
LEFT JOIN
${prefix}system_sections as LV3 ON LV3.parent = LV2.id
where
LV1.active = 1
order by
LV3.`index` ASC,
id ASC
“;

$results = $this->SystemSection->query($sql);

// データの整理
foreach ($results as $result){
// data
$id = $result[0][‘id’];
$rr[$id] = array(
‘panName’ => $result[0][‘panName’],
‘name’  => $result[0][‘name’]
);
}
return $rr;
}

さらに変更

function _getSectionList2($type = null) {
// get database config => ‘prefix_’
$prefix = $this->_getPrefix();

// setting options
$options = array(
‘fields’ => array(
‘IFNULL(LV3.id, IFNULL(LV2.id, SystemSection.id)) as id’,
‘IFNULL(LV3.name, IFNULL(LV2.name, SystemSection.name)) as name’
),
‘conditions’=> array(
‘SystemSection.active =’ => 1
),
// ‘group’  => ‘SystemSection.id’,
‘order’  => array(
‘LV3.index ASC’,
‘SystemSection.id ASC’
),
);

// setting joins
$options[‘joins’][] = array(
‘type’  => ‘LEFT’,
‘table’  => $prefix . ‘system_sections’,
‘alias’  => ‘LV2’,
‘conditions’=> ‘LV2.parent = SystemSection.id’
);
$options[‘joins’][] = array(
‘type’  => ‘LEFT’,
‘table’  => $prefix . ‘system_sections’,
‘alias’  => ‘LV3’,
‘conditions’=> ‘LV3.parent = LV2.id’
);

// get data (can not get the LIST mode)
$results = $this->SystemSection->find(“all”, $options);
// debug($results);

if ($type == ‘list’){
// convert array, ALL to LIST
// LIST: array( [id] => [name], [id] => [name], … )
foreach ($results as $result){
$rr[ $result[0][‘id’] ] = $result[0][‘name’];
}
}else{
// convert default find array
foreach ($results as $result){
$rr[ $result[0][‘id’] ] = $result[0];
}
}
//debug($rr);

// return
return $rr;
}

どう見ても良くない･･･。

参考文献

参考文献（SQL）

  • データを保存する :: モデル :: CakePHPによる開発 :: マニュアル :: 1.2 Collection :: The Cookbook :
  • http://book.cakephp.org/ja/view/75/Saving-Your-Data
  • 複雑な find の条件 :: データを取得する :: モデル :: CakePHPによる開発 :: マニュアル :: 1.2 Collection :: The Cookbook :
  • http://book.cakephp.org/ja/compare/74/Complex-Find-Conditions
  • CakePHP find で max や min を使いたい – foldrrの日記 :
  • http://d.hatena.ne.jp/foldrr/20090708/1247038309
  • ひでぶろぐ CAKEPHPcakephp で使う sql 用 max 関数 :
  • http://www.hideblog.net/nikkis/show/32

MAX/MIN関数を使う

  • $this->Model->find(‘all’,array(‘fields’=>array(‘MAX(id)’)));

参考文献（複数テーブル）

スポンサーリンク

コメント