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

Changeset 9267


Ignore:
Timestamp:
Nov 20, 2008, 10:32:39 AM (14 years ago)
Author:
akamine
Message:

#1474 sql文の書き方を再度修正

File:
1 edited

Legend:

Unmodified
Added
Removed
  • OpenPNE/branches/stable-2.10.x/webapp/lib/db/commu.php

    r9247 r9267  
    516516
    517517    if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
    518         $sql = "SELECT cct.c_commu_topic_id , cct.name, MAX(cctc.r_datetime) as r_datetime , cct.c_commu_id";
    519         $sql .= " , max(cctc.image_filename1) as image_filename1, max(cctc.image_filename2) as image_filename2, max(cctc.image_filename3) as image_filename3 ";
    520         $sql .= " FROM c_commu_topic_comment as cctc , c_commu_topic as cct";
    521         $sql .= " WHERE cctc.c_commu_topic_id = cct.c_commu_topic_id ";
    522         $sql .= " AND cct.event_flag = ?";
    523         $sql .= " AND cct.c_commu_id = ?";
    524         $sql .= " group by cct.c_commu_topic_id, cct.name, cct.c_commu_id ";
    525         $sql .= " order by r_datetime desc ";
     518        $sql = "SELECT cct.c_commu_topic_id , cct.name, MAX(cctc.r_datetime) as r_datetime , cct.c_commu_id"
     519             . " , max(cctc.image_filename1) as image_filename1, max(cctc.image_filename2) as image_filename2, max(cctc.image_filename3) as image_filename3 "
     520             . " FROM c_commu_topic_comment as cctc , c_commu_topic as cct"
     521             . " WHERE cctc.c_commu_topic_id = cct.c_commu_topic_id "
     522             . " AND cct.event_flag = ?"
     523             . " AND cct.c_commu_id = ?"
     524             . " group by cct.c_commu_topic_id, cct.name, cct.c_commu_id "
     525             . " order by r_datetime desc ";
    526526    } else {
    527         $sql = "SELECT cct.c_commu_topic_id , cct.name, MAX(cctc.r_datetime) as r_datetime , cct.c_commu_id ";
    528         $sql .= " , cctc.image_filename1, cctc.image_filename2, cctc.image_filename3 ";
    529         $sql .= " FROM c_commu_topic_comment as cctc , c_commu_topic as cct";
    530         $sql .= " WHERE cctc.c_commu_topic_id = cct.c_commu_topic_id ";
    531         $sql .= " AND cct.event_flag = ?";
    532         $sql .= " AND cct.c_commu_id = ?";
    533         $sql .= " group by cct.c_commu_topic_id ";
    534         $sql .= " order by r_datetime desc ";
     527        $sql = "SELECT cct.c_commu_topic_id , cct.name, MAX(cctc.r_datetime) as r_datetime , cct.c_commu_id "
     528             . " , cctc.image_filename1, cctc.image_filename2, cctc.image_filename3 "
     529             . " FROM c_commu_topic_comment as cctc , c_commu_topic as cct"
     530             . " WHERE cctc.c_commu_topic_id = cct.c_commu_topic_id "
     531             . " AND cct.event_flag = ?"
     532             . " AND cct.c_commu_id = ?"
     533             . " group by cct.c_commu_topic_id "
     534             . " order by r_datetime desc ";
    535535    }
    536536    $params = array((bool)$event_flag, intval($c_commu_id));
     
    11741174    $hint = db_mysql_hint('USE INDEX (r_datetime_c_commu_id)');
    11751175    if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
    1176         $sql = 'SELECT sub_cct_tbl.c_commu_topic_id, cct.c_commu_id, sub_cct_tbl.r_datetime, cct.c_member_id';
    1177         $sql .= ' , max(cctc.image_filename1) as image_filename1, max(cctc.image_filename2) as image_filename2, max(cctc.image_filename3) as image_filename3';
    1178         $sql .= ' FROM (';
    1179         $sql .=     ' SELECT cct.c_commu_topic_id, MAX(cctc.r_datetime) as r_datetime';
    1180         $sql .=     ' FROM';
    1181         $sql .=         ' c_commu_topic as cct, c_commu_topic_comment as cctc'. $hint;
    1182         $sql .=     ' WHERE ';
    1183         $sql .=         ' cct.c_commu_id IN (' . $ids . ') AND cctc.c_commu_topic_id = cct.c_commu_topic_id';
    1184         $sql .=     ' GROUP BY cct.c_commu_topic_id';
    1185         $sql .=     ') as sub_cct_tbl, c_commu_topic_comment as cctc , c_commu_topic as cct';
    1186         $sql .= ' WHERE';
    1187         $sql .= ' cct.c_commu_topic_id=sub_cct_tbl.c_commu_topic_id';
    1188         $sql .= ' AND cctc.c_commu_topic_id = cct.c_commu_topic_id';
    1189         $sql .= ' AND cctc.r_datetime=sub_cct_tbl.r_datetime';
    1190         $sql .= ' ORDER BY r_datetime DESC';
     1176        $sql = 'SELECT sub_cct_tbl.c_commu_topic_id, cct.c_commu_id, sub_cct_tbl.r_datetime, cct.c_member_id'
     1177             . ' , max(cctc.image_filename1) as image_filename1, max(cctc.image_filename2) as image_filename2, max(cctc.image_filename3) as image_filename3'
     1178             . ' FROM ('
     1179             .     ' SELECT cct.c_commu_topic_id, MAX(cctc.r_datetime) as r_datetime'
     1180             .     ' FROM'
     1181             .         ' c_commu_topic as cct, c_commu_topic_comment as cctc'. $hint
     1182             .     ' WHERE '
     1183             .         ' cct.c_commu_id IN (' . $ids . ') AND cctc.c_commu_topic_id = cct.c_commu_topic_id'
     1184             .     ' GROUP BY cct.c_commu_topic_id'
     1185             .     ') as sub_cct_tbl, c_commu_topic_comment as cctc , c_commu_topic as cct'
     1186             . ' WHERE'
     1187             . ' cct.c_commu_topic_id=sub_cct_tbl.c_commu_topic_id'
     1188             . ' AND cctc.c_commu_topic_id = cct.c_commu_topic_id'
     1189             . ' AND cctc.r_datetime=sub_cct_tbl.r_datetime'
     1190             . ' ORDER BY r_datetime DESC';
    11911191    } else {
    1192         $sql = 'SELECT cct.c_commu_topic_id, cct.c_commu_id, MAX(cctc.r_datetime) as r_datetime, cct.c_member_id';
    1193         $sql .= ' , cctc.image_filename1, cctc.image_filename2, cctc.image_filename3 ';
    1194         $sql .= ' FROM c_commu_topic as cct, c_commu_topic_comment as cctc'. $hint;
    1195         $sql .= ' WHERE cct.c_commu_id IN (' . $ids . ') AND cctc.c_commu_topic_id = cct.c_commu_topic_id';
    1196         $sql .= ' GROUP BY cctc.c_commu_topic_id';
    1197         $sql .= ' ORDER BY r_datetime DESC';
     1192        $sql = 'SELECT cct.c_commu_topic_id, cct.c_commu_id, MAX(cctc.r_datetime) as r_datetime, cct.c_member_id'
     1193             . ' , cctc.image_filename1, cctc.image_filename2, cctc.image_filename3 '
     1194             . ' FROM c_commu_topic as cct, c_commu_topic_comment as cctc'. $hint
     1195             . ' WHERE cct.c_commu_id IN (' . $ids . ') AND cctc.c_commu_topic_id = cct.c_commu_topic_id'
     1196             . ' GROUP BY cctc.c_commu_topic_id'
     1197             . ' ORDER BY r_datetime DESC';
    11981198    }
    11991199    $c_commu_topic_list = db_get_all_limit($sql, 0, $limit);
     
    12031203        $c_commu_topic_list[$key]['nickname'] = $c_member['nickname'];
    12041204
    1205         $sql = 'SELECT number FROM c_commu_topic_comment';
    1206         $sql .= ' WHERE c_commu_topic_id = ? AND r_datetime = ?';
     1205        $sql = 'SELECT number FROM c_commu_topic_comment'
     1206             . ' WHERE c_commu_topic_id = ? AND r_datetime = ?';
    12071207        $params = array(intval($value['c_commu_topic_id']), $value['r_datetime']);
    12081208        $temp = db_get_row($sql, $params);
     
    12361236    $hint = db_mysql_hint('USE INDEX (r_datetime_c_commu_id)');
    12371237    if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
    1238         $sql = 'SELECT sub_cct_tbl.c_commu_topic_id, cct.c_commu_id, sub_cct_tbl.r_datetime, cct.c_member_id';
    1239         $sql .= ' , max(cctc.image_filename1) as image_filename1, max(cctc.image_filename2) as image_filename2, max(cctc.image_filename3) as image_filename3 ';
    1240         $sql .= ' FROM (';
    1241         $sql .=     ' SELECT cct.c_commu_topic_id, MAX(cctc.r_datetime) as r_datetime';
    1242         $sql .=     ' FROM';
    1243         $sql .=         ' c_commu_topic as cct, c_commu_topic_comment as cctc'. $hint;
    1244         $sql .=     ' WHERE ';
    1245         $sql .=         ' cct.c_commu_id IN (' . $ids . ') AND cctc.c_commu_topic_id = cct.c_commu_topic_id';
    1246         $sql .=     ' GROUP BY cct.c_commu_topic_id';
    1247         $sql .=     ') as sub_cct_tbl, c_commu_topic_comment as cctc , c_commu_topic as cct';
    1248         $sql .= ' WHERE';
    1249         $sql .= ' cct.c_commu_topic_id=sub_cct_tbl.c_commu_topic_id';
    1250         $sql .= ' AND cctc.c_commu_topic_id = cct.c_commu_topic_id ';
    1251         $sql .= ' AND cctc.r_datetime=sub_cct_tbl.r_datetime';
    1252         $sql .= ' ORDER BY r_datetime DESC';
     1238        $sql = 'SELECT sub_cct_tbl.c_commu_topic_id, cct.c_commu_id, sub_cct_tbl.r_datetime, cct.c_member_id'
     1239             . ' , max(cctc.image_filename1) as image_filename1, max(cctc.image_filename2) as image_filename2, max(cctc.image_filename3) as image_filename3 '
     1240             . ' FROM ('
     1241             .     ' SELECT cct.c_commu_topic_id, MAX(cctc.r_datetime) as r_datetime'
     1242             .     ' FROM'
     1243             .         ' c_commu_topic as cct, c_commu_topic_comment as cctc'. $hint
     1244             .     ' WHERE '
     1245             .         ' cct.c_commu_id IN (' . $ids . ') AND cctc.c_commu_topic_id = cct.c_commu_topic_id'
     1246             .     ' GROUP BY cct.c_commu_topic_id'
     1247             .     ') as sub_cct_tbl, c_commu_topic_comment as cctc , c_commu_topic as cct'
     1248             . ' WHERE'
     1249             . ' cct.c_commu_topic_id=sub_cct_tbl.c_commu_topic_id'
     1250             . ' AND cctc.c_commu_topic_id = cct.c_commu_topic_id '
     1251             . ' AND cctc.r_datetime=sub_cct_tbl.r_datetime'
     1252             . ' ORDER BY r_datetime DESC';
    12531253    } else {
    1254         $sql = 'SELECT cct.c_commu_topic_id, cct.c_commu_id, MAX(cctc.r_datetime) as r_datetime, cct.c_member_id';
    1255         $sql .= ' , cctc.image_filename1, cctc.image_filename2, cctc.image_filename3 ';
    1256         $sql .= ' FROM c_commu_topic as cct, c_commu_topic_comment as cctc'. $hint;
    1257         $sql .= ' WHERE cct.c_commu_id IN (' . $ids . ') AND cctc.c_commu_topic_id = cct.c_commu_topic_id';
    1258         $sql .= ' GROUP BY cctc.c_commu_topic_id';
    1259         $sql .= ' ORDER BY r_datetime DESC';
     1254        $sql = 'SELECT cct.c_commu_topic_id, cct.c_commu_id, MAX(cctc.r_datetime) as r_datetime, cct.c_member_id'
     1255             . ' , cctc.image_filename1, cctc.image_filename2, cctc.image_filename3 '
     1256             . ' FROM c_commu_topic as cct, c_commu_topic_comment as cctc'. $hint
     1257             . ' WHERE cct.c_commu_id IN (' . $ids . ') AND cctc.c_commu_topic_id = cct.c_commu_topic_id'
     1258             . ' GROUP BY cctc.c_commu_topic_id'
     1259             . ' ORDER BY r_datetime DESC';
    12601260    }
    12611261    $c_commu_topic_list = db_get_all_limit($sql, ($page-1)*$limit, $limit);
     
    12641264        $c_commu_topic_list[$key]['nickname'] = $c_member['nickname'];
    12651265
    1266         $sql = 'SELECT number FROM c_commu_topic_comment ';
    1267         $sql .= ' WHERE c_commu_topic_id = ? AND r_datetime = ?';
     1266        $sql = 'SELECT number FROM c_commu_topic_comment '
     1267             . ' WHERE c_commu_topic_id = ? AND r_datetime = ?';
    12681268        $params = array(intval($value['c_commu_topic_id']), $value['r_datetime']);
    12691269        $temp = db_get_row($sql, $params);
     
    12831283    }
    12841284
    1285     $sql = "SELECT count(DISTINCT ct.c_commu_topic_id)";
    1286     $sql .= " FROM c_commu_member AS cm, c_commu_topic_comment AS cc";
    1287     $sql .= ", c_commu AS c, c_commu_topic AS ct";
    1288     $sql .= " WHERE cm.c_member_id = ?";
    1289     $sql .= " AND cc.c_commu_id=cm.c_commu_id";
    1290     $sql .= " AND c.c_commu_id=cm.c_commu_id";
    1291     $sql .= " AND ct.c_commu_id=cm.c_commu_id";
    1292     $sql .= " AND ct.c_commu_topic_id=cc.c_commu_topic_id";
     1285    $sql = "SELECT count(DISTINCT ct.c_commu_topic_id)"
     1286         . " FROM c_commu_member AS cm, c_commu_topic_comment AS cc"
     1287         . ", c_commu AS c, c_commu_topic AS ct"
     1288         . " WHERE cm.c_member_id = ?"
     1289         . " AND cc.c_commu_id=cm.c_commu_id"
     1290         . " AND c.c_commu_id=cm.c_commu_id"
     1291         . " AND ct.c_commu_id=cm.c_commu_id"
     1292         . " AND ct.c_commu_topic_id=cc.c_commu_topic_id";
    12931293    $params = array(intval($c_member_id));
    12941294    $total_num = db_get_one($sql, $params);
Note: See TracChangeset for help on using the changeset viewer.