0

I have the following collection. Basically, it returns a collection of customer entities joined with an attribute joined with the number of reward points which are going to expire for this month.

$oAttribute = Mage::getSingleton('eav/config')->getAttribute('customer', 'confirmation');
$this->_collection = Mage::getResourceModel('customer/customer_collection');
$this->_collection->getSelect()->joinLeft(array('customer_entity_varchar'=>$this->_collection->getTable('customer_entity_varchar')), 'e.entity_id = customer_entity_varchar.entity_id and customer_entity_varchar.attribute_id = '.$oAttribute->getId(), array('confirmation' => 'value'));
$this->_collection->getSelect()->joinLeft(array('reward'=>$this->_collection->getTable('enterprise_reward/reward')), 'e.entity_id = reward.customer_id', array());
$this->_collection->getSelect()->joinLeft(array('reward_history'=>$this->_collection->getTable('enterprise_reward/reward_history')), "reward_history.reward_id = reward.reward_id and reward_history.points_delta > 0 and (reward_history.expired_at_static BETWEEN '".date('Y-m-01')."' AND '".date('Y-m-t')."')", array('expiring_points' => new Zend_Db_Expr('SUM(reward_history.points_delta - reward_history.points_used)')) );
$this->_collection->getSelect()->group('e.entity_id');

I need this collection to support pagination since I need to feed this collection to a 3rd party module which loads collections by pagination. I've noticed that

$this->_collection = Mage::getResourceModel('customer/customer_collection');

alone does support pagination. So I guess I'm losing the pagination effect with the joins. How can I make my custom collection support pagination?

Thanks!

0

1 Answer 1

0

The group by was ruining the pagination. I customized the query to avoid the group by. It looks like this now:

$oAttribute = Mage::getSingleton('eav/config')->getAttribute('customer', 'confirmation');
$this->_collection = Mage::getResourceModel('customer/customer_collection');
$this->_collection->getSelect()->joinLeft(array('customer_entity_varchar'=>$this->_collection->getTable('customer_entity_varchar')), 'e.entity_id = customer_entity_varchar.entity_id and customer_entity_varchar.attribute_id = '.$oAttribute->getId(), array('confirmation' => 'value'));
$this->_collection->getSelect()->joinLeft(array('reward'=>$this->_collection->getTable('enterprise_reward/reward')), 'e.entity_id = reward.customer_id', array());
$this->_collection->getSelect()->joinLeft(array('reward_history'=>new Zend_Db_Expr("(SELECT reward_id, SUM(points_delta - points_used) as `expiring_points` from `".$this->_collection->getTable('enterprise_reward/reward_history')."` where points_delta > 0 and ((expired_at_static BETWEEN '".date('Y-m-01')."' AND '".date('Y-m-t')."') OR (expired_at_dynamic BETWEEN '".date('Y-m-01')."' AND '".date('Y-m-t')."')))")), 'reward_history.reward_id = reward.reward_id', 'expiring_points');

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