sfPropelPager and GROUP BY criteria
So for one reason or another (actually a few bad ones) I ended up having to use a Criteria object looking like this:
<?php
$c = new Criteria ( );
$having = $c->getNewCriterion('the_count', count($tags), Criteria::GREATER_EQUAL);
$c->add(TaggingPeer::TAG_ID, $tags, Criteria::IN);
$c->addSelectColumn("COUNT(*) AS the_count");
$c->addSelectColumn(TaggingPeer::ID);
$c->addSelectColumn(TaggingPeer::TAGGABLE_ID);
$c->addSelectColumn(TaggingPeer::TAGGABLE_MODEL);
$c->addGroupByColumn(TaggingPeer::TAGGABLE_MODEL);
$c->addGroupByColumn(TaggingPeer::TAGGABLE_ID);
$c->addHaving($having);It makes SQL that looks something like this:
“SELECT tag_id, tag_model, id, COUNT(*) AS the_count FROM sf_tagging WHERE tag_id IN (1,2,3) GROUP BY taggable_model, taggable_id HAVING the_count > int”The query sucks but whatever it works.
My issue came when I tried to use it with a sfPropelPager. I set up the pager per usual but for some reason the results that were coming back weren’t correct. For some reason, the COUNT being returned by the sfPropelPager was completely wrong. It turns out the offending lines are here in sfPropelPager.class.php :
<?php
public function init()
{
$hasMaxRecordLimit = ($this->getMaxRecordLimit() !== false);
$maxRecordLimit = $this->getMaxRecordLimit();
$cForCount = clone $this->getCriteria();
$cForCount->setOffset(0);
$cForCount->setLimit(0);
$cForCount->clearGroupByColumns();
}For whatever reason, sfPropelPager clears the GROUP BY clauses before it calculates the COUNT for a criteria object. I’m not sure why it does this – but it certainly is unexpected and breaks my query in particular.
There are a handful of posts about this on the Symfony forums and it looks like the Propel people know about the issue to.
The solution to this is to use the setPeerCountMethod() from sfPropelPager. The setPeerCountMethod() function allows you to specify a custom COUNT() method inside the peer for your Criteria. I went ahead and added a new function to put the GROUP BY columns back in:
<?php
public static function advancedSearchCount($c){
$c->addGroupByColumn(TaggingPeer::TAGGABLE_MODEL);
$c->addGroupByColumn(TaggingPeer::TAGGABLE_ID);
return self::doCount($c);
}This solution works but it is extremely rigid. Since the custom count function has to be static you’d really be out of luck if you had variable columns or other dynamic requirements.
I’d love to know if someone has a cleaner/better/more elegant solution for this.
