CODEWITHSUNDEEP
cakephpmodelassociations
Kez
Kez

Reputation: 760

Associating multiple models in CakePHP

I am currently working on an application in CakePHP to display (but not modify) the contents of a legacy database. I am not able to change the database structure in any way.

There are six tables I am working with and the relationships are outlined in this awful paint diagram:

DB relations

I have currently defined the models as follows:

http://pastebin.com/4aEaSnQp

(I decided to put it in a PasteBin as it is a bit of a wall of text, I am happy to edit it into the post if preferred).

What I would like to do is use one controller to pass relevant linked data from my controller to the view from essentially all of these tables. However, even if I set recursion to 2 the links only seem to go so far - for example if I try to find data from the 'Ctit' table with code like this:

$this->set('contracts',$this->Ctit->find('all',
            array(
            'recursion' => 2,
            'conditions' => array('Title.TITNO' => $id),
            'fields' => array('Contract.CONNO','Title.TITLE','Contract.CONDATE','Territory.DESCRIPTION')
            )
        ));

The resulting query does not include the "Territory" table and so throws back an error.

What am I doing wrong here? Is it possible to retrieve the data in this way or might I have to run multiple finds or write my own queries (something I am hoping to avoid)?

Thanks in advance,

Kez

Upvotes: 0

Views: 51

Answers (1)

Nunser
Nunser

Reputation: 4522

So... condensing the comments.

Basically it's a Containable problem. To get everything related to a single model (meaning, the 5 other models), a way to do it would be like

$this->Ctit->find('all', array(
                         'contain' => array(
                            'Royalty',
                            'Title',
                            'Contract' => array('Publisher', 'Territory')
)

You can also add options to that array, like any other query, for example order, fields, conditions, etc.
Example:

$this->Ctit->find('all', array(
                         'contain' => array(
                            'Royalty' => array('order' => 'id DESC'),
                            'Title' => array('fields' => array('id', 'name'),
                            'Contract' => array('Publisher', 
                                                'Territory' => array('conditions' => array('name' => 'terr'))
)

One thing to have in mind is to always use 

public $actsAs = array('Containable');

(mind that s in actsAs, some people have had trouble because of that) otherwise your models won't behave containabl-y and you'd think this whole thing doesn't work.

Containable behaviour is useful for this, because you don't have to do a lot of finds, but keep in mind that cake does a lot of queries behind scenes to use containable. If you want this to be just one big query, use joins (found it!)

Also, be aware that this query might get big real quick. You are basically asking for all records with all 5 associations, when you reach a memory limit notice you are going to remember me. I doubt you want to show all data for every record on one view, so reconsider, maybe a simple paginate first and then a single view for each single record (in that case, the find will be a find('first') and that's lot better than find('all').

Upvotes: 1

Related Questions