CODEWITHSUNDEEP
cakephp
Sharon
Sharon

Reputation: 3909

CakePHP - sorting on deeply associated field

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?

Upvotes: 0

Views: 98

Answers (2)

Sharon
Sharon

Reputation: 3909

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!

Upvotes: 0

Kai
Kai

Reputation: 3823

I find deep operations don't work very well with containable, and so I often resort to manual joins. But in this case you may be able to force it by doing something like the following, since if you make an option that CakePHP can't parse, it'll just default to inserting the unparsable part directly into your query.

$itemlist = $this->Invoiceitem->find('all', array(
     'conditions' => $conditions,
     'contain' => array('Invoice' => array('Supplier')),
     'order' => 'Supplier.name desc, Invoiceitem.id asc',
));

Upvotes: 1

Related Questions