SQL function hack for current Library.

borconi

This is how I got SQL functions to work with the current Editor Library, it is HACKY but for what I need it works, might not be suitable for everybody and for every case, so tread carefully.

1) Edit Database\Query.php and change the _build_filed function to look like this:

    protected function _build_field( $addAlias=false )
    {
        $a = array();
        

        for ( $i=0 ; $i<count($this->_field) ; $i++ ) {
            $field = $this->_field[$i];
            
            // Keep the name when referring to a table
            if (strpos($field, ' fake_as ')) {
            
            $field=str_replace("fake_as","as",$field);
            $field=str_replace("|",",",$field);
            $split = explode(' as ', $field);
            
            $a[]=$this->_protect_identifiers( $field );
            $this->_field[$i]=trim(str_replace("`","",$split[1]));
            
            }
            else if ( $addAlias && strpos($field, ' as ') !== false && $field !== '*' ) {
                
                $split = explode(' as ', $field);
                $a[] = $this->_protect_identifiers( $split[0] ).' as '.
                    $this->_field_quote. $split[1] .$this->_field_quote;
            }
            else if ( $addAlias && strpos($field, ' as ') === false && $field !== '*' ) {
                
                $a[] = $this->_protect_identifiers( $field ).' as '.
                    $this->_field_quote. $field .$this->_field_quote;
            }
            
            else {
                $a[] = $this->_protect_identifiers( $field );
        
                
            }
        }

        return ' '.implode(', ', $a).' ';
    }

2) Edit the Field.php find the following line:

/** @var boolean */
    private $_xssFormat = true;

and insert the following text:

    /** @var saved_query **/
    private $_savedField = null;

In the same file replace the dbField function with the following code:

public function dbField ( $_=null )
    {
        if ( $_ === null ) {
             if (stripos ($this->_dbField, ' fake_as ') )
             {
                 $this->_savedField=$this->_dbField;
                 $this->_dbField=$this->_name;
             }
            return $this->_dbField;
        }

        if ( stripos( $_, ' as ' ) ) {
            $a = preg_split( '/ as /i', $_ );
            $this->_dbField = trim( $a[0] );
            $this->_name = trim( $a[1] );
        }
        else {
            $this->_dbField = $_;
        }

        return $this;
    }

Immediately after the dbField function insert one more function like this:

    public function dbSavedField ()
    {
        return $this->_savedField;
    }

3) Inside the Editor.php find the function _get ($id=null, $http=null) and change this bit:

    // Add all fields that we need to get from the database
        foreach ($this->_fields as $field) {
            if ( $field->apply('get') && $field->getValue() === null ) {
                $query->get( $field->dbField() );
            }
        }

to:

        // Add all fields that we need to get from the database
        foreach ($this->_fields as $field) {
            if ( $field->apply('get') && $field->getValue() === null ) {
                if ($field->dbSavedField()===null)
                    $query->get( $field->dbField() );
                else
                    $query->get( $field->dbSavedField());
                
            }
        }

Done with the editing, you can now simply specify your function inside the Field instance like this:

Field::inst( "DATE_FORMAT('2009-10-04 22:23:00'| '%W %M %Y') fake_as `clients.oneday`",'clients.oneday' )

Some notes, you will need to use | instead of , inside your field definition, also when naming your function use fake_as keyword instead of as and you must specify the alias one more time so Editor can name the fields correctly.

Enjoy.

allan

Very clever! Thanks for sharing this with us

Allan

borconi

Thanks, hopefully I give you an idea for your next release, of course with a more proper implementation rather than this quick afternoon workaround.

allan

It won't be in 1.6 I'm afraid, but it is something that I plan to introduce in future.

Allan