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
