タケユー・ウェブ日報

Ruby on Rails や Flutter といったWeb・モバイルアプリ技術を武器にお客様のビジネス立ち上げを支援する、タケユー・ウェブ株式会社の技術ブログです。

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