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')
));
}
Showing posts with label Programming. Show all posts
Showing posts with label Programming. Show all posts
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.
Labels:
CakePHP,
Programming
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
Labels:
CakePHP,
Programming
Thursday, July 24, 2008
Learning Object Oriented Programming
The majority of my programming experience is from PICK/BASIC on jBase running on a Redhat Linux Server.
The more I do in Pick/Basic the more I become interested in learning new lauguages and branching out to other platforms. In my attempts to branch out and learn new languages I have found interest in mostly Ruby on Rails. But they are different than what I am used to seeing, and they seemed to have a common theme. I have since learned that Ruby is a Object Oriented Language.
I have found this wikipedia page very helpful in explaining Object Oriented Programming.
http://en.wikipedia.org/wiki/Object_oriented
... to be continued.
The more I do in Pick/Basic the more I become interested in learning new lauguages and branching out to other platforms. In my attempts to branch out and learn new languages I have found interest in mostly Ruby on Rails. But they are different than what I am used to seeing, and they seemed to have a common theme. I have since learned that Ruby is a Object Oriented Language.
I have found this wikipedia page very helpful in explaining Object Oriented Programming.
http://en.wikipedia.org/wiki/Object_oriented
... to be continued.
Labels:
OOP,
Programming
Subscribe to:
Posts (Atom)