Zend2 Db\Select with dynamic IN clause with HAVING

Question

SELECT 
    CONCAT_WS(' ', user.firstname, user.lastname) AS name,
    user.email AS email
FROM
    user
WHERE
    op_id IN (
        SELECT 
            user.op_id AS op_id
        FROM
            user
        WHERE
            last_login_on IS NOT NULL
        GROUP BY op_id
        HAVING (SUM(IF(DATE(last_login_on) > DATE(DATE_SUB(NOW(), INTERVAL 31 DAY)), 1, 0)) = 0)
        ORDER BY op_id ASC)
AND role_type = 'owner';

From the SQL above which, from all users assigns 1 if login within last month and 0 if not logged in for more than a month, then reduces the result set to the ones HAVING 0 logins as the IN clause and then SELECTs the op_id owner details.

This has been replicated in Zend DB\Select and all works as expected, except the HAVING clause is lost in the generated SQL query, when used as a new Expression ("SUM(IF(DATE(last_login_on) > DATE(DATE_SUB(NOW(), INTERVAL 31 DAY)), 1, 0)) = 0").

Can this expression be applied to a $select->having() statement?

Answer 1

This should be possible, having() method of Select accepts an expression as parameter. The problem is which expression you use: \Zend\Db\Sql\Expression or \Zend\Db\Sql\Predicate\Expression, although Predicate\Expression inherits from Sql\Expression. Honestly, the difference is not completely clear to me, but I was able to create your query without having got lost:

$select = new Select('user');

$inSelect = new Select('user');
$inSelect->columns(array('op_id' => 'op_id'));
$inSelect->where(new \Zend\Db\Sql\Predicate\IsNotNull('last_login_on'));
$inSelect->group('op_id');
$inSelect->order(array('op_id' => 'asc'));
$inSelect->having(new \Zend\Db\Sql\Predicate\Expression('SUM(IF(DATE(last_login_on) > DATE(DATE_SUB(NOW(), INTERVAL 31 DAY)), 1, 0)) = 0'));

$inExpression = new In('op_id', $inSelect);
$select->where($inExpression);
$select->where(array('role_type' => 'owner'), PredicateSet::OP_AND);

echo $select->getSqlString();

This should echo a string like this:

SELECT 
    user.*
FROM
    user
WHERE
    op_id IN (
        SELECT 
            user.op_id AS op_id
        FROM
            user
        WHERE
            last_login_on IS NOT NULL
        GROUP BY op_id
        HAVING SUM(IF(DATE(last_login_on) > DATE(DATE_SUB(NOW(), INTERVAL 31 DAY)),
        1,
        0)) = 0
        ORDER BY op_id ASC
    )
AND role_type = 'owner'

Edit:

ZF2 Version: 2.4.9