Thursday 21 April 2011

CakePHP: Length of a virtual field's name

Perhaps the fact that I am accessing an MS SQL 2008 server from Linux using the freetds driver could be the cause of my troubles.

What I did find out today, is that when using the $virualFields property of a model the size of the virtual field's name, cannot exceed 14 characters.

My setup here is: CakePHP 1.3.8, running on CentOS 5.6 x86_64 using PHP 5.3.6-3 from Remi's repository.

And here is my case: My Model looks pretty much like this

class ArrivalPackage extends AppModel {
 var $name = 'ArrivalPackage';
 var $belongsTo = array('Arrival', 'Product', 'PackageType');
 var $hasMany = 'PackageLine';

 var $virtualFields = array(
   'reserved_date_gr' => 'CONVERT(NVARCHAR(10), ArrivalPackage.reserved_date, 105)',  //  not good 
   'loaded_date_gr'   => 'CONVERT(NVARCHAR(10), ArrivalPackage.loaded_date, 105)',
   'sold_date_gr'     => 'CONVERT(NVARCHAR(10), ArrivalPackage.sold_date, 105)'
 );
}

After I baked a view and controller for PackageLines I added a pr($packageLine); on the top of my view.ctp view file. The result was :

Array
(
    [ArrivalPackage] => Array
        (
            [id] => 3
            [arrival_id] => 3
            [product_id] => C2ΕLΙLΤ075000000075DULW4
            [supplier_package_number] => 345345345
            [shelman_package_number] => 1099228
            [SSCC] => 
            [min_length] => 2600
            [max_length] => 2600
            [package_type_id] => 1
            [expected_m3] => 0
            [total_pcs] => 20
            [total_m3] => 0.015
            [total_m] => 2
            [loaded] => 0
            [sold] => 1
            [reserved] => 1
            [loaded_date] => 
            [sold_date] => 2011-04-21
            [reserved_date] => 2011-04-18
            [reserved_user] =>  
            [loaded_date_gr] => 
            [sold_date_gr] => 21-04-2011
        )
    
    [0] => Array
        (
            [ArrivalPackage__reserved_date_] => 18-04-2011
        )
    
)

Only after I shortened the name of the reserved_date_gr field to rsvd_date_gr, did I get my expected

Array
(
    [ArrivalPackage] => Array
        (
            [id] => 3
            [arrival_id] => 3
            [product_id] => C2ΕLΙLΤ075000000075DULW4
            [supplier_package_number] => 345345345
            [shelman_package_number] => 1099228
            [SSCC] => 
            [min_length] => 2600
            [max_length] => 2600
            [package_type_id] => 1
            [expected_m3] => 0
            [total_pcs] => 20
            [total_m3] => 0.015
            [total_m] => 2
            [loaded] => 0
            [sold] => 1
            [reserved] => 1
            [loaded_date] => 
            [sold_date] => 2011-04-21
            [reserved_date] => 2011-04-18
            [reserved_user] =>  
            [resvd_date_gr] => 18-04-2011
            [loaded_date_gr] => 
            [sold_date_gr] => 21-04-2011
        )
)

Funny thing is that the actual sql statement produced by Cake, no matter what you name the field is valid for SQL Server, still unless the field name becomes smaller than 14 characters things don't work as you would expect them.

Tuesday 19 April 2011

CakePHP: Storing paging info in the Session

Warning: This post applies to CakePHP 1.3. If you are using CakePHP 2, then visit the updated version available through here.

There is no way you can miss it. Sooner or later you 'll end up having baked your controllers and views only to discover that when you edit a record from the 4th page and press submit on the edit page, much to your annoyance you end up on page one. Needless to say that whatever sort order was there is now gone with the wind.

I have googled a bit and found out the following article in Stack Overflow. The solutions provided over there seemed rather complicated to me, so I decided to craft my own.

Now here is the deal: From what I have seen in cake 1.3, paging is controlled by the value of three parameters: page, sort and direction. All we need to do is provide a way to store them in the session and then have the controller's redirect method inject these back into the URL parameters when they are not present.

<?php
     
class AppController extends Controller {
    const SESSION_KEY = 'PagingInfo.%s.%s';
        
    public $components = array('Session');            

    protected function savePagingInfo()
    {
        // create an array to hold the paging info
        $argsToSave = array();

        // remove paging info from passed args
        foreach( $this->passedArgs as $key => $value)
            if ($key == 'page' || $key == 'sort' || $key == 'direction')
                $argsToSave[$key] = $value;

        // abort if any paging info was found
        $sessionKey = $this->buildSessionKey();
        if (!empty($argsToSave)) {
            $this->Session->write( $sessionKey, $argsToSave);
            return;
        }
        
        // no paging info let's see if we have something in the sesion
        if ($this->Session->check($sessionKey)) {
                $pagingInfo = $this->Session->read($sessionKey);
                $this->passedArgs = array_merge( $this->passedArgs, $pagingInfo);
        }
    }
                        
    private function buildSessionKey($action = NULL)
    {
        if (empty($action))
            $action = $this->action;
            
        return sprintf( self::SESSION_KEY, $this->name, $action);
    }
}
     
?>    

This approach may be simplistic but in my case it worked. All I had to was call $this->savePagingInfo(); from my index() action and then let the rest of the baked code do its work.

Note

And one last comment, there are many cases that you need paging during display of master detail record sets. In cases like these savePagingInfo() will save paging status for the detail records matching the current master and when the master changes, the paging in the session becomes invalid. My simple solution to this is to add a 'page' => 1 entry in the $url params pointing to a master record change. With the design of the application I am currently working on this proves just good enough.