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

Opened 13 years ago

Closed 13 years ago

#511 closed defect (fixed)

h_homeとかのコミュトピック最新リストのソートが最新コミュトピックコメント順でない

Reported by: kunitada Owned by: kunitada
Priority: critical Milestone: OpenPNE2.7.2
Component: core【OpenPNEコア部分】 Version:
Keywords: Cc:

Description

http://www.openpne.jp/ticket/365 から移動。


■現象
h_homeとかのコミュトピック最新リストのソートが最新コミュトピックコメント順でない。

■再現ブランチ
http://trac.openpne.jp/browser/OpenPNE/branches/prj_tuning [1915]以上
http://trac.openpne.jp/log/OpenPNE/trunk [1891]以上

■対象関数
db_commu_c_commu_topic_comment_list4c_member_id
db_commu_c_commu_topic_comment_list4c_member_id_2
db_commu_c_commu_topic_comment_list4c_member_id_3

■原因
【元のソース】
    $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";
    $sql .= " FROM c_commu_member AS cm, c_commu_topic_comment AS cc";
    $sql .= ", c_commu AS c, c_commu_topic AS ct";
    $sql .= " WHERE cm.c_member_id = ?";
    $sql .= " AND cc.c_commu_id=cm.c_commu_id";
    $sql .= " AND c.c_commu_id=cm.c_commu_id";
    $sql .= " AND ct.c_commu_id=cm.c_commu_id";
    $sql .= " AND ct.c_commu_topic_id=cc.c_commu_topic_id";
    $sql .= " GROUP BY c_commu_topic_id, c_commu_name, c_commu_topic_name ,c_member_id ";
    $sql .= " ORDER BY r_datetime DESC";
    $params = array(intval($c_member_id));
    $c_commu_topic_list = db_get_all_limit($sql, 0, $limit, $params);

【不具合ソース】
    $sql = 'SELECT c_commu_id FROM c_commu_member WHERE c_member_id = ?';
    $c_commu_id_list = db_get_col($sql, array(intval($c_member_id)));
    $ids = implode(", ", $c_commu_id_list);

    $hint = db_mysql_hint('USE INDEX (r_datetime_c_commu_id)');
    $sql = 'SELECT c_commu_id, c_commu_topic_id, name AS c_commu_topic_name, r_datetime, c_member_id'.
    	' FROM c_commu_topic'. $hint . ' WHERE c_commu_id IN (' . $ids . ') ORDER BY r_datetime DESC';
    $c_commu_topic_list = db_get_all_limit($sql, 0, $limit);

■解決策
1)
c_commu_topic_comment.r_datetime
でソートする。

2)
ALTER TABLE c_commu_topic ADD INDEX r_datetime_c_commu_id(r_datetime, c_commu_id);
を適切なindexに変える。
ALTER TABLE c_commu_topic_comment ADD INDEX r_datetime_c_commu_id(r_datetime, c_commu_id);
とかかな?

■希望解決期限
2/26中とかだとうれしいですー

Change History (1)

comment:1 Changed 13 years ago by takanashi

Milestone: OpenPNE2.7.4OpenPNE2.7.2
Resolution: fixed
Status: newclosed

r2040で抜本対処完了

Note: See TracTickets for help on using tickets.