ここの情報は古いです。ご理解頂いた上でお取り扱いください。

Changeset 3655


Ignore:
Timestamp:
Aug 8, 2007, 3:11:45 PM (12 years ago)
Author:
ToshihiroK
Message:

postgres対応:トピック検索をpostgrres対応

File:
1 edited

Legend:

Unmodified
Added
Removed
  • OpenPNE/branches/prj/postgres/2_9_1/webapp/lib/db/commu.php

    r3651 r3655  
    29422942            $c_commu_id = 0)
    29432943{
    2944     $select = 'SELECT c.name AS commu_name, c.image_filename AS commu_image'
    2945             . ', ct.*, MAX(ctc2.r_datetime) AS last_datetime, MAX(ctc2.number) as max_number';
    2946     $from = ' FROM c_commu AS c, c_commu_topic AS ct, c_commu_topic_comment AS ctc, c_commu_topic_comment AS ctc2';
     2944    if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     2945        $select = 'SELECT distinct on (ct.c_commu_topic_id) c.name AS commu_name, c.image_filename AS commu_image'
     2946                . ', ct.*, ctc.r_datetime AS r_datetime2';
     2947    } else {
     2948        $select = 'SELECT c.name AS commu_name, c.image_filename AS commu_image'
     2949                . ', ct.*, ctc.r_datetime';
     2950    }
     2951
     2952    $from = ' FROM c_commu AS c, c_commu_topic AS ct, c_commu_topic_comment AS ctc';
    29472953
    29482954    $params = array();
    29492955    $where = ' WHERE ct.c_commu_topic_id = ctc.c_commu_topic_id'
    2950            . ' AND ct.c_commu_topic_id = ctc2.c_commu_topic_id'
    29512956           . ' AND c.c_commu_id = ct.c_commu_id';
    29522957    if ($c_commu_id) {
     
    29772982        break;
    29782983    }
    2979     $group = ' GROUP BY ct.c_commu_topic_id';
    2980     $order = ' ORDER BY last_datetime DESC';
    2981 
    2982     $sql = $select . $from . $where . $group . $order;
     2984
     2985    if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     2986        $order = ' ORDER BY sub_tbl.r_datetime DESC';
     2987        $sql = "SELECT sub_tbl.* FROM (" . $select . $from . $where . ") as sub_tbl " . $order;
     2988    } else {
     2989        $group = ' GROUP BY ct.c_commu_topic_id';
     2990        $order = ' ORDER BY ctc.r_datetime DESC';
     2991        $sql = $select . $from . $where . $group . $order;
     2992    }
     2993
    29832994    $list = db_get_all_page($sql, $page, $page_size, $params);
    2984    
     2995
    29852996    foreach ($list as $key => $value) {
    29862997        $p = array((int)$value['c_commu_topic_id']);
    29872998        $sql = 'SELECT body FROM c_commu_topic_comment WHERE number = 0 AND c_commu_topic_id = ?';
    29882999        $list[$key]['body'] = db_get_one($sql, $p);
     3000        $sql = 'SELECT MAX(number) FROM c_commu_topic_comment WHERE c_commu_topic_id = ?';
     3001        $list[$key]['max_number'] = db_get_one($sql, $p);
     3002        $sql = 'SELECT MAX(r_datetime) FROM c_commu_topic_comment WHERE c_commu_topic_id = ?';
     3003        $list[$key]['last_datetime'] = db_get_one($sql, $p);
    29893004    }
    29903005
Note: See TracChangeset for help on using the changeset viewer.