Reputation: 1133
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.
Upvotes: 2
Views: 173
Reputation: 8461
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
Upvotes: 2
Reputation: 9398
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')
));
Upvotes: 1