Help us understand the problem. What is going on with this article?

🍰【CakePHP2】任意のカラムでGROUP BYした最新レコードの取得

環境

PHP 7.2.21
CakePHP 2.10.18

内容

CakePHP2のfind()では特定のカラムでGROUP BYした上でそれぞれの最新レコード取得するのが難しかったので後の為にメモ

やりたいこと

下記のようなテーブルでpage_idをGROUP BYしてそのページごとの最新レコードが欲しい
あと、できればpaginatorされてほしい

test_page_history.sql
CREATE TABLE `test_page_history` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `page_id` varchar(50) NOT NULL,
  `update_date` datetime NOT NULL,
  `updater_name` varchar(50) NOT NULL,
  `reason` varchar(50) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `test_UN` (`page_id`,`update_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

① 元データ

"id" "page_id" "update_date" "updater_name" "reason"
1 A-1 "2019-12-01 00:00:00.0" alice ページ新規追加
2 A-2 "2019-12-02 00:00:00.0" bob ページ新規追加
3 A-1 "2019-12-05 00:00:00.0" charlie 機能追加
4 A-3 "2019-12-05 00:00:00.0" alice 新規追加
5 A-1 "2019-12-06 00:00:00.0" delta 不要の為ページ削除
6 A-3 "2019-12-07 00:00:00.0" charlie デザイン修正

② 取得したいテータ(page_idでまとめた最新レコード)

"id" "page_id" "update_date" "updater_name" "reason"
5 A-1 "2019-12-06 00:00:00.0" delta 不要の為ページ削除
2 A-2 "2019-12-02 00:00:00.0" bob ページ新規追加
6 A-3 "2019-12-07 00:00:00.0" charlie デザイン修正

やったこと

Modelにカスタムfinderを追加実装し
conditionsで力業を使った

TestPageHistory.php
<?php

App::uses('AppModel', 'Model');

class TestPageHistory extends AppModel {
    public $useTable    = 'test_page_history';
    public $primaryKey  = 'id';
    public $useDbConfig = 'admin';

    // 自作するカスタムfinderを使用可能に設定
    public $findMethods = [
        'newest' => true,
    ];

    /**
     * newest実装
     *
     * @param string $state
     * @param array  $query
     * @param array  $results
     */
    protected function _findNewest($state, $query, $results = []) {
        if ($state === 'before') {
            $query['conditions']["{$this->alias}.update_date = (SELECT MAX(b.update_date) FROM test_page_history AS b WHERE TestPageHistory.page_id = b.page_id)"] = [true];
            $query['order']["{$this->alias}.page_id"] = ['asc'];
            return $query;
        }

        return $results;
    }
}

Controllerはカスタムfinderを使うのみ

TestController.php
<?php

App::uses('AppController', 'Controller');
App::uses('TestPageHistory', 'Model');

class PointsListController extends AppController {

    public $uses = [
        'TestPageHistory',
    ];

    /**
     * 一覧
     *
     * @return void
     */
    public function index(): void {
        // カスタムfinderである「'newest'」でデータ取得
        $test = $this->TestPageHistory->find('newest');
        var_dump($test);
      }

↓

取得結果

page_idごとの最新レコードが取得できている🐈

array(3) {
  [0]=>
  array(1) {
    ["TestPageHistory"]=>
    array(5) {
      ["id"]=>
      string(1) "5"
      ["page_id"]=>
      string(3) "A-1"
      ["update_date"]=>
      string(19) "2019-12-06 00:00:00"
      ["updater_name"]=>
      string(5) "delta"
      ["reason"]=>
      string(27) "不要の為ページ削除"
    }
  }
  [1]=>
  array(1) {
    ["TestPageHistory"]=>
    array(5) {
      ["id"]=>
      string(1) "2"
      ["page_id"]=>
      string(3) "A-2"
      ["update_date"]=>
      string(19) "2019-12-02 00:00:00"
      ["updater_name"]=>
      string(3) "bob"
      ["reason"]=>
      string(21) "ページ新規追加"
    }
  }
  [2]=>
  array(1) {
    ["TestPageHistory"]=>
    array(5) {
      ["id"]=>
      string(1) "6"
      ["page_id"]=>
      string(3) "A-3"
      ["update_date"]=>
      string(19) "2019-12-07 00:00:00"
      ["updater_name"]=>
      string(7) "charlie"
      ["reason"]=>
      string(18) "デザイン修正"
    }
  }
}

paginatorにもつっこめた

TestController.php
            $this->paginate = [
                'conditions' => $conditions,
                'limit'      => $limit,
                'findType'   => 'newest',
            ];
            $this->paginate();
Why do not you register as a user and use Qiita more conveniently?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away