So I have been working on a maintenance scheduler and wrote a custom sql
query for a upcoming maintenance report. Then converted it to a Cake built
statement. Awesome. Works great and doesn't break any of my datasources, or
model methods...
I have always wondered how something like this would pan out, and it works
wonderful. Building Subqueries with the buildStatement() method, using
custom joins, including using the subqueries as the source of the join...
even a cross join. The only thing that is a little spooky is I can't seem
to disable the ON clause for the cross join. Anyone know if that is
possible? I just set it to 1 = 1... but cross joins don't need ON clauses.
Cake Version of Original:
$dbo = $this->MaintenanceLog->getDataSource();
$MaxService = $dbo->buildStatement(
array(
'fields' => array('vehicle_service_task_id', 'vehicle_id',
'MAX(odometer_reading) as LastServiceMiles', 'MAX(`date`) as
LastServiceDate'),
'table' => $dbo->fullTableName($this->MaintenanceLog),
'alias' => 'MaxService',
'group' => array('vehicle_service_task_id', 'vehicle_id'),
'conditions' => array(),
'order' => null,
'limit' => null
)
,$this->MaintenanceLog
);
//pr($MaxService);
$MaxMileage = $dbo->buildStatement(
array(
'fields' => array('vehicle_id', 'MAX(mileage_beginning) AS
MaxStart','MAX(mileage_ending) AS MaxEnding','MAX(log_date) as
LastDailyLog'),
'table' => $dbo->fullTableName($this->MaintenanceLog->Vehicle->DailyLog),
'alias' => 'Dl',
'group' => array('vehicle_id'),
'conditions' => array(),
'order' => null,
'limit' => null,
'joins' => array(
array(
'table'=>$dbo->fullTableName($this->MaintenanceLog->Vehicle),
'type'=>'left',
'alias'=>'Vh',
'conditions'=>array('Vh.id = Dl.vehicle_id')
)
)
)
,$this->MaintenanceLog
);
//pr($MaxMileage);
$vehicleTasks = $this->MaintenanceLog->Vehicle->find('all',array(
'recursive' => -1,
'conditions' => array('NOT' => array('LastServiced.LastServiceDate' =>
NULL)),
'fields' => array('Task.id', 'Task.title','Task.mileage_interval' ,
'Task.date_interval' , 'Vehicle.id', 'Vehicle.name',
'MaxMileage.MaxEnding', 'MaxMileage.LastDailyLog',
'LastServiced.LastServiceMiles', 'LastServiced.LastServiceDate'
,'(DATEDIFF(CURDATE(),LastServiced.LastServiceDate)) AS DaysSinceService',
'(MaxMileage.MaxEnding-LastServiced.LastServiceMiles) AS
MilesSinceService'),
'joins' => array(
array(
'table' => $dbo->fullTableName($this->MaintenanceLog->VehicleServiceTask),
'alias' => 'Task',
'type' => 'CROSS',
'conditions' => '1=1'
),
array(
'table' => '('.$MaxService.')',
'alias' => 'LastServiced',
'type' => 'LEFT',
'conditions' => array(
'LastServiced.vehicle_id = Vehicle.id',
'LastServiced.vehicle_service_task_id = Task.id'
)
),
array(
'table' => '('.$MaxMileage.')',
'alias' => 'MaxMileage',
'type' => 'LEFT',
'conditions' => array(
'MaxMileage.vehicle_id = Vehicle.id'
)
)
)
));
Original Query:
SELECT Task.id, Task.title,Task.mileage_interval
, Task.date_interval
, Vehicle.id, Vehicle.name, MaxEnding, LastDailyLog,
LastServiced.LastServiceMiles, LastServiceDate
,(DATEDIFF(CURDATE(),LastServiceDate)) AS DaysSinceService,
(MaxEnding-LastServiceMiles) AS MilesSinceService
FROM `db_name`.`vehicle_service_tasks` Task
CROSS JOIN `db_name`.`vehicles` Vehicle
LEFT JOIN (
SELECT vehicle_id, MAX(mileage_beginning) AS MaxStart, MAX(mileage_ending)
AS MaxEnding,
MAX(log_date) as LastDailyLog
FROM `db_name`.`vehicle_daily_logs` Dl
LEFT JOIN db_name.vehicles Vh ON (Vh.id = Dl.vehicle_id)
GROUP BY vehicle_id
) AS MaxMileage ON (MaxMileage.vehicle_id = Vehicle.id)
LEFT JOIN (
SELECT vehicle_service_task_id, vehicle_id, MAX(odometer_reading) as
LastServiceMiles, MAX(`date`) as LastServiceDate
FROM `db_name`.`maintenance_logs`
GROUP BY vehicle_service_task_id, vehicle_id
) AS LastServiced
ON (LastServiced.vehicle_id = Vehicle.id AND
LastServiced.vehicle_service_task_id = Task.id)
WHERE LastServiceDate IS NOT NULL
--
Our newest site for the community: CakePHP Video Tutorials
http://tv.cakephp.org
Check out the new CakePHP Questions site http://ask.cakephp.org and help others
with their CakePHP related questions.
To unsubscribe from this group, send email to
[email protected] For more options, visit this group at
http://groups.google.com/group/cake-php
