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
EDIT
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()
->from(
array('mymodule/registry'),
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);
Best Answer
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.
And you can get results by calling this function at anywhere:
If you put all above correctly it will give result like these:
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: