Using \Zend_Db_Table_Abstract::find($id). MySQL SET field returns string instead of (wanted) int

Question

Basic question How can I fetch the 'type' column as integer value from inside the table mapper?

I have a PHP Zend Framework 1.12 application running a website. Inside MySQL are multiple tables with multiple columns. Inside two tables I use the SET type. The column is named 'type' and as 'set('LOCAL','EXTERNAL')'. Don't mix up this field type with the ENUM please!

So far no problems, querying the table and fetching the type column as INT or STRING is not a problem:

$Sql = $Db->select()->from('tablename', ['type_as_int' => new \Zend_Db_Expr('type+0')]); //returns INT (if both are selected: 3)
$Sql = $Db->select()->from('tablename', ['type']); //returns STRING (if both are selected: LOCAL,EXTERNAL)

But, in this application also has table mappers that extend Zend_Db_Table_Abstract. Inside the mapper resides the 'find()' method. Default built in into the abstract to find records by their primary key. But.. When I use the object to fetch a record , I find the following response inside my populate method:

array([type] => LOCAL,EXTERNAL) 

Querying it by hand (and defining the columns myself) would be an options ($this->select()->from...), but isn't there a more elegant way?

(I know that I am using an older version of ZF, but upgrading would cost too much time at this moment.)

Paranoiac

Why dont you store the field as INTEGER instead of SET, and use bitmapindex for your purpose. – Regeneration

Answer 1

After the bounty was started I noticed that there wasn't a really simple anwer, so I began looking deeper into Zend Framework 1.12 and the mapper objects that I use. I noticed that the 'find()' method just uses the primary key columns to build a query. So starting with that knowledge I built my own 'find()' method which resides in the 'abstract model mapper' and uses the 'find()' mapper inside the class that extends \Zend_Db_Table_Abstract

/* sample abstract mapper class with find */
abstract class MapperAbstract {

    /*
     * Zend db table abstract object
     * @var \Zend_Db_Table_Abstract
     */
    private $DbTable;

    public function find($id, $Model) {         
        $Select = $this->$DbTable->select(\Zend_Db_Table_Abstract::SELECT_WITH_FROM_PART);

        //Fetch record and populate model if we got 
        //a result
        $Row = $this->$DbTable->fetchRow($Select);

        //do some extra shizzle

        if ($Row !== null) {
            return $Model->populate((array)$Row);
        }
        return;
    }

}

Now I need to add a method that overrides the default columns. So I created a method called 'overrideColumns()' that return an array filled with column names that need to be selected or must be overriden.

/**
 * Returns array with columns that need to be overridden
 * or selected as extra
 * @return array
 */
public function overrideColumns() {
    return ['type' => new \Zend_Db_Expr('type+0')];
}

And from that point I only needed to adjust the $Select query so it would use the 'overrideColumns()' method. So the full class becomes something like:

/* sample abstract mapper class with find */
abstract class MapperAbstract {

    /*
     * Zend db table abstract object
     * @var \Zend_Db_Table_Abstract
     */
    private $DbTable;

    /**
     * Returns array with columns that need to be overridden
     * or selected as extra
     * @return array
     */
    private function overrideColumns() {
        return ['type' => new \Zend_Db_Expr('type+0')];
    }

    public function find($id, $Model) {         
        $Select = $this->DbTable->select(\Zend_Db_Table_Abstract::SELECT_WITH_FROM_PART);

        //Check if we need to override columns in the select query
        $overrideColumns = $this->getOverrideColumns();
        if (!empty($overrideColumns)) {
            $Select->columns($overrideColumns); //overrides the columns
        }

        //Add where clause to the query
        //I know there can also be a compound primary key, but
        //I'm just ignoring that in this example
        $Select->where($this->DbTable->getPrimaryKeyColumn() . ' = ?', $id); 

        //doing some extra shizzle
        //that is not important when I want to explain stuff

        //Fetch record and populate model if we got a result
        $Row = $this->DbTable->fetchRow($Select);

        if ($Row !== null) {
            return $Model->populate((array)$Row);
        }
        return;
    }

}

So.. after a while I found the answer I was looking for, without having to declare all columns.

Paranoiac