So, Symfony is pretty cool and propel is blah, but there are projects where we were forced to use Propel. As such I just encountered a scenario where I had to join multiple tables to pull in needed data and ended up banging my head against the wall trying to figure out how to do it in a sensible and re-usable manner. After a few hours I reached a solution which prompted this post.

First a little background. PropelĀ  is a ORM (Object Relational Model) which maps php code (/lib/model files) to your database schema. This gives you access to CRUD (create, retrieve, update, delete) functionality using auto-generated classes.

Propel creates model files (php classes) using a <somedatabase>.xml file in /config. Within this xml file you will find table schemas such as table names, foreign keys, primary keys, and indices. This xml file tells propel how to build the php classes you will be using to gain access to stored data.

The actual creation of the model files is covered in my Symfony Setup guide so I won’t go over it here. What I did want to talk about is hydration. When I first heard this term I didn’t understand what it meant, and after reading the code that actually hydrates, I finally realized what it does. Hydration refers to setting all the object’s getters to values given by a 0-index array which lines up, exactly, with the columns from the source table. Still reading ‘blah blah’? Let me try to break it down:

Say you have a books table with the following row:

Title: MacBeth
ISBN: 1337

From an array perspective, Title would be the first element, so it would have an array key of 0 where ISBN would have an array key of 1. So when you want to hydrate an object you pass the row array to a $book object and it does the following (this method is automatically created by Propel within the Book.class.php file):

public function hydrate($row, $startcol = 0, $rehydrate = false) {

    try {

        $this->title = ($row[$startcol + 0] !== null) ? (int) $row[$startcol + 0] : null;
        $this->isbn = ($row[$startcol + 1] !== null) ? (string) $row[$startcol + 1] : null;
        $this->resetModified();

        $this->setNew(false);

        if ($rehydrate) {
            $this->ensureConsistency();
        }

        // FIXME - using NUM_COLUMNS may be clearer.
        return $startcol + 2; // 2 = KbbReviewPeer::NUM_COLUMNS - KbbReviewPeer::NUM_LAZY_LOAD_COLUMNS).

    } catch (Exception $e) {
        throw new PropelException("Error populating KbbReview object", $e);
    }

}

Notice the setting of setters for title and ISBN, it uses $startcol + 0, then $startcol + 1, which line up exactly with title and isbn in the $row array. This is hydration.

After an object has been successfully hydrated you can simply access it’s getters:

echo $book->getTitle()

Hydration is a cool thing as it allows access to data via OOP, which if crafted correctly turns into self-documenting code; in that it’s easy to read and understand just what the hell is going.

Dealing with single tables is easy /w respect to object hydration, but what if you need to access data from another table? There are a few ways to do this, but if you want to keep with the hydration method you have to manually hydrate the objects you want to use.

Here is a snippet of code to use as an example:

$c = new Criteria();

$c->addJoin(BookPeer::ID, AuthorPeer::BOOK_ID, Criteria::INNER_JOIN);

BookPeer::addSelectColumns($c);
AuthorPeer::addSelectColumns($c);

$stmt = self::doSelectStmt($c);

while ($row = $stmt->fetch(PDO::FETCH_BOTH)) {

    $row = array_change_key_case($row);

    $book = new Book();

    $book->hydrate($row);

    $book->setAuthorName($row['author_name']);

    $books[] = $book;

}//End while

return $books;

Ok, in the above code we told propel we want columns from both the books table and the authors table so we can display author information from one hydrated object. To do this we first did the join:

$c->addJoin(BookPeer::ID, AuthorPeer::BOOK_ID, Criteria::INNER_JOIN);

Then we told propel we want all columns from both tables:

BookPeer::addSelectColumns($c);
AuthorPeer::addSelectColumns($c);

We excute the query:

try {
    $stmt = self::doSelectStmt($c);
} catch (Exception $e) {
    throw $e;
}//End catch

If the query executed /w no problem, then we attempt to retrive the data:

try {

    while ($row = $stmt->fetch(PDO::FETCH_BOTH)) {

Because we set the fetch mode to ‘both’ we are going to be returned an array which is 0-index AND associative. So the first element in the array will be $row[0] = ‘MacBeth’ and $row['TITLE'] = ‘MacBeth’. B/c propel uppercases everthing I generally like dealing with lowercase text so that’s what the change key case does:

$row = array_change_key_case($row);

Now, $row['TITLE'] becomes $row['title']. You may be askingĀ  yourself; ‘Why is he returning both a 0-index AND an associative array?’ Well the reason is b/c the hydrate method is expecting a 0-index array, so that’s what we will give it. But we want an associative array so we can access columns from the other table using column names rather than their numerical order. It’s much easier to understand that we want $row['author_name'] instead of $row[3].

Now that we have access to what we needed, we hydrated the book object:

$book = new Book();

$book->hydrate($row);

So now all the getters have been set for the book object, but we now need to set our custom data:

$book->setAuthorName($row['author_name']);

Now, anywhere you want you can simply call $book->getAuthorName(). Pretty cool. There is one step we did miss to make this work. You will need to add the getter/setter for $authorname in the Book.php file. The reason for this is b/c the base version of book has no idea that author name even exists, it only knows what the xml file said was available at time of model creation.

So to add the custom getter/setter to the book object just add the following to /lib/model/Book.php:

protected $authorName;

public function getAuthorName() {
    return $this->authorName;
}//End getAuthorName()

public function setAuthorName($authorName) {
    $this->authorName = $authorName;
}//End setAuthorName()

You will have to do this for each column you want the book object to have access too.

That’s about it for manually hydrating propel objects to accomodate table joins. Hope you found this useful.