[FIXED] CakePHP - sorting on deeply associated field

Issue

I have the following associations:

Invoiceitem belongsTo Invoice Invoice belongsTo Supplier

I want to retrieve Invoiceitems, subject to various conditions, but be able to sort on Supplier name.

So far I have:

$this->Invoiceitem->Invoice->belongsTo['Supplier']['order'] = "Supplier.name " . $direction;
$itemlist = $this->Invoiceitem->find('all', array(
    'conditions' => $conditions,
    'contain' => array('Invoice' => array('Supplier')),
));

This doesn't seem to work though - the results I get back appear to be sorted on Supplier name ascending, but if I try it with descending order, it doesn't make any difference. I think the fact that the names are in order at all may just be a coincidence (I only have 3 of them, so they may just happen to be in order to start with).

Am I doing something wrong here? How can I make it sort in descending order?

Solution

I eventually resolved this by setting up a virtual field:

$this->Invoiceitem->virtualFields['supplierName'] = 
    'SELECT name FROM suppliers s JOIN invoices i ON s.id = i.supplier_id 
      WHERE i.id=Invoiceitem.invoice_id';

Seems to be working so far!

Answered By - Sharon