function getBudgetItemSelectionList(){
$this->virtualFields['select_list_value']
= 'CONCAT(BudgetItem.name,if(BudgetCategory.income = 1," (Income)",""))';
return $this->find('list',array(
'fields'=>array('BudgetItem.id','BudgetItem.select_list_value','Budget.name'),
'order'=>'BudgetItem.name ASC',
'recursive'=>0,
'conditions'=>array('Budget.state_id'=>'0')
));
}
Monday, December 19, 2011
Using CakePHP Virtual Fields on the Fly
CakePHP virtualFields are awesome.
Here is an example of using them on the fly.
This code returns an value label pair for a Cake Form Helper Select List.
Tuesday, December 13, 2011
Using CakePHP to build semi-custom queries.
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
Subscribe to:
Posts (Atom)