Retrieving related model's data in 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

What I would like to do is to retrieve events for a particular date based on company's id. What I have tried to do is the following:

$this->User->find('all', 
     array(
       'conditions' => array(
           'company_id' => CakeSession::read("Auth.User.company_id")
        ), 
       'contain' => array(
             'Event' => array(
                   'conditions' => array(
                         'Event.date' => date("Y-m-d", $tomorrow)
                    )
             )
        )
  ));

but this retrieves all of the events for the company, the date condition is not being applied. In the best case I would like to retrieve only the events for one company for a particular date. Otherwise I would get by with returning a list of users related to one company and their events for that particular date.

What would be the most efficient way to do this?

Any help or guidance is much appreciated.

Following are the relations between the tables:

Events:
    var $belongsTo = array(

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

Here is the query that I get:

SELECT Event.id, Event.customer_id, Event.user_id, Event.project_id, Event.service_id, Event.date, Event.start_city, Event.material, Service.id, Service.company_id, Service.name, Service.service_nr, User.id, User.company_id, User.employee_nr, User.name, User.surname, User.email, User.password, User.role, Customer.id, Customer.company_id, Project.name, Project.description, Project.link_nr, Project.start_date, Project.finish_date, Project.project_nr FROM schedule.events AS Event LEFT JOIN schedule.services AS Service ON (Event.service_id = Service.id) LEFT JOIN schedule.users AS User ON (Event.user_id = User.id) LEFT JOIN schedule.customers AS Customer ON (Event.customer_id = Customer.id) LEFT JOIN schedule.projects AS Project ON (Event.Project_id = Project.id) WHERE 1 = 1

The problem right now is that the company id is not taken into concideration, and all of the events are being returned no matter what date it is.

Why dont you make model of Events and make a relation with Users such that Events belongs to Users. — Moyed Ansari
– Moyed Ansari, Commented Mar 31, 2014 at 11:00
Events do belong to users, I will update the question with relations in a minute. — Domas
– Domas, Commented Mar 31, 2014 at 11:04
you containable is not working... As your comments on @Moyed Ansari 's answer , you should use $actsAs to bind Containable behavior not $actAs — Fazal Rasel
– Fazal Rasel, Commented Apr 28, 2014 at 18:05

Moyed Ansari · Accepted Answer · 2014-04-29 07:56:41Z

2

I’ve tried this solution and it works for me.

I made all tables with small letters according to cake conventions like companies, events and users.

This is Event Model

class Event extends AppModel {

    var $actsAs = array('Containable');

    public $belongsTo = array(
        'User' => array(
            'className' => 'User',
            'foreignKey' => 'user_id',
            'conditions' => '',
            'fields' => '',
            'order' => ''
        )
    );
}

This is controller Event class

$tomorrow = '2013-04-01';

$this->Event->find('all', 
 array(
   'conditions' => array(
       'date' => date("Y-m-d", strtotime($tomorrow)),
    ), 
   'contain' => array(
         'User' => array(
               'conditions' => array(
                     'User.company_id' => CakeSession::read ('Auth.User.company_id')
                )
         )
    )

));

This will give you

 SELECT `Event`.`id`, `Event`.`user_id`, `Event`.`details`, `Event`.`date`, `User`.`id`, `User`.`name`, `User`.`company_id` 
    FROM `schedule`.`events` AS `Event` 
    LEFT JOIN `schedule`.`users` AS `User` ON (`Event`.`user_id` = `User`.`id` AND `User`.`company_id` = 2) 
WHERE `date` = '2013-04-01'

I hope this will work for you. Thanks

edited Apr 29, 2014 at 7:56

answered Mar 31, 2014 at 11:10

Moyed Ansari

8,4612 gold badges38 silver badges57 bronze badges

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

17 Comments

Domas Over a year ago

Thanks for your quick response. There is some problem with the following double array after users and conditions: 'Users'=>'conditions'=>array('company_id'=>CakeSession::read ('Auth.User.company_id')))

Moyed Ansari Over a year ago

what is the problem ?

Domas Over a year ago

Error: syntax error, unexpected '=>' (T_DOUBLE_ARROW), expecting ')'

Domas Over a year ago

Unfortunatelly, I have just tested it again and the problem is that all of the events are being returned. No matter what user or company they belong to.

Moyed Ansari Over a year ago

it looks like containable is not working. please verify whether this behavior is attached to your model or not.

|

arilia · Accepted Answer · 2014-04-28 12:48:22Z

1

+50

try this code:

$this->Event->find('all', 
     array(
       'conditions' => array(
           'User.company_id' => CakeSession::read("Auth.User.company_id"),
           'Event.date' => date("Y-m-d", $tomorrow)
        ), 
       'contain' => array('User')
  ));

answered Apr 28, 2014 at 12:48

arilia

9,3982 gold badges24 silver badges47 bronze badges

Collectives™ on Stack Overflow

Retrieving related model's data in CakePHP

2 Answers 2

17 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

17 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related