WordPress filter to add HAVING clause to SQL

Posted by

There’s posts_fields, posts_join, posts_where, posts_groupby but strangely there’s no WordPress filter named posts_having and it took many Google searches to find anything which might present a solution, and when I did find something it had precisely one upvote and hadn’t even been accepted as the answer.

It’s not necessarily a robust solution, it depends on your circumstances. Obviously to even use a HAVING clause you have to be grouping and have specific fields to compare. It seems a bit hacky, but what WordPress solution doesn’t begin with the words “it’s a bit of a hack but…”.

The answer is to add a SECOND posts_groupby filter and ensure it comes last so you can add the HAVING term manually.

add_filter('posts_fields', 'my_fields', 10);
add_filter('posts_groupby', 'my_groupby', 10);
add_filter('posts_groupby', 'my_having', 1000000);

function my_fields($sql){
    $sql.=' , MIN(`my_field`) AS `my_field` ';
    return $sql;
}

function my_groupby($sql){
    $sql.= (strlen($sql)?',':'').' some.id ';
    return $sql;
}

function my_having($sql){
    $sql.= (strpos($sql, 'HAVING') === false ? ' HAVING ' : ' AND ') . ' my_field >= 0 ';
    return $sql;
}

If you need more than one HAVING condition then just add another posts_groupby filter with a number one higher than the previous and repeat the check for HAVING in the filter function to ensure you only have one instance.