Magento join query with group by and having error

Question

join query with customer table, and group by and having clauses not working, its show an error , filed is not found.

The Query

$collection = $this->getCollection();
 $collection->addAttributeToSelect('userid')->addAttributeToSelect('firstname')->addAttributeToSelect('lastname')->addAttributeToSelect('profile_image')->addAttributeToSelect('specialist')->addAttributeToSelect('latitude')->addAttributeToSelect('free_consult')->addAttributeToSelect('longitude')->addAttributeToFilter('group_id',Federallawyer_Customer_Helper_Data::LAWYER);
 $collection->getSelect()->join(array('review'=>'flrating'),"e.entity_id = review.reviewed_user_id ",array('avg_rating'=>'AVG(review.avg_rating)'));
 $collection->getSelect()->group('review.reviewed_user_id');
$collection->getSelect()->having('avg_rating > ?', $min)

• Amit Bera: try $collection->getSelect()->group('review.reviewed_user_id')->having('avg_rating > {$min}' ) instead of ` $collection->getSelect()->group('review.reviewed_user_id'); $collection->getSelect()->having('avg_rating > ?', $min`
• senthil: i follow your way but when i print the query using $collection->getSelect()->__toString(); its show the empty result.

Answer 1

Maybe because this is a calculated field, did you try this for your having clause? :

$collection->getSelect()->having('AVG(review.avg_rating) > ?', $min)

Answer 2

Short answer:

Use this code:

$collection->getSelect()->having('AVG(review.avg_rating) > ?', $min)

Explanation:

Loading a collection with HAVING works fine, but if you are using this in grid context, Magento might call getSelectCountSql() on the collection to get a modified query to retrieve the number of results.

This is the method:

/**
 * Get SQL for get record count
 *
 * @return Varien_Db_Select
 */
public function getSelectCountSql()
{
 $this->_renderFilters();
 $countSelect = clone $this->getSelect();
 $countSelect->reset(Zend_Db_Select::ORDER);
 $countSelect->reset(Zend_Db_Select::LIMIT_COUNT);
 $countSelect->reset(Zend_Db_Select::LIMIT_OFFSET);
 $countSelect->reset(Zend_Db_Select::COLUMNS);
 $countSelect->columns('COUNT(*)');
 return $countSelect;
}

By default, it removes selected columns but unfortunately not the HAVING parts and so the resulting SELECT COUNT(*) ... query is broken.

There are some collections like the collections in Mage_Reports that override this method and do reset HAVING.

This is for example Mage_Reports_Model_Resource_Order_Collection:

/**
 * Get select count sql
 *
 * @return unknown
 */
public function getSelectCountSql()
{
 $countSelect = clone $this->getSelect();
 $countSelect->reset(Zend_Db_Select::ORDER);
 $countSelect->reset(Zend_Db_Select::LIMIT_COUNT);
 $countSelect->reset(Zend_Db_Select::LIMIT_OFFSET);
 $countSelect->reset(Zend_Db_Select::COLUMNS);
 $countSelect->reset(Zend_Db_Select::GROUP);
 $countSelect->reset(Zend_Db_Select::HAVING);
 $countSelect->columns("COUNT(DISTINCT main_table.entity_id)");
 return $countSelect;
}

I don't know why this is not included in the default implementation, but essentially it means you cannot use HAVING with calculated columns but must repeat the expression:

$collection->getSelect()->having('AVG(review.avg_rating) > ?', $min)