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. 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') )); }

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

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.