How should I link these models to query multi tables via CakePHP associations?

Question

I am using cakephp 2.3.2 and I need to do a query on multi tables.

I have this database:

--------------------------------------------------------------
|    Users     |    Agents     |    Companies    |    Ads    |
--------------------------------------------------------------
|      id      |      id       |       id        |    id     |
|    username  |      name     |     company     |   title   |
|    password  |    lastname   |      sector     |  message  |
|              |     user_id   |     user_id     |  user_id  |
--------------------------------------------------------------

These are the associations (Models):

User

hasOne Agent
hasOne Company
hasMany Ads

Agent

belongsTo User

Company

belongsTo User

Ad

belongsTo User

(NOTE: Please, keep in mind that when I add a new user that user could be an Agent OR a Company.)

QUESTION:

In my AdsController I have an action named view, there I read two params that I receve from Route:

$this->params['id']
$this->params['sector']

I need to do a query to check if the id is really associated to an Ad.id and If the sector is associated to Company.sector

I would like to check it with ONE find('first') instead of checking

If the ID exists
If the sector exists and it is associated to the user_id

How could I do it ?

(If the query finds Ad.id and Company.sector I need to retrieve all fields of Ad and Company)

At the moment my find('first') in AdsController/view is:

$options = array(
    'fields' => array(
        'Ad.*'
    ),
    'contain' => array(
        'User' => array(
            'Company' => array(
                'fields' => array(
                    'Company.*'
                ),
                'conditions' => array(
                    'Company.sector' => $this->params['sector'],
                ),
            )       
        )
    ),
    'conditions' => array(
        'Ad.id' => $this->params['id'],
    )
)

$data = $this->Ad->find('first', $options);
debug($data);

The problem is that Company is now shown in the result (array).

Please, keep in mind that I only need to retrieve the array IF:

The ID of the AD exists
The sector of the Company exists

If one of above are not "true" I would like to get an empty array.

Obviously I have added Containable behavior in Ad model.

Don't select *. Plus Cake will do this automatically. Company will be included as you've asked it to be included in your Contain. If no record matches the foreign key it will be empty. — David Yell
@DavidYell as I wrote the company is not shown. I only see Ad and User array in the result. — Dail

johhniedoe johhniedoe · Accepted Answer · 2013-04-09T12:27:34

I've seen this, had same problem that for some reason contain, kinda malfunctions with that array() structure, maybe it works how it means to be, but I dont get it, anyways to retrive chain dependencies i use this kind of construction:

$options = array(
        'contain' => array(
            'User',
            'User.Company' => array(
                'conditions' => array(
                    'Company.sector' => $this->params['sector'],
                ),
            )                   
        ),
        'conditions' => array(
            'Ad.id' => $this->params['id'],
        )
    )

But, you need to have in mind that conditions in contain are not the same as the conditions for main model, therfore even if the company sector does not exist but Ad with given id exits you will not receive an empty set. As far as I know you cannot use a condition to exclude results if there is no direct association between models. So you would still need to check if $result['User']['Company'] is an empty set or not.

One more thing your query array does not match structure you've provided with your question, there is no field sector in your Company table

How should I link these models to query multi tables via CakePHP associations?

1 Answers