CakePHP (2.4) Deep Relationship

Question

I have a database:

 Companies    |    Users    |    Files
 - ID             - ID           - ID
                  - company_id   - user_id

So for my models:

• Company hasmany User
• User belongsto Company & hasmany File
• File belongsto User

How can I list al the files belonging to a company? Without the user and company data? So a single array listing files.

I could fetch all the user IDs followed by a query in File searching with these IDs. But I was wondering, is there a best practice cakePHP way?

I'm fairly new to cake.

Thanks

Answer 1

As far as I know it's not possible to get company files without any user or company data, but you can limit the data by using the Containable behaviour. For example, if you want to get files that belong to a company in the company controller you could use this:

$users = $this->Company->User->find('all', array(
    'conditions' => array('User.company_id' => $id),
    'fields' => array('id', 'company_id'),
    'contain' => array('File'),
));

The result ($users) would look something like this:

array(
    0 => array(
        'User' => array(
            'company_id' => '75',
            'id' => '51'
        ),
        'File' => array(
            0 => array(
                'id' => '399',
                'user_id' => '51',
                ...
            ),
            1 => array(
                'id' => '337',
                'user_id' => '51',
                ...
            )
            ...
        )
    )
    1 => array(
        'User' => array(
            'company_id' => '75',
            'id' => '65'
        ),
        'File' => array(
            0 => array(
                'id' => '450',
                'user_id' => '65',
                ...
            ),
            ...
        )
    )
)

You can then get an array of files using the Hash utility.

$files = Hash::extract($users, '{n}.File.{n}');

Which will give you something like this:

array(
    0 => array(
        'id' => '399',
        'user_id' => '51',
        ...
    ),
    1 => array(
        'id' => '337',
        'user_id' => '51',
        ...
    ),
    3 => array(
        'id' => '450',
        'user_id' => '65',
        ...
    ),
    ...
)

Don't forget to enable the behaviour. I suggest you do it in your app model by adding the following line:

public $actsAs = array('Containable');

Answer 2

CakePHP will not help you on this and even if want to write mysql query you cant do this by using files table only until and unless you will add company_id in files table.