I build a query with an alias field and need to include this alias field value inthe query condition.

The query:

$collection = Mage::getModel('customer/customer')->getCollection();
$collection->getSelect()->join(array('review'=>'flrating'),"e.entity_id = review.reviewed_user_id ",array('total_avg_rating'=>'AVG(review.avg_rating)'));
$collection->getSelect()->having('e.total_avg_rating >= ?', $min)->having('e.total_avg_rating <= ?',$max);

allies name is "total_avg_rating" but it shows error: "total_avg_rating not exist".

allies filed name result

enter image description here

As per follow your mention way

enter image description here


Please change

having('e.total_avg_rating <= ?',$max)


having('total_avg_rating <= ?',$max)

since total_avg_rating is not a field of the main table e but an alias

If you still get an error, it might be because somewhere $collection->getSize() is called. In this case, you cannot use an alias in having and it has to be changed to

having('AVG(review.avg_rating) <= ?',$max)

For explanation see here: Magento join query with group by and having error

  • i tried total_avg_rating instead of e.total_avg_rating, but this again showing the same error – senthil Jul 15 '15 at 9:43
  • I thought so. See updated answer and link – Fabian Schmengler Jul 15 '15 at 9:44
  • it's working, but the result is not accurate – senthil Jul 15 '15 at 9:46
  • i read from the above link , and i check the function getSelectCountSql(), its same as my Collection.php ('app/code/core/Mage/Reports/Model/Resource/Order')file, can you explain briefly what i need do to fix the above issue – senthil Jul 15 '15 at 9:53
  • No because I don't understand your issue. You say it's working but the restults are not accurate. As far as I see, the issue from the question is resolved and now you need to work on your query to get what you really want. – Fabian Schmengler Jul 15 '15 at 9:55

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.