スポンサーリンク
スポンサーリンク

Cocoonで注目記事ランキングのショートコードを作成した【SQLって何?からのデータベース操作】

ニッチな話題プログラミングホームページ運用

記事のアクセスランキング(人気記事)は、上位の記事がなかなか変わらないので、あまりおもしろくありません。

WordPressの記事のアクセス数の前週比のランキング(急上昇の記事)を作りたいと思って、Cocoonのコードを読んでみました。

Cocoonのアクセスランキングは、SQLクエリで生成されているので、SQLについてまとめました。

スポンサーリンク

Cocoonのget_access_ranking_recordsのSQLクエリ

Cocoonのアクセスランキングは、「lib/page-access/access-func.php」の「function get_access_ranking_records()」に取得クエリがあります。

人気記事のショートコードの生成コードから、関数をたどることができました。

人気記事のショートコードから関数をたどる

カテゴリー・タグの抽出をする場合。

    $query = "
      SELECT {$joined_table}.post_id, SUM({$joined_table}.count) AS sum_count, {$joined_table}.term_taxonomy_id, {$joined_table}.taxonomy
        FROM (
          #カテゴリとアクセステーブルを内部結合してグルーピングし並び替えた結果
          SELECT {$access_table}.post_id, {$access_table}.count, {$term_relationships}.term_taxonomy_id, {$term_taxonomy}.taxonomy
            FROM {$term_relationships}
            INNER JOIN {$access_table} ON {$term_relationships}.object_id = {$access_table}.post_id
            INNER JOIN {$term_taxonomy} ON {$term_relationships}.term_taxonomy_id = {$term_taxonomy}.term_taxonomy_id
            $where #WHERE句
            GROUP BY {$access_table}.id
        ) AS {$joined_table} #カテゴリとアクセステーブルを内部結合した仮の名前
        GROUP BY {$joined_table}.post_id
        ORDER BY sum_count DESC
    ";

PHPコードの中に、SQLクエリがあります。

ややこしいですね💦

カテゴリー・タグ抽出しないアクセスランキングの場合はもっとシンプルです。

    $query = "
      SELECT {$access_table}.post_id, SUM({$access_table}.count) AS sum_count
        FROM {$access_table} $where
        GROUP BY {$access_table}.post_id
        ORDER BY sum_count DESC
    ";

これだけみると、シンプルですが、$whereの中身の解読も必要です。

  $where = " WHERE {$access_table}.post_type = '$post_type' ".PHP_EOL;
  if ($days != 'all') {
    $date_before = get_current_db_date_before($days);
    $where .= " AND {$access_table}.date BETWEEN '$date_before' AND '$date' ".PHP_EOL;
  }
  if ($days == 1) {
    $where .= " AND {$access_table}.date = '$date' ".PHP_EOL;
  }
  //_v($exclude_post_ids);
  // _v($exclude_post_ids[0]);
  if (is_ids_exist($exclude_post_ids)) {
    $where .= " AND {$access_table}.post_id NOT IN(".implode(',', $exclude_post_ids).") ".PHP_EOL;
  }

まず記事で絞り込んで、アクセスランキングの取得範囲によって、「すべて」「範囲」「一日」と条件分岐しています。

この人気記事のコードをもとに、急上昇記事のショートコードを作りたいと思います。

必要なSQLの知識

今回のアクセス数の集計では、以下のキーワードを理解する必要がありました。

注目記事のクエリを作る

基本的には、(前日+当日のアクセス数)が(期間のアクセス数)に対して、増えている記事を「急上昇の記事」と考えています。

ただ、変動率だけで順位を決めると、もともとのアクセス数がない記事が少しのアクセスで上位にあがってしまいます。当日のアクセス数をかけることで、アクセス数が少ない記事の重みを減らしています。

できあがった「急上昇の記事」の計算式は、
「((t2.today * $days / 1.5 +1) / (t1.latest+1) – 1) * t2.today」としています。

              $query = "
			  
SELECT t1.post_id, 
  ROUND(((t2.today * $days / 1.5 +1) / (t1.latest+1) - 1) * t2.today) AS sum_count
FROM (
	SELECT {$access_table}.post_id, 
	  SUM({$access_table}.count) AS latest
	FROM {$access_table}
	WHERE {$access_table}.post_type = '$post_type'
	  AND {$access_table}.date BETWEEN '$date_before' AND '$date' 
	GROUP BY {$access_table}.post_id
) t1 INNER JOIN (
	SELECT {$access_table}.post_id, 
	  SUM({$access_table}.count) AS today
	FROM {$access_table}
	WHERE {$access_table}.post_type = '$post_type'
	  AND {$access_table}.date BETWEEN '$date_yesterday' AND '$date' 
	GROUP BY {$access_table}.post_id
) t2
  ON t1.post_id = t2.post_id
ORDER BY  sum_count DESC

		
    ";

記事の並べ替えは、cocoonのPHPコードの中で「sum_count」を利用しているようなので、ORDERは関係なさそうです。sum_countは、PV表示でも利用されていました。

sum_countの列名を勝手に変えたら、うまく記事が表示できませんでした。

function.phpから急上昇の記事のショートコードを追加

できたPHPコードはこちら。function.phpに追加しました。

(コードのライセンスは、GPLです。)

//注目記事ショートコード関数
if (!shortcode_exists('trend_list')) {
  add_shortcode('trend_list', 'trend_entries_shortcode');
}
if ( !function_exists( 'trend_entries_shortcode' ) ):
function trend_entries_shortcode($atts) {
  extract(shortcode_atts(array(
  'days' => 'all',
  'count' => 5,
  'type' => 'default',
  'rank' => 0,
  'pv' => 0,
  'cats' => 'all',
  'children' => 0,
  'bold' => 0,
  'arrow' => 0,
  'class' => null,
  'author' => null,
  ), $atts, 'trend_list'));
  $cat_ids = array();
  if ($cats && $cats != 'all') {
    $cat_ids = explode(',', $cats);
  }
  $atts = array(
  'days' => $days,
  'entry_count' => $count,
  'entry_type' => $type,
  'ranking_visible' => $rank,
  'pv_visible' => $pv,
  'cat_ids' => $cat_ids,
  'children' => $children,
  'bold' => $bold,
  'arrow' => $arrow,
  'class' => $class,
  'author' => $author,
  );
  ob_start();
  generate_trend_entries_tag($atts);
  //_v($atts);
  //generate_popular_entries_tag($days, $count, $type, $rank, $pv, $categories);
  $res = ob_get_clean();
  return $res;
}
endif;

/** */
if ( !function_exists( 'generate_trend_entries_tag' ) ):
function generate_trend_entries_tag($atts){
  extract(shortcode_atts(array(
  'days' => 'all',
  'entry_count' => 5,
  'entry_type' => ET_DEFAULT,
  'ranking_visible' => 0,
  'pv_visible' => 0,
  'cat_ids' => array(),
  'children' => 0,
  'exclude_post_ids' => array(),
  'exclude_cat_ids' => array(),
  'bold' => 0,
  'arrow' => 0,
  'class' => null,
  'author' => null,
  ), $atts));
  
  $records = get_trend_ranking_records($days, $entry_count, $entry_type, $cat_ids, $exclude_post_ids, $exclude_cat_ids, $children, $author);
  
  $thumb_size = get_popular_entries_thumbnail_size($entry_type);
  $atts = array(
  'type' => $entry_type,
  'ranking_visible' => $ranking_visible,
  'pv_visible' => $pv_visible,
  'bold' => $bold,
  'arrow' => $arrow,
  'class' => $class,
  );
  $cards_classes = get_additional_widget_entry_cards_classes($atts);
  ?>
  <div class="popular-entry-cards widget-entry-cards no-icon cf<?php echo $cards_classes; ?>">
    <?php if ( $records ) :
    $i = 1;
    foreach ($records as $post):
    $permalink = get_permalink( $post->ID );
    $title = $post->post_title;
    $no_thumbnail_url = ($entry_type == ET_DEFAULT) ? get_no_image_120x68_url($post->ID) : get_no_image_320x180_url($post->ID);
    $w   = ($entry_type == ET_DEFAULT) ? THUMB120WIDTH  : THUMB320WIDTH;
    $h   = ($entry_type == ET_DEFAULT) ? THUMB120HEIGHT : THUMB320HEIGHT;
    
    $post_thumbnail = get_the_post_thumbnail( $post->ID, $thumb_size, array('alt' => '') );
    $pv = $post->sum_count;
    
    if ($post_thumbnail) {
      $post_thumbnail_img = $post_thumbnail;
    } else {
      $post_thumbnail_img = get_original_image_tag($no_thumbnail_url, $w, $h, 'no-image popular-entry-card-thumb-no-image widget-entry-card-thumb-no-image', '');
    }
    
    $pv_tag = null;
    if ($pv_visible){
      $pv_text = $pv == '1' ? $pv.' view' : $pv.' views';
    $pv_tag = '<span class="popular-entry-card-pv widget-entry-card-pv">'.$pv_text.'</span>';
  }
  ?>
  <a href="<?php echo $permalink; ?>" class="popular-entry-card-link a-wrap no-<?php echo $i; ?>" title="<?php echo esc_attr($title); ?>">
    <div class="popular-entry-card widget-entry-card e-card cf">
      <figure class="popular-entry-card-thumb widget-entry-card-thumb card-thumb">
        <?php echo $post_thumbnail_img; ?>
        <?php
        $is_visible = apply_filters('is_popular_entry_card_category_label_visible', false);
        $is_visible = apply_filters('is_widget_entry_card_category_label_visible', $is_visible);
        the_nolink_category($post->ID, $is_visible); //カテゴリラベルの取得 ?>
        </figure><!-- /.popular-entry-card-thumb -->
        
        <div class="popular-entry-card-content widget-entry-card-content card-content">
      <span class="popular-entry-card-title widget-entry-card-title card-title"><?php echo $title;?></span>
      <?php if ($entry_type != ET_LARGE_THUMB_ON): ?>
      <?php echo $pv_tag; ?>
      <?php endif ?>
      <?php generate_widget_entry_card_date('popular', $post->ID); ?>
      </div><!-- /.popular-entry-content -->
      <?php if ($entry_type == ET_LARGE_THUMB_ON): ?>
      <?php echo $pv_tag; ?>
      <?php endif ?>
      </div><!-- /.popular-entry-card -->
      </a><!-- /.popular-entry-card-link -->
      
      <?php
      $i++;
      endforeach;
      else :
    echo '<p>'.__( '人気記事は見つかりませんでした。', THEME_NAME ).'</p>';//見つからない時のメッセージ
    endif; ?>
    </div>
    <?php
    }
endif;

          
          //アクセスランキングを取得
          if ( !function_exists( 'get_trend_ranking_records' ) ):
          function get_trend_ranking_records($days = 'all', $limit = 5, $type = 'post', $cat_ids = array(), $exclude_post_ids = array(), $exclude_cat_ids = array(), $children = 0, $author = null){

            
            // //ページの判別ができない場合はDBにアクセスしない
            // if (!is_singular()) {
            //   return null;
            // }
            //var_dump($cat_ids);
            
            //アクセスキャッシュを有効にしている場合
            if (is_access_count_cache_enable()) {
              $cats = implode(',', $cat_ids);
              if ($cat_ids) {
                //子孫カテゴリも含める場合
                if ($children) {
                  $categories = $cat_ids;
                  $res = $categories;
                  foreach ($categories as $category) {
                    $res = array_merge($res, get_term_children( $category, 'category' ));
                  }
                  $cat_ids = $res;
                  $cats = implode(',', $res);
                }
              }
              
              //除外投稿
              $archive_exclude_post_ids = get_archive_exclude_post_ids();
              if ($archive_exclude_post_ids && is_array($archive_exclude_post_ids)) {
                $exclude_post_ids = array_unique(array_merge($exclude_post_ids, $archive_exclude_post_ids));
              }
              
              $expids = implode(',', $exclude_post_ids);
              $excats = implode(',', $exclude_cat_ids);
              $type = get_accesses_post_type();
              $transient_id = TRANSIENT_POPULAR_PREFIX.'?days='.$days.'&limit='.$limit.'&type='.$type.'&cats='.$cats.'&children='.$children.'&expids='.$expids.'&excats='.$excats;
              //_v($transient_id);
              $cache = get_transient( $transient_id );
              if ($cache) {
                if (DEBUG_MODE && is_user_administrator()) {
                  // echo('<pre>');
                  // echo $transient_id;
                  // echo('</pre>');
                } elseif (is_user_administrator()){
                  
                } else {
                  return $cache;
                }
              }
            }
            
            
            
            global $wpdb;
            $access_table = ACCESSES_TABLE_NAME;
            $post_type = 'post';
            $date = get_current_db_date();
            $date_before = get_current_db_date_before($days);
            $date_yesterday = get_current_db_date_before(1);
            
            
            $where = " WHERE {$access_table}.post_type = '$post_type' ".PHP_EOL;
            if ($days != 'all') {
              $date_before = get_current_db_date_before($days);
              $where .= " AND {$access_table}.date BETWEEN '$date_before' AND '$date' ".PHP_EOL;
            }
            if ($days == 1) {
              $where .= " AND {$access_table}.date = '$date' ".PHP_EOL;
            }
            //_v($exclude_post_ids);
            // _v($exclude_post_ids[0]);
            if (is_ids_exist($exclude_post_ids)) {
              $where .= " AND {$access_table}.post_id NOT IN(".implode(',', $exclude_post_ids).") ".PHP_EOL;
            }
            //3180, 3234
            if (!is_numeric($limit)) {
              $limit = 5;
            }
            //カテゴリを指定する場合
            if (is_ids_exist($cat_ids) || is_ids_exist($exclude_cat_ids)) {
              global $post;
              $term_relationships = $wpdb->term_relationships;
              $term_taxonomy = $wpdb->term_taxonomy;
              $joined_table = 'terms_accesses';
              //カテゴリー指定
              if (is_ids_exist($cat_ids)) {
                $cat_ids = implode(',', $cat_ids);
                //$where .= " AND {$term_relationships}.term_taxonomy_id IN ({$cat_ids}) ".PHP_EOL;
                $where .= " AND {$term_taxonomy}.term_id IN ({$cat_ids}) ".PHP_EOL;
              }
              //除外カテゴリー指定
              if (is_ids_exist($exclude_cat_ids)) {
                //空の配列を取り除く
                $exclude_cat_ids = array_filter($exclude_cat_ids, "strlen");
                //カンマ区切りにする
                $ex_cat_ids = implode(',', $exclude_cat_ids);
                $ex_cat_ids = preg_replace('/,$/', '', $ex_cat_ids);
                $where .= " AND {$term_relationships}.term_taxonomy_id NOT IN ({$ex_cat_ids}) ".PHP_EOL;
              }
              
              $where .= " AND {$term_taxonomy}.taxonomy = 'category' ".PHP_EOL;
              // //テーブル結合するクエリの場合はWHEREに付け加えるのでANDに変更する
              // $where = str_replace('WHERE', 'AND', $where);
              $query = "
      SELECT {$joined_table}.post_id, SUM({$joined_table}.count) AS sum_count, {$joined_table}.term_taxonomy_id, {$joined_table}.taxonomy
        FROM (
          #カテゴリとアクセステーブルを内部結合してグルーピングし並び替えた結果
          SELECT {$access_table}.post_id, {$access_table}.count, {$term_relationships}.term_taxonomy_id, {$term_taxonomy}.taxonomy
            FROM {$term_relationships}
            INNER JOIN {$access_table} ON {$term_relationships}.object_id = {$access_table}.post_id
            INNER JOIN {$term_taxonomy} ON {$term_relationships}.term_taxonomy_id = {$term_taxonomy}.term_taxonomy_id
            $where #WHERE句
            GROUP BY {$access_table}.id
        ) AS {$joined_table} #カテゴリとアクセステーブルを内部結合した仮の名前
        GROUP BY {$joined_table}.post_id
        ORDER BY post_id DESC
    ";
              //_v($query);
              //1回のクエリで投稿データを取り出せるようにケーブル結合クエリを追加
              $query = wrap_joined_wp_posts_query($query, $limit, $author);
            } else {
              $query = "
			  
SELECT t1.post_id, 
  ROUND(((t2.today * $days / 1.5 +1) / (t1.latest+1) - 1) * t2.today) AS sum_count
FROM (
	SELECT {$access_table}.post_id, 
	  SUM({$access_table}.count) AS latest
	FROM {$access_table}
	WHERE {$access_table}.post_type = '$post_type'
	  AND {$access_table}.date BETWEEN '$date_before' AND '$date' 
	GROUP BY {$access_table}.post_id
) t1 INNER JOIN (
	SELECT {$access_table}.post_id, 
	  SUM({$access_table}.count) AS today
	FROM {$access_table}
	WHERE {$access_table}.post_type = '$post_type'
	  AND {$access_table}.date BETWEEN '$date_yesterday' AND '$date' 
	GROUP BY {$access_table}.post_id
) t2
  ON t1.post_id = t2.post_id
ORDER BY  sum_count DESC

		
    ";
              //1回のクエリで投稿データを取り出せるようにケーブル結合クエリを追加
              $query = wrap_joined_wp_posts_query($query, $limit, $author);
            }
            
            //_v($query);
            $records = $wpdb->get_results( $query );
            //_v($query);
            if (is_access_count_cache_enable() && $records) {
              set_transient( $transient_id, $records, 60 * get_access_count_cache_interval() );
            }
            // _v($records);
            return $records;
          }
            
            
      endif;

これで、ショートコードで[trend_list days=”7″ bold=1 count=20]を入れると、急上昇の記事ランキングを表示できます。

タグやカテゴリーの絞り込みには対応していませんが、とりあえず自分の実用に耐えるので良しとしています。

QRコードを読み込むと、関連記事を確認できます。
Cocoonで注目記事ランキングのショートコードを作成した【SQLって何?からのデータベース操作】
タイトルとURLをコピーしました