MT::Object での GROUP BY 集計関数の利用方法

微妙に痒いところに手が届かないものの、MT::Object->(count|max|avg|sum)_group_byというのがひっそりとある。

使い方は共通してこんなかんじ。hogeのところは便宜読み替えのこと。

my $group_iter = MT::Foo->hoge_group_by(
    $terms,
    {
        %args,
        group   => [グループキー],
        hoge    => 集計対象
    }
);
while ( my ($count, $blog_id) = $group_iter->() ) {
    print "COUNT(集計対象):$count blog_id:$blog_id\n";
}

グループキー、集計対象は列名からプレフィクス（entry_とか）を省いたものです。

$termsは連想配列のリファレンス、%argsは連想配列そのものという点に注意。なお、これらは普通にload()などと共通なので、JOINとかもできます。

具体的には・・・

件数

たとえばブログごとの公開中の記事数を取得するなど。

SQLならこう。

 SELECT entry_blog_id, COUNT(entry_id)
 FROM mt_entry
 WHERE entry_status = 2 AND entry_class = 'entry'
 GROUP BY entry_blog_id

MT::Object->count_group_by()を使う。

my $group_iter = MT->model( 'entry' )->count_group_by(
    {
        status => 2
    },
    {
        (),
        group   => ['blog_id'],
        count   => 'id'
    }
);
while ( my ($count, $blog_id) = $group_iter->() ) {
    print "COUNT(*):$count blog_id:$blog_id\n";
}

最大値

たとえばブログごとの最新の公開日を取得するなど。

SQLならこう。

 SELECT entry_blog_id, MAX(entry_authored_on)
 FROM mt_entry
 WHERE entry_status = 2 AND entry_class = 'entry'
 GROUP BY entry_blog_id

MT::Object->max_group_by()を使う。

my $group_iter = MT->model( 'entry' )->max_group_by(
    {
        status => 2
    },
    {
        (),
        group   => ['blog_id'],
        max     => 'authored_on'
    }
);
while ( my ($max, $blog_id) = $group_iter->() ) {
    print "MAX(authored_on):$max blog_id:$blog_id\n";
}

平均値

MT::Object->avg_group_by()

パラメータがavgになる以外は同じ。

合計値

MT::Object->sum_group_by()

パラメータがsamになる以外は同じ。

JOINの利用

たとえば記事ごとに設定されているタグの数を集計するなど。

※以下のサンプルでは特定のブログに絞るため blog_id を付けていますが、全体でやるなら不要です。

SQLならこんなかんじ。

SELECT entry_id, COUNT(objecttag_tag_id)
FROM mt_objecttag, mt_entry
WHERE
    objecttag_object_datasource = 'entry' AND
    objecttag_object_id = entry_id AND
    objecttag_blog_id = 4 AND
    entry_status = 2
GROUP BY entry_id

MT::Object->count_group_by()を使う場合はMT::Object->load()と同様にjoinで指定。

なお、SELECT句に入るカラム名を構築する際、レシーバのクラスのプレフィクスが使用されるため、MT::EntryではなくMT::ObjecTtagである必要がある点に注意。

my $entry_class = MT->model( 'entry' );
my $group_iter = MT->model( 'objecttag' )->count_group_by(
    {
        object_datasource   => $entry_class->datasource,
        blog_id             => 4,
    },
    {
        (
            'join' => [
                $entry_class,
                undef,
                {
                    id      => \'= objecttag_object_id',
                    status  => 2,
                }
            ]
        ),
        group   => ['entry_id'],
        count   => 'tag_id'
    }
);
while ( my ($count, $entry_id) = $group_iter->() ) {
    print "COUNT(tag_id):$count entry_id:$entry_id\n";
}

カスタムフィールドの集計

たとえばテキストフィールドに入力された値の平均とか･･･と思って試してみたものの、MT標準のカスタムフィールドではvarchar(255)のカラムに値が格納されるため、集計関数を利用できないようです。

SQLでやるのであればAVG(CAST(entry_meta_vchar_idx AS SIGNED))とかやればできます。（どうしてもやりたい方はMT::Object->driver()を利用した生のSQL実行＆結果取得方法を参照のこと→MTのデータベースで任意のSQLを実行して結果を取得する）

PowerCMSで追加される「テキスト（整数）」の場合はint(11)なカラムに格納されるため、MT::Object->avg_group_by()などを利用することができます。

my $class = MT->model( 'entry' );
my $type = MT::Meta->metadata_by_name( $class, 'field.val1' );
my $group_iter = $class->avg_group_by(
    {
        status => 2
    },
    {
        (
            'join'  => [
                $class->meta_pkg,
                undef,
                {   type          => 'field.val1',
                    'entry_id'    => \'= entry_id'
                }
            ],
        ),
        group   => ['blog_id'],
        avg     => 'meta_' . $type->{type}
    }
);
while ( my ($avg, $blog_id) = $group_iter->() ) {
    print "AVG(field.val1):$avg blog_id:$blog_id\n";
}

SELECT句に設定されるカラム名の都合で、avgがちょっとキモいですが仕方がなさげ。

なお、生成されるSQLはこんなかんじです。

SELECT AVG(entry_meta_vchar_idx) AS avg_entry_meta_vchar_idx, entry_blog_id
FROM mt_entry, mt_entry_meta
WHERE (entry_status = '2') AND (entry_class = 'entry') AND (entry_meta_type = 'field.val1') AND (entry_meta_entry_id = entry_id)
GROUP BY entry_blog_id
ORDER BY avg_entry_meta_vchar_idx DESC