LoginSignup
1

More than 5 years have passed since last update.

GROUP BYした結果、対象が何件あるか調べる #PostgreSQL #CakePHP

Last updated at Posted at 2015-05-13

これは何か?

GROUP BYした結果、対象が何件あるか調べたいときに用いた方法のメモ。
CakePHPで利用するにあたりOVER句を使う方法にした。

OVER句を使う

PostgreSQL 9.3.4でCOUNT(*) OVER ()を使うと次のような感じ。

SELECT COUNT(*) OVER ()
FROM table
GROUP BY group_id
HAVING SUM(point) > 0
LIMIT 1;

CakePHP 2.3.9で表現すると次のような感じ。

$this->Model->virtualFields = array('cnt' => 'COUNT(*) OVER ()');

$this->Model->find('first', array(
    'fields' => array(
        'cnt',
    ),
    'group' => array(
        'group_id HAVING SUM(point) > 0'
    ),
));

OVER句を使わない、OVER句を使えない場合

OVER句が使えない場合はこんな感じ。

SELECT COUNT(*)
FROM (

SELECT group_id
FROM table
GROUP BY group_id
HAVING SUM(point) > 0

) AS t

CakePHP 2.3.9で表現すると次のような感じ。

$ds = $this->Model->getDataSource();

$query = $ds->buildStatement(array(
    'table' => 'table',
    'alias' => '',
    'fields' => array(
        'group_id',
    ),
    'group' => array(
        'group_id HAVING SUM(point) > 0'
    ),
), $this->Model);

$query = $ds->buildStatement(array(
    'table' => '(' . $query . ')',
    'alias' => 't',
    'fields' => array(
        'COUNT(*)',
    ),
), $this->Model);

$ds->query($query);

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
1