Build query with aggregate functions in HAVING clause

Question

I am trying to figure out how to have aggregate functions in the having clause with CakePHP's query builder.

Background: the intent is to correct all rows in a table with compound primary-keys (page-ID and URL) such that each page-ID-group has only one default video. There are some groups with no, and some groups with more than one "default" row, which needs to be corrected. I've figured out all the steps – except for this detail.

This is the query that I'm trying to build.

SELECT 
        video_page_id, video_url
    FROM page_video 
    WHERE 
        video_page_id IN (
            SELECT video_page_id 
                FROM page_video 
                GROUP BY video_page_id 
                HAVING SUM(video_is_default) < 1
        ) 
    AND video_order = 0 
    ;

And this is what I have built:

    // sub-select: all groups that have too few defaults.
    // Returns list of page-IDs.
    $qb = $this->getQueryBuilder();
    $group_selection = $qb
        ->select(array(
            'video_page_id',
        ))
        ->from('page_video')
        ->group('video_page_id')
        ->having(array(
            '1 >' => $qb->func()->sum('video_is_default'),
        ))
    ;

    // sub-select: compound-primary-key identifiers of all rows where
    // `video_is_default` has to be modified from `0` to `1`.
    // Returns list of two columns.
    $qb = $this->getQueryBuilder();
    $modifiable_selection = $qb
        ->select(array(
            'video_page_id',
            'video_url',
        ))
        ->from('page_video')
        ->where(array(
            'video_page_id IN' => $group_selection,
            'video_order = 0',
        ))
    ;

But then I get this exception: Column not found: 1054 Unknown column '1' in 'having clause'

The crux is the HAVING clause. I basically don't know how to combine the aggregate function with the attribute-value properties of an array. Usually, in order to craft lower/greater-than clauses, you write it like this: array('col1 >' => $value). But here, I needed to flip the equation because the complex expression can't fit into an array key. And now the 1 gets interpreted as a column name.

---

Writing it as a concatenated string doesn't seem to help either.

array(
    $qb->func()->sum('video_is_default') .' > 1',
)

Exception: PHP Recoverable fatal error: Object of class Cake\Database\Expression\FunctionExpression could not be converted to string

---

I know I could do …

SELECT (…), SUM(video_is_default) AS default_sum FROM (…) HAVING default_sum < 1 (…)

… but then the sub-select column count doesn't match anymore.

Exception: ERROR 1241 (21000): Operand should contain 1 column(s)

Answer 1

I feel silly for figuring out the solution so soon after asking the question.

The lt method acccepts complex values as the first parameter.

->having(function($exp, $qb) {
    $default_sum = $qb->func()->sum('video_is_default');
    return $exp->lt($default_sum, 1);
})