CE1.9.2 - Filter Orders collection on hasInvoices()

Question

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')
    ->getCollection()
    ->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.

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:

$orders->addAttributeToFilter(
    'grand_total',
    ['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', []);