6

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)
  • 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` – Amit Bera Jul 14 '15 at 6:16
  • i follow your way but when i print the query using $collection->getSelect()->__toString(); its show the empty result. – senthil Jul 14 '15 at 6:21
3

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

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

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.