Retrieving related model's data CakePHP

Question

Here is the database tables in question:

Companies:
____________________
| id | name |
____________________

|   1| Unimex|
|   2| Solomex|

Users:
________________________
| id | name | company_id
_________________________
|   1| John | 1
|   2| Ricky| 2

Events:
_____________________________________
| id | user_id | details | date|
_____________________________________
|   1|        1| null    | 2014-04-01
|   2|        1| null    | 2014-04-15
|   3|        2| null    | 2013-04-01
|   4|        1| null    | 2014-04-02

What I would like to do is to retrieve a list of users(based on company's id) and their related events for a range of dates. What I have tried to do is the following:

$users = $this->User->find('all',
        array(
            'conditions' => array(
                'company_id' => CakeSession::read("Auth.User.company_id")
            ),
            'contain' => array(
                'Event' => array(
                    'conditions' => array(
                        'Event.date >=' => $from,
                        'Event.date <=' => $to
                    )
                )
            )
        )
);

This way I get the list of Users with their related events, but the $from and $to dates are not taken into consideration, meaning all of the events for a particular user are returned.

The relations are like following:

Event:

var $belongsTo = array(
    'User' => array(
        'className' => 'User',
        'foreignKey' => 'user_id'
    ));

User:

        var $hasMany = array(
    'Event' => array(
        'className' => 'Event',
        'foreignKey' => 'user_id',
        'dependent' => false,
    )
);

        var $belongsTo = array(
        'Company' => array(
        'className' => 'Company',
        'foreignKey' => 'company_id',
        'dependent' => false,
    ),
);

Company:

    var $hasMany = array(
    'User' => array(
        'className' => 'User',
        'foreignKey' => 'company_id',
        'dependent' => false
    ));

Any help or guidance is much appreciated.

Following are the two queries that are executed by cakePHP:

SELECT `User`.`id`, `User`.`company_id`, `User`.`name`, `User`.`surname`, `User`.`email`,  `User`.`phone`, `Company`.`id`, `Company`.`name`, `Company`.`address` FROM `database`.`users` AS `User` LEFT JOIN `database`.`companies` AS `Company` ON (`User`.`company_id` = `Company`.`id`) WHERE `company_id` = 54

SELECT `Event`.`id`, `Event`.`customer_id`, `Event`.`user_id`,`Event`.`details`, `Event`.`hours`, `Event`.`minutes`, `Event`.`xhours`, `Event`.`xminutes`, `Event`.`assignment`, `Event`.`start_time` FROM `database`.`events` AS `Event` WHERE `Event`.`user_id` IN (124, 125, 126, 141, 143, 144, 147, 156)

So as you can see the date in the Event field conditions is not taken into consideration. If there is any other important information that I can provide, please, just let me know.

Moreover, I have just tried to retrieve only specific fields of the Event model, and that did not work as well. So basically the model is just contained as is, an no other parameter is applied. Or maybe the model is not contained at all and the result I get is only because of the recursive being set to 1?

can you add the resulting query of that find? to check if it is a problem with the query itself or how it is constructed by cake. — Nunser
– Nunser, Commented Jun 27, 2014 at 15:17
The Event table you show (id | user_id | details | date), has little to do with the one I see in the query (id, customer_id, user_id, hours, minutes, xhours, xminutes, assignment, start_time). Is that a typo or is there something your not telling us (like virtual fields maybe)? — Nunser
– Nunser, Commented Jul 1, 2014 at 17:13
Well these are just plain strings for holding irrelevant data in this case. I just wanted to make the visualization as simple as possible to understand, that is why I have skipped some of them. And I can assure you that there is no other relation, virtual field etc. — Domas
– Domas, Commented Jul 2, 2014 at 7:26

CoolLife · Accepted Answer · 2014-06-27 15:14:39Z

0

I don't see the error only as a suggestion

$users = $this->User->find('all',
        array(
            'contain' => array(
                'Event' => array(
                    'conditions' => array(
                       'and' => array(
                            array(
                                 'Event.date >=' => $from,
                                 'Event.date <=' => $to
                            ),
                       'company_id' => CakeSession::read("Auth.User.company_id")
                        )
                    )  
                )
             )
          )
    );

answered Jun 27, 2014 at 15:14

CoolLife

1,4895 gold badges19 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Domas Over a year ago

Tried that, but it still does not take the dates into consideration.

Domas Over a year ago

Still all of the events are returned, no matter what the date is.

Domas · Accepted Answer · 2014-07-02 08:27:30Z

0

The problem is that there was a typo. I always thought it has to be:

var $actAs = array('Containable');

But it actually had to be $actsAs.

answered Jul 2, 2014 at 8:27

Domas

1,1334 gold badges19 silver badges46 bronze badges

Collectives™ on Stack Overflow

Retrieving related model's data CakePHP

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related