Geoff's Mad Geek Skills: 2011

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

Saturday, March 5, 2011

Installing a Complete Web Development Machine on Windows 7 with CakePHP

The solution listed below is running PHP in IIS7, which actually works pretty good, all things considered.

1. Install Web Platform Installer

2. Install PHP Manager, PHP 5.3, CGI (including FastCGI), URL Rewrite Module (Not quite as bullet proof as Mod Rewrite, but very well done)

3. Install MySQL CE

4. Install MySQL Workbench (If you like GUI MySQL management)

5. Copy Over CakePHP to Library, and Add PHP executable path, and cake console path to Env PATH

I recently, installed Windows 7 on my laptop, (Thanks Chuck) and it gave me the opportunity to re-create my development environment.

Supposedly, Windows has a WebMatrix Package the creates a fully functional web server stack, but it uses SQL Server express, which always just seems so heavy to me.

CakePHP on Mac OSX

Ok, so some of you geeks out there may already know this, but I didn't so I will share it with you.

The newer version of Mac OSX has apache built into it. On OSX, Apache is called Web Sharing and the Web Server itself can be turned on in the System Preferences.

Also, PHP comes installed and ready to use via the command line.

If you want an actual Web Server stack there are a couple things you need to change to make it all work.
Htaccess – By default Mac disables the htaccess rewrites and stuff. So edit
the /etc/apache2/httpd.conf to AllowOverride in the Directory Directive.
PHP – By default Mac disables the PHP module. So edit the
/etc/apache2/httpd.conf and uncomment the PHP module.

And for those of us that wear the I heart CakePHP t-shirts...

To use the Cake Console directly from the command line – Add cake to the ENV path, by adding a simple file to the
/etc/paths.d directory that contains one line of the path to the cake console file.

And there you have it, a full blown Production Environment Grade Web Server right there in every Mac.

We use a Mac at work for preparing and uploading images to our web properties... I needed to tap a couple Soap APIs from the Mac and was getting ready to install the dreaded, bulky, MAMP. I was relieved when I found this fully functional web server built in, very cool.