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

Changeset 1667


Ignore:
Timestamp:
Jan 18, 2007, 4:18:01 PM (16 years ago)
Author:
ebihara
Message:

新着コミュニティコメントリスト取得のSQL文分割関連の変更を取り込み

File:
1 edited

Legend:

Unmodified
Added
Removed

  • OpenPNE/branches/ebihara/prj_dev27_tuning/webapp/lib/db/commu.php

    r1262 r1667  
    797797function db_commu_c_commu_topic_comment_list4c_member_id($c_member_id, $limit)
    798798{
    799     $sql = "SELECT cc.c_commu_topic_id, c.name AS c_commu_name, ct.name AS c_commu_topic_name , cm.c_member_id, cc.number, max(cc.r_datetime) as r_datetime";
    800     $sql .= " FROM c_commu_member AS cm, c_commu_topic_comment AS cc";
    801     $sql .= ", c_commu AS c, c_commu_topic AS ct";
    802     $sql .= " WHERE cm.c_member_id = ?";
    803     $sql .= " AND cc.c_commu_id=cm.c_commu_id";
    804     $sql .= " AND c.c_commu_id=cm.c_commu_id";
    805     $sql .= " AND ct.c_commu_id=cm.c_commu_id";
    806     $sql .= " AND ct.c_commu_topic_id=cc.c_commu_topic_id";
    807     $sql .= " GROUP BY c_commu_topic_id, c_commu_name, c_commu_topic_name ,c_member_id ";
    808     $sql .= " ORDER BY r_datetime DESC";
    809     $params = array(intval($c_member_id));
    810     $c_commu_topic_list = db_get_all_limit($sql, 0, $limit, $params);
     799    $sql = 'SELECT c_commu_id FROM c_commu_member WHERE c_member_id = ?';
     800    $c_commu_id_list = db_get_col($sql, array(intval($c_member_id)));
     801    $ids = implode(", ", $c_commu_id_list);
     802
     803    $hint = db_mysql_hint('USE INDEX (r_datetime_c_commu_id)');
     804    $sql = 'SELECT c_commu_id, c_commu_topic_id, name AS c_commu_topic_name, r_datetime, c_member_id'.
     805        ' FROM c_commu_topic'. $hint . ' WHERE c_commu_id IN (' . $ids . ') ORDER BY r_datetime DESC';
     806    $c_commu_topic_list = db_get_all_limit($sql, 0, $limit);
    811807
    812808    foreach ($c_commu_topic_list as $key => $value) {
    … …  
    820816        $temp = db_get_row($sql, $params);
    821817
     818        $sql = 'SELECT name AS c_commu_name FROM c_commu WHERE c_commu_id = ?';
     819        $c_commu_name = db_get_one($sql, $value['c_commu_id']);
     820
    822821        //最新の書き込み番号
    823822        $number = db_commu_get_max_number4topic($value['c_commu_topic_id']);
    … …  
    827826        $c_commu_topic_list[$key]['image_filename2'] = $temp['image_filename2'];
    828827        $c_commu_topic_list[$key]['image_filename3'] = $temp['image_filename3'];
     828        $c_commu_topic_list[$key]['c_commu_name'] = $c_commu_name;
    829829    }
    830830
    … …  
    834834function db_commu_c_commu_topic_comment_list4c_member_id_2($c_member_id, $limit,$page)
    835835{
    836     $sql = "SELECT cc.c_commu_topic_id, c.name AS c_commu_name, ct.name AS c_commu_topic_name , cm.c_member_id, cc.number, max(cc.r_datetime) as r_datetime";
    837     $sql .= " FROM c_commu_member AS cm, c_commu_topic_comment AS cc";
    838     $sql .= ", c_commu AS c, c_commu_topic AS ct";
    839     $sql .= " WHERE cm.c_member_id = ?";
    840     $sql .= " AND cc.c_commu_id=cm.c_commu_id";
    841     $sql .= " AND c.c_commu_id=cm.c_commu_id";
    842     $sql .= " AND ct.c_commu_id=cm.c_commu_id";
    843     $sql .= " AND ct.c_commu_topic_id=cc.c_commu_topic_id";
    844     $sql .= " group by c_commu_topic_id, c_commu_name, c_commu_topic_name ,c_member_id ";
    845     $sql .= " ORDER BY r_datetime DESC";
    846     $params = array(intval($c_member_id));
    847     $c_commu_topic_list = db_get_all_limit($sql, ($page-1)*$limit, $limit, $params);
     836    $sql = 'SELECT c_commu_id FROM c_commu_member WHERE c_member_id = ?';
     837    $c_commu_id_list = db_get_col($sql, array(intval($c_member_id)));
     838    $ids = implode(", ", $c_commu_id_list);
     839
     840    $hint = db_mysql_hint('USE INDEX (r_datetime_c_commu_id)');
     841    $sql = 'SELECT c_commu_id, c_commu_topic_id, name AS c_commu_topic_name, r_datetime, c_member_id'.
     842        ' FROM c_commu_topic'. $hint . ' WHERE c_commu_id IN (' . $ids . ') ORDER BY r_datetime DESC';
     843    $c_commu_topic_list = db_get_all_limit($sql, ($page-1)*$limit, $limit);
    848844
    849845    foreach ($c_commu_topic_list as $key => $value) {
    … …  
    857853        $temp = db_get_row($sql, $params);
    858854
     855        $sql = 'SELECT name AS c_commu_name FROM c_commu WHERE c_commu_id = ?';
     856        $c_commu_name = db_get_one($sql, $value['c_commu_id']);
     857
    859858        //最新の書き込み番号
    860859        $number = db_commu_get_max_number4topic($value['c_commu_topic_id']);
    … …  
    864863        $c_commu_topic_list[$key]['image_filename2'] = $temp['image_filename2'];
    865864        $c_commu_topic_list[$key]['image_filename3'] = $temp['image_filename3'];
     865        $c_commu_topic_list[$key]['c_commu_name'] = $c_commu_name;
    866866    }
    867867
    … …  
    14821482function db_commu_c_commu_topic_comment_list4c_member_id_3($c_member_id, $page_size, $page)
    14831483{
    1484     $sql = "SELECT cc.c_commu_topic_id, c.name AS c_commu_name, ct.name AS c_commu_topic_name , cm.c_member_id, cc.number, max(cc.r_datetime) as r_datetime";
    1485     $sql .= " FROM c_commu_member AS cm, c_commu_topic_comment AS cc";
    1486     $sql .= ", c_commu AS c, c_commu_topic AS ct";
    1487     $sql .= " WHERE cm.c_member_id = ?";
    1488     $sql .= " AND cc.c_commu_id = cm.c_commu_id";
    1489     $sql .= " AND c.c_commu_id = cm.c_commu_id";
    1490     $sql .= " AND ct.c_commu_id = cm.c_commu_id";
    1491     $sql .= " AND ct.c_commu_topic_id = cc.c_commu_topic_id";
    1492     $sql .= " group by c_commu_topic_id, c_commu_name, c_commu_topic_name ,c_member_id ";
    1493     $sql .= " ORDER BY r_datetime DESC";
    1494 
    1495     $params = array(intval($c_member_id));
    1496     $c_commu_topic_list = db_get_all_page($sql, $page, $page_size, $params);
     1484    $sql = 'SELECT c_commu_id FROM c_commu_member WHERE c_member_id = ?';
     1485    $c_commu_id_list = db_get_col($sql, array(intval($c_member_id)));
     1486    $ids = implode(", ", $c_commu_id_list);
     1487
     1488    $hint = db_mysql_hint('USE INDEX (r_datetime_c_commu_id)');
     1489    $sql = 'SELECT c_commu_id, c_commu_topic_id, name AS c_commu_topic_name, r_datetime, c_member_id'.
     1490        ' FROM c_commu_topic'. $hint . ' WHERE c_commu_id IN (' . $ids . ') ORDER BY r_datetime DESC';
     1491    $c_commu_topic_list = db_get_all_page($sql, $page, $page_size);
    14971492
    14981493    foreach ($c_commu_topic_list as $key => $value) {
     1494        $sql = 'SELECT name AS c_commu_name FROM c_commu WHERE c_commu_id = ?';
     1495        $c_commu_name = db_get_one($sql, $value['c_commu_id']);
     1496
    14991497        $c_commu_topic_list[$key]['number'] = db_commu_get_max_number4topic($value['c_commu_topic_id']);
     1498        $c_commu_topic_list[$key]['c_commu_name'] = $c_commu_name;
    15001499    }
    15011500
Note: See TracChangeset for help on using the changeset viewer.