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

Changeset 1659


Ignore:
Timestamp:
Jan 17, 2007, 9:07:36 PM (13 years ago)
Author:
ebihara
Message:

新着コミュニティコメントリスト取得のSQL文を分割

Location:
OpenPNE/branches/ebihara/prj_tuning
Files:
1 added
1 edited

Legend:

Unmodified
Added
Removed
  • OpenPNE/branches/ebihara/prj_tuning/webapp/lib/db/commu.php

    r864 r1659  
    692692function db_commu_c_commu_topic_comment_list4c_member_id($c_member_id, $limit)
    693693{
    694     $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";
    695     $sql .= " FROM c_commu_member AS cm, c_commu_topic_comment AS cc";
    696     $sql .= ", c_commu AS c, c_commu_topic AS ct";
    697     $sql .= " WHERE cm.c_member_id = ?";
    698     $sql .= " AND cc.c_commu_id=cm.c_commu_id";
    699     $sql .= " AND c.c_commu_id=cm.c_commu_id";
    700     $sql .= " AND ct.c_commu_id=cm.c_commu_id";
    701     $sql .= " AND ct.c_commu_topic_id=cc.c_commu_topic_id";
    702     $sql .= " GROUP BY c_commu_topic_id, c_commu_name, c_commu_topic_name ,c_member_id ";
    703     $sql .= " ORDER BY r_datetime DESC";
    704     $params = array(intval($c_member_id));
    705     $c_commu_topic_list = db_get_all_limit($sql, 0, $limit, $params);
     694    $sql = 'SELECT c_commu_id FROM c_commu_member WHERE c_member_id = ?';
     695    $c_commu_id_list = db_get_col($sql, array(intval($c_member_id)));
     696    $ids = implode(", ", $c_commu_id_list);
     697
     698    $hint = db_mysql_hint('USE INDEX (r_datetime_c_commu_id)');
     699    $sql = 'SELECT c_commu_id, c_commu_topic_id, name AS c_commu_topic_name, r_datetime, c_member_id'.
     700        ' FROM c_commu_topic'. $hint . ' WHERE c_commu_id IN (' . $ids . ') ORDER BY r_datetime DESC';
     701    $c_commu_topic_list = db_get_all_limit($sql, 0, $limit);
    706702
    707703    foreach ($c_commu_topic_list as $key => $value) {
     
    715711        $temp = db_get_row($sql, $params);
    716712
     713        $sql = 'SELECT name AS c_commu_name FROM c_commu WHERE c_commu_id = ?';
     714        $c_commu_name = db_get_one($sql, $value['c_commu_id']);
     715
    717716        //最新の書き込み番号
    718717        $number = db_commu_get_max_number4topic($value['c_commu_topic_id']);
     
    722721        $c_commu_topic_list[$key]['image_filename2'] = $temp['image_filename2'];
    723722        $c_commu_topic_list[$key]['image_filename3'] = $temp['image_filename3'];
     723        $c_commu_topic_list[$key]['c_commu_name'] = $c_commu_name;
    724724    }
    725725
     
    729729function db_commu_c_commu_topic_comment_list4c_member_id_2($c_member_id, $limit,$page)
    730730{
    731     $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";
    732     $sql .= " FROM c_commu_member AS cm, c_commu_topic_comment AS cc";
    733     $sql .= ", c_commu AS c, c_commu_topic AS ct";
    734     $sql .= " WHERE cm.c_member_id = ?";
    735     $sql .= " AND cc.c_commu_id=cm.c_commu_id";
    736     $sql .= " AND c.c_commu_id=cm.c_commu_id";
    737     $sql .= " AND ct.c_commu_id=cm.c_commu_id";
    738     $sql .= " AND ct.c_commu_topic_id=cc.c_commu_topic_id";
    739     $sql .= " group by c_commu_topic_id, c_commu_name, c_commu_topic_name ,c_member_id ";
    740     $sql .= " ORDER BY r_datetime DESC";
    741     $params = array(intval($c_member_id));
    742     $c_commu_topic_list = db_get_all_limit($sql, ($page-1)*$limit, $limit, $params);
     731    $sql = 'SELECT c_commu_id FROM c_commu_member WHERE c_member_id = ?';
     732    $c_commu_id_list = db_get_col($sql, array(intval($c_member_id)));
     733    $ids = implode(", ", $c_commu_id_list);
     734
     735    $hint = db_mysql_hint('USE INDEX (r_datetime_c_commu_id)');
     736    $sql = 'SELECT c_commu_id, c_commu_topic_id, name AS c_commu_topic_name, r_datetime, c_member_id'.
     737        ' FROM c_commu_topic'. $hint . ' WHERE c_commu_id IN (' . $ids . ') ORDER BY r_datetime DESC';
     738    $c_commu_topic_list = db_get_all_limit($sql, ($page-1)*$limit, $limit);
    743739
    744740    foreach ($c_commu_topic_list as $key => $value) {
     
    752748        $temp = db_get_row($sql, $params);
    753749
     750        $sql = 'SELECT name AS c_commu_name FROM c_commu WHERE c_commu_id = ?';
     751        $c_commu_name = db_get_one($sql, $value['c_commu_id']);
     752
    754753        //最新の書き込み番号
    755754        $number = db_commu_get_max_number4topic($value['c_commu_topic_id']);
     
    759758        $c_commu_topic_list[$key]['image_filename2'] = $temp['image_filename2'];
    760759        $c_commu_topic_list[$key]['image_filename3'] = $temp['image_filename3'];
     760        $c_commu_topic_list[$key]['c_commu_name'] = $c_commu_name;
    761761    }
    762762
     
    13781378function db_commu_c_commu_topic_comment_list4c_member_id_3($c_member_id, $page_size, $page)
    13791379{
    1380     $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";
    1381     $sql .= " FROM c_commu_member AS cm, c_commu_topic_comment AS cc";
    1382     $sql .= ", c_commu AS c, c_commu_topic AS ct";
    1383     $sql .= " WHERE cm.c_member_id = ?";
    1384     $sql .= " AND cc.c_commu_id = cm.c_commu_id";
    1385     $sql .= " AND c.c_commu_id = cm.c_commu_id";
    1386     $sql .= " AND ct.c_commu_id = cm.c_commu_id";
    1387     $sql .= " AND ct.c_commu_topic_id = cc.c_commu_topic_id";
    1388     $sql .= " group by c_commu_topic_id, c_commu_name, c_commu_topic_name ,c_member_id ";
    1389     $sql .= " ORDER BY r_datetime DESC";
    1390 
    1391     $params = array(intval($c_member_id));
    1392     $c_commu_topic_list = db_get_all_page($sql, $page, $page_size, $params);
     1380    $sql = 'SELECT c_commu_id FROM c_commu_member WHERE c_member_id = ?';
     1381    $c_commu_id_list = db_get_col($sql, array(intval($c_member_id)));
     1382    $ids = implode(", ", $c_commu_id_list);
     1383
     1384    $hint = db_mysql_hint('USE INDEX (r_datetime_c_commu_id)');
     1385    $sql = 'SELECT c_commu_id, c_commu_topic_id, name AS c_commu_topic_name, r_datetime, c_member_id'.
     1386        ' FROM c_commu_topic'. $hint . ' WHERE c_commu_id IN (' . $ids . ') ORDER BY r_datetime DESC';
     1387    $c_commu_topic_list = db_get_all_page($sql, $page, $page_size);
    13931388
    13941389    foreach ($c_commu_topic_list as $key => $value) {
     1390        $sql = 'SELECT name AS c_commu_name FROM c_commu WHERE c_commu_id = ?';
     1391        $c_commu_name = db_get_one($sql, $value['c_commu_id']);
     1392
    13951393        $c_commu_topic_list[$key]['number'] = db_commu_get_max_number4topic($value['c_commu_topic_id']);
     1394        $c_commu_topic_list[$key]['c_commu_name'] = $c_commu_name;
    13961395    }
    13971396
Note: See TracChangeset for help on using the changeset viewer.