Working with models which don't have a single foreign key in CakePHP

Question

I'm working on an application which has data imported from a foreign (and wholly unchangeable) source. I'll simplify some of the tables to explain the problem. Below are the tables with their primary keys.

invoices     (doc_number, date_printed, batch_number)
headers      (doc_number, date_printed, batch_number)
deliveries   (doc_number, date_printed, batch_number)
transactions (doc_number, date_printed, batch_number, line_number)
messages     (doc_number, date_printed, batch_number, line_number)

So you can see that Invoices, Headers and Deliveries have One-to-One relationships. Invoices-to-Transactions and Invoices-to-Messages are One-to-Many.

In importing these tables to my own database, I've changed the existing primary key to be a unique key and added an auto_incrementing field (id) on each table.

Now the problem is setting up the relationships in Cake, since it doesn't really handle composite keys at all. I've been able to get the One-to-One relationships working like this:

class Invoice extends AppModel {
    public $name = "Invoice"
         , $hasOne = array(
            "Header" => array(
                'foreignKey' => false,
                'conditions' => array(
                    "Invoice.doc_number = Header.doc_number",
                    "Invoice.date_printed = Header.date_printed",
                    "Invoice.batch_number = Header.batch_number"
                )
            )
         )
    ;
}

And this works, because the one-to-one relationships are queried in one go with a big LEFT JOIN. Trying the same method with a one-to-many relationship (eg, with Invoices and Transactions) dies, because Cake does two queries: the first to find all the matching Invoices, and then the second to find all the Transactions with a corresponding foreign key which matches the results from the first query: (here's the simplified query it's trying to run)

SELECT `Transaction`.* FROM `transactions` AS `Transaction`
WHERE `Invoice`.`doc_number` = `Transaction`.`doc_number`
AND `Invoice`.`date_printed` = `Transaction`.`date_printed`
AND `Invoice`.`batch_number` = `Transaction`.`batch_number`

You can see, it's not joining onto invoices so the query dies.

Any ideas about how I can make this work?

Answer 1

Worst case scenario, you can use the finderQuery parameter of the hasMany relationship. It allows you to completely override the queries.

You might also be able to add a unique id field that combines the relevant data fields into one. An UPDATE statement could fix existing data and a TRIGGER could be set up to update new records as they are added or changed, assuming that your database supports triggers.

Edit: With all of the other complexities, you might be better off skipping the hasMany relationship altogether and fetching the Transaction records in a separate find(). If you have to do it very many places you could always wrap it in a function in the Invoice model like this one:

<?php
class Invoice extends AppModel() {
    ...

    function getInvoice($conditions) {
        $invoice = $this->find('first', compact('conditions'));

        $conditions = array(
             'Transaction.doc_number' => $invoice['Invoice']['doc_number'],
             'Transaction.date_printed' => $invoice['Invoice']['date_printed'],
             'Transaction.batch_number' => $invoice['Invoice']['batch_number']);
        $invoice['transactions'] = $this->Transaction->find('all', compact('conditions'));

        return $invoice;
    }
}
?>

If you use a function like that in the model, don't forget that Invoice would need a defined relationship to Transaction, even if you don't use it directly, so that $this->Transaction is defined.

Also, if you prefer the data to be returned the way a hasMany relationship would be, you could always add a foreach loop to recombine them that way.

Update There's also a SimpleResults behavior in the Cake Bakery that would allow easily returning those results from the other table in the format of a regular hasMany association.

Answer 2

http://bakery.cakephp.org/articles/view/quick-tip-doing-ad-hoc-joins-in-model-find