http://groups.google.com/group/cake-php/browse_thread/thread/7059111f006ac694
I want to get all client records, where their associated invoiceitems
(client hasMany invoiceitems) have a particular condition.
For a while I thought this might not work, because even though the query I needed to modify would be run without the modifications, the query I wanted to do was impossible via normal SQL if I started with Clients as the first table in my FROM clause. This is because there are many invoice items for each client, so theres no 1:1 relationships for direct joining.
Instead, I tackled this from the other model, the model that belongsTo client.
/**
*returns a simple aray of Client ids and names,
*containing all clients that have invoiceitems without an invoice assigned to them.
*/
function getPendingClients()
{
$rtrnItems=array();
$this->restrict('Invoiceitem', 'Client');
$items=$this->findAll
(
array("`Invoiceitem`.`invoice_id`" => null),
$fields = array('DISTINCT Client.id', 'Client.name'),
$order = 'name'
);
if(!empty($items))
{
//simplify query results for a cleaner view
$newItems=Set::extract($items, '{n}.Client.id');
$ids = Set::extract($items, '{n}.Client.name');
$rtrnItems = array_combine($newItems, $ids);
}
return $rtrnItems;
//return $items;
}
As you can see, this uses distinct and binding to get just the data I need.
Semantically, I'd prefer to have this function working from the client model: after all, its a list of clients that I want, not invoice items!
Becuase of that I tried fiddling as suggested in the groups thread, by defining the join relationship on the fly, with code like this:
function getClientsForInvoicing()
{
/*
$this->restrict(
array('Client','Invoiceitem'=>array('conditions' => 'InvoiceItem.invoice_id IS NULL')
)
);*/
$ret= $this->find('all',
array
(
'restrict'=>
array('Client','Invoiceitem'=>
array('conditions' => 'InvoiceItem.invoice_id IS NULL')
)
)
);
return $ret;
}
With both calling restrict and using the restrict arg for find, I got different errors. Maybe my Cake version isnt ready for this new chaining of params to find, so theyre not ending up in the behaviour???
Frustrated, I gave up!! Grrr.
