cakePHP 一つのテーブルで統計的にデータを取り出す

Question

前提・実現したいこと

cakePHPでのMySQLからのレコードの抽出についてです。
以下のようにテーブル構成をしています。
Postsテーブルで、ユーザーからのポストはmessageの内容によって種類をtypeで分類しています。
未分類のものはtypeを0にしており、ひとりのユーザーに対して、未分類のものがあればそのユーザーのそれ以前のポストは全て分類されているものとします。
そこで、未分類のポストを取得したいのですが、今までのポストでtypeを2に分類された数が多いユーザー順に取得したいです。
またpaginationも使いたいです。
どのようにpaginationに設定すればよろしいでしょうか。グループ化や、count関数を使って考えてみましたが、うまくいきませんでした。
解決方法があれば教えていただきたいです。

テーブル

Posts Table:
------------------------------------------------------------------------
| id | user_id | message  | type |
------------------------------------------------------------------------
|  1 |    1    | message1 |  2   | 
------------------------------------------------------------------------
|  2 |    2    | message2 |  2   | 
------------------------------------------------------------------------
|  3 |    3    | message3 |  2   | 
------------------------------------------------------------------------
|  4 |    1    | message4 |  1   | 
------------------------------------------------------------------------
|  5 |    3    | message5 |  2   | 
------------------------------------------------------------------------
|  6 |    2    | message6 |  1   | 
------------------------------------------------------------------------
|  7 |    2    | message7 |  2   | 
------------------------------------------------------------------------
|  8 |    3    | message8 |  2   | 
------------------------------------------------------------------------
|  9 |    1    | message9 |  1   | 
------------------------------------------------------------------------
| 10 |    1    | message10|  0   | 
------------------------------------------------------------------------
| 11 |    2    | message11|  0   | 
------------------------------------------------------------------------
| 12 |    4    | message12|  0   | 
------------------------------------------------------------------------

期待している抽出結果

------------------------------------------------------------------------
| 11 |    2    | message11|  0   | 
------------------------------------------------------------------------
| 10 |    1    | message10|  0   | 
------------------------------------------------------------------------
| 12 |    4    | message12|  0   |
------------------------------------------------------------------------

現状

現状以下のようになっています。

            $this->Post->find('all',array(
                'fields' => array(
                    ' (select count(*) from posts where user_id=t1.user_id and type=2) as cnt 
                      , t1.*'
                ),
                'conditions' => array(
                    'type' => 0
                )
                'order' => array(
                    '1 desc'
                )
            ))

以下のSQL文が発行されます

select (select count(*) from posts where user_id=t1.user_id and type=2) as cnt 
     , t1.* 
from posts
where type=0
order by 1 desc

当然、from posts t1とはなりません。
何か解決方法はあるでしょうか。

Answer 1

CREATE TABLE Posts
    (id int, user_id int, message varchar(9), type int)
;
INSERT INTO Posts
    (id, user_id, message, type)
VALUES
    (1, 1, 'message1', 2),
    (2, 2, 'message2', 2),
    (3, 3, 'message3', 2),
    (4, 1, 'message4', 1),
    (5, 3, 'message5', 2),
    (6, 2, 'message6', 1),
    (7, 2, 'message7', 2),
    (8, 3, 'message8', 2),
    (9, 1, 'message9', 1),
    (10, 1, 'message10', 0),
    (11, 2, 'message11', 0),
    (12, 4, 'message12', 0)
;

select (select count(*) from posts where user_id=t1.user_id and type=2) as cnt 
     , t1.* 
from posts t1
where type=0
order by 1 desc

Comments

[art_of]

回答ありがとうございます。
DB上でそのSQL文を叩くと、確かに希望通りの結果が得られました。
しかし、cakephpのfind文ではどのように表したら良いのかわかりませんでした。find文での解決方法はありませんでしょうか。

[sazi]

現状どのように実装されているのか、コードを質問に追記した方が良いですね。

[sazi]

mysqlだと「order by cnt desc」 のようにエイリアスでの指定もエラーにはならないとは思います。

[art_of]

なるほど、ご指摘ありがとうございます。
現状、このように指定しているのですが
```
$this->Post->find('all',array(
'fields' => array(
' (select count(*) from posts where user_id=t1.user_id and type=2) as cnt
, t1.*'
),
'conditions' => array(
'type' => 0
),
'order' => array(
'1 desc'
)
))
```
実際に発行されるSQL文は以下のようになります。
```
SELECT ((select count(*) from posts where user_id=t1.user_id and type=2) as cnt, t1.* FROM posts WHERE `type` = 0 ORDER BY `1` desc
```
当然、From posts t1とはならないんですが、どのように解決すればよろしいでしょうか。

[sazi]

テーブルのエイリアスを逆にしてみて下さい。
SELECT ((select count(*) from posts t1 where user_id=posts.user_id and type=2) as cnt, * FROM posts WHERE `type` = 0 ORDER BY `1` desc

[art_of]

おお！
そちらでうまく取得することができました。
本当に助かりました。ありがとうございました！！