Selecting arbitrary columns using propel

Generally we cannot use hydrate() with a query where we specify our own select columns. Propel always assumes that statements passed to hydrate() contain the full properties for the object. But of course we can still get the object(s) by populating the statements manually.

while ($row = $stmt->fetch (PDO::FETCH_NUM))
{
   $myClass = new myClass();
   $myClass->setId($row[0]);
   ....
   $myClasses[] = $myClass;
}
return $myClasses;

But the problem will arise when we are working with sfPropelPager, because sfPropelPager only works with Object.
So, how to return the propel object(s) when we want to select only a few columns?. In this article, i would like to share my idea how to solve the problem stated above.

addSelectColums() and hydrate() function

The problems above can be solved by manipulating addSelectColumns function (BaseXXXPeer) and hydrate function (BaseXXXObject). If you look carefully at the two functions, you can immediately conclude, that the hydrate function is closely related to addSelectColumns function. So when we want to select a few columns, then we have to modify the hydrate function, so that the selected column will correspond with the hydrated columns. Let us consider the example below:

public static function addSelectColumns(Criteria $criteria)
{
    $criteria->addSelectColumn(ExcelPeer::ID);
    $criteria->addSelectColumn(ExcelPeer::NAME);
    $criteria->addSelectColumn(ExcelPeer::CREATED_AT);
}
public function hydrate($row, $startcol = 0, $rehydrate = false)
{
  try {
    $this->id = ($row[$startcol + 0] !== null) ? (int) $row[$startcol + 0] : null;
    $this->name = ($row[$startcol + 1] !== null) ? (string) $row[$startcol + 1] : null;
    $this->created_at = ($row[$startcol + 2] !== null) ? (string) $row[$startcol + 2] : null;
    ...
    return $startcol + 3; // 3 = ExcelPeer::NUM_COLUMNS - ExcelPeer::NUM_LAZY_LOAD_COLUMNS).
  } catch (Exception $e) {
    throw new PropelException("Error populating Excel object", $e);
  }
}

For example, say that we only want to select two columns, id (ExcelPeer:: ID) and name (ExcelPeer:: NAME) respectively, so the code $this->created_at = ($row [$startcol + 2]! == null) ? (String) $row[$startcol + 2] : null; in the hydrate function should be removed and its return value should be $startcol + 2 (number of selected columns).
Ok, now we’ve got a clear picture on how to manipulate addSelectColumns and hydrate functions. Let’s go to the next step.

Implementation

How to know which columns that will be selected by users? Before the users do ‘doSelectXXX’, they have to specify the selected columns first.
Open your peer class, and add the script below:

protected $selectedColumns = array();

public static function setSelectColumns(array $selectedColumns) {
   self::$selectedColumns = $selectedColumns;
}

public static function getSelectedColumns() {
   return self::$selectedColumns;
}

/*this function used to get the starting column when the user do doSelectJoinXXX*/
public static function getStartColumn() {
   if (empty(self::$selectedColumns)) {
      return self::NUM_COLUMNS - self::NUM_LAZY_LOAD_COLUMNS;
   } else {
      return count(self::$selectedColumns);
   }
}

Then override the addSelectColumns and hydrate functions. In your peer class, add the script below:

public static function addSelectColumns($criteria) {
   if (empty(self::$selectedColumns)) {
     return parent::addSelectColumns($criteria);
   } else {
     foreach (self::$selectedColumns as $selectedColumn) {
       $criteria->addSelectColumn($selectedColumn);
     }
   }
}

and in your object class:

public function hydrate($row, $startcol = 0, $rehydrate = false) {
   $selectedColumns = YourPeer::getSelectedColumns();
   if (empty($selectedColumns))  {
      return parent::hydrate($row, $startcol, $rehydrate);
   } else {
      foreach ($selectedColumns as $key => $selectedColumn) {
         $this->setByName($selectedColumn, $row[$key], BasePeer::TYPE_COLNAME);
      }
      $this->resetModified();
      $this->setNew(false);
      if ($rehydrate) $this->ensureConsistency();
      return $startcol + count($selectedColumns);
   }
}

But what we have done above still not able to cover the doSelectJoinXXX, why? If you have a look at the doSelectJoinXXX function generated by propel, the starting hydration column is always stay the same.

$startcol = YourPeer::NUM_COLUMNS - YourPeer::NUM_LAZY_LOAD_COLUMNS;

In our case, the statement above will produce an error, because the number of selected colums is always vary. So, how to fix the problem above? What we have to do is override the doSelectJoinXXX function(s) and change the YourPeer::NUM_COLUMNS – YourPeer::NUM_LAZY_LOAD_COLUMNS above by our getStartColumn function.
Copy the doSelectJoinXXX function(s) from your base peer to your peer, here is the example taken from my project:

....
ExcelSheetPeer::addSelectColumns($criteria);
$startcol2 = ExcelSheetPeer::getStartColumn();

ExcelPeer::addSelectColumns($criteria);
$startcol3 = $startcol2 + ExcelPeer::getStartColumn();
....

How To Use?

You can read here

Tired to copy paste your code? The good news is i’ve packaged it as a plugin, sfPropelCustomSelectPlugin :D. Clone it and enjoy!!!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: