Complex find queries, with conditions in the associated model

I described my problem here:

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.