I've separated this from another question I had asked before. How would I be able to filter the sales/order collection on hasInvoices() and ensure that there is no balance due to pay? I'd like to avoid doing this in the iteration step and hopefully save performance hits.

$orders = Mage::getModel('sales/order')
    ->addAttributeToFilter('status', array('complete','processing'));

foreach ($orders as $order) {
    if (($order->hasInvoices()) && ($order->getTotalDue() == 0)) {
        echo $order->getIncrementId() . "\n";

The code above does work, but I think it would be much better to iterate through an already filtered collection.

1 Answer 1


Filter by getTotalDue()

To filter by "total due" you have to calculate it with SQL. The method is

public function getTotalDue()
    $total = $this->getGrandTotal()-$this->getTotalPaid();
    $total = Mage::app()->getStore($this->getStoreId())->roundPrice($total);
    return max($total, 0);

In SQL getTotalDue() == 0 would be

GREATEST(grand_total - total_paid, 0) = 0

You can use arbitrary SQL expressions in addAttributeToFilter with Zend_Db_Expr, as long as the format is $attribute $operator $expression.

So we need to rearrange the equation:

    GREATEST(grand_total - total_paid, 0) = 0
<=> grand_total - total_paid <= 0
<=> grand_total <= total_paid

This results in the following PHP code:

    ['lteq' => new Zend_Db_Expr('total_paid')]

Filter by hasInvoices()

Then join the invoice table using INNER JOIN to restrict your collection to orders that have at least one invoice:

$orders->join('sales/invoice', 'order_id=main_table.entity_id', []);
  • This looks so much neater than the method I settled on (direct SQL Query): SELECT 'increment_id', 'status', 'total_due' FROM 'sales_flat_order' WHERE ('status' IN ("processing", "complete")) AND ('total_invoiced' IS NOT NULL) AND ('total_due' = 0) AND ('total_paid' IS NOT NULL). Thanks fschmengler!
    – maGz
    May 20, 2016 at 7:41

Your Answer

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

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