How can I write this unsecure, ugly, query following the Magento way without using collection?

select (select sum(points) FROM ".$this->getTable('mymodule/registry')." where points < 0 and entity_id=".$customerId.") as points_spent,
(select sum(points) FROM ".$this->getTable('mymodule/registry')." where points > 0  and entity_id=".$customerId.") as points_received,
(select sum(points) FROM ".$this->getTable('mymodule/registry')."  where entity_id=".$customerId.") as points_current


A possible solution.

In a resource model I can put something like this:

$adapter = $this->_getReadAdapter();

        $receivedPoints = $adapter->select()
            ->from(array('mymodule/registry'), array(new Zend_Db_Expr('sum(points)')))
            ->where('points > ?', 0)
            ->where('entity_id = ?', $customerId);

        $spentPoints = $adapter->select()
            ->from(array('mymodule/registry'), array(new Zend_Db_Expr('sum(points)')))
            ->where('points < ?', 0)
            ->where('entity_id = ?', $customerId);

        $query = $adapter->select()
                array('current_points'  => new Zend_Db_Expr('sum(points)'),
                      'received_points' => new Zend_Db_Expr('(' . $receivedPoints . ')'),
                      'spent_points'    => new Zend_Db_Expr('(' . $spentPoints . ')')
            ->where('entity_id = ?', $customerId);
        return $adapter->fetchRow($query);
  • If you also have a resource model to retrieve collections, and Im guessing you do try this page for working with the collection.magentocommerce.com/wiki/1_-_installation_and_configuration/… Commented Oct 9, 2013 at 17:02
  • Thank You for your suggestion. Maybe my bad english, but I wouldn't use collection in this case. Commented Oct 9, 2013 at 17:05
  • Ah ok, my bad. Your english is good but I misunderstood you :-) Commented Oct 9, 2013 at 17:33
  • 1
    I also do not understand. You already have an ugly query so what is the problem? Commented Oct 9, 2013 at 18:09
  • @SanderMagel no probs, I should probably explain better my question :) Commented Oct 9, 2013 at 19:38

2 Answers 2


Try to add this function to your collection class. My logic is you have sum of points grouping by below of 0 and above of 0 and all of them.

class My_Module_Model_Mysql4_Registry_Collection extends Mage_Core_Model_Mysql4_Collection_Abstract
    public function _construct()

    public function getSumOfPoints($customerId)
        $this->addFieldToFilter('customer_id', $customerId);

            ->columns(new Zend_Db_Expr("sign(points) as sign"))
            ->columns(new Zend_Db_Expr("sum(points) as summ"))
            ->group(new Zend_Db_Expr("sign(points)"));
        return $this;

And you can get results by calling this function at anywhere:

$results = Mage::getResourceModel('mymodule/registry_collection')->getSumOfPoints($customerId);
foreach ($results as $result) {

If you put all above correctly it will give result like these:

    [sign] => -1 //summ of points less than 0
    [summ] => -166
    [sign] => 1 //summ of points great than 0
    [summ] => 312

You can get summ of all points adding two result above. If you don't want to use collection class you can adopt this logic to your own code which is using resource model. Collection class generates sql query given below, try first this query, if it gives appropriate result you can use code given above:

    sign(points)AS sign,
    sum(points)AS summ
    `mymodule_registry` AS `main_table` //put here real table name
    (customer_id = '4') //put real customer id

At the end of the day, that part of Magento relies on Zend_DB, so what are you looking for is: Zend_Db_Select. lib/Varien (and in newer Magento version lib/Magento) extends this part.

The object used for selects is Varien_Db_Select which extends Zend_Db_Select

 $db = Mage::getSingleton('core/resource')->getConnection('core_read');//(Magento|Varien)_Db_Adapter_Pdo_Mysql
    public function select()
        return new Varien_Db_Select($this);
  • Thank You. Of course I had to use the Varien_Db_Select, I knew that. I was asking for the right sintax to use. I never used nested select using the Zend_Db_Select. Anyway I found out a solution that I'm gonna post in an update of my question. Commented Oct 9, 2013 at 19:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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