Posts tagged ORM

doctrineLogo

PHP – Doctrine – Soft Delete – Multiple Rows at a Time

1

One of the big fallacies of working with an ORM is that behind the scenes there may be high number of queries being executed to support the application. For example, say you want to delete several messages from a user’s inbox. We create a simple form; message subject, time message was sent, status (read, unread) and a checkbox. The checkbox is used for selecting specific messages upon which we wish to conduct a specific action, let’s say delete for example. If I select ten messages at a time, for delete, and follow the normal ORM process, I would have to load each message object, then call the delete() method for each one. If you tail your query log you will notice that you just issued twenty queries; ten queries to load the message, then ten messages to delete the object. Why all this overhead when we can use MySQL’s ‘IN’ clause and issue only one delete query?

At first I added a ‘status_id’ column for each message, which would indicate if the message was in a ‘deleted’ state, otherwise known as a “soft delete”. But then I remembered that Doctrine offers this behavior via the schema config file using the ActAs: SoftDelete construct. When done reading I learned that I needed to add a ‘deleted_at’ column to my user_message table to support Doctrine’s soft delete behavior. After some thought I agreed that this would be a better approach, as opposed to using a ‘status_id’ column. Mainly because there is a precedent regarding datetime columns and Doctrine behaviors, for example created_at and updated_at; timestampable behavior.

Unfortunately, in order to take advantage of the SoftDelete behavior, I would have to issue the aforementioned twenty queries, but this time when I call the delete method on each object, it would only update the deleted_at column, vs doing a hard delete (removing it from the table permanently). So how could I take advantage of the SoftDelete behavior AND using only one query, after some tinkering I came up with the following DQL statement:

public function unlink(array $messageIds)
{
    $q = Doctrine_Query::create()
        ->update('UserMessage')
        ->set('deleted_at',  new Doctrine_Expression('NOW()'))
        ->whereIn('id', $messageIds)
        ->limit(count($messageIds));
        
    $q->execute();
}

Notice the name of the method, I prefer using link/unlink naming convention, to indicate that I may be deleting one or many rows, and the method is strategically placed within the correct class, in case you have a pivot table breaking up a M:M relationship, or on a source table with no regard to any ancillary tables.

With the above example, you now only need issue one query to affect multiple rows, and still use the MySQL server’s timezone, vs using PHP date functions which rely on the local systems timezone.

doctrineLogo

PHP – Doctrine – DQL – Select Subquery

0

Whilst working on a recent project I needed the ability to do a subquery look-up as part of a select statement. Basically I needed to count the number of rows, without having to do a group by on the entire result set. After a few minutes of research I noticed there was no real definitive source on how to do do a subquery as a select using DQL, although there were some references to using a subquery within a where clause.

Below is a code snippet which worked for me:

$q = Doctrine_Query::create()
    ->select('t1.*')
    ->addSelect('t2.*');

// You can put this anywhere within the select clauses, but must be done before  you make call to from()        
$subQ = $q->createSubquery()
    ->select('COUNT(*)')
    ->from('Table3 t3')
    ->innerJoin('t3.Table4 t4')
    ->innerJoin('t4.Table5 t5')            
    ->where('t4.status_id = :statusId')
    ->andWhere('t5.id = t2.code_id');
        
$q->addSelect(sprintf('(%s) AS my_count', $subQ->getDql()))
    ->from('Table1 t1')
    ->innerJoin('t1.Table2 t2')
    ->where('t2.user_id = :userId', array('statusId' => $statusId, 'userId' => $userId);

// And you should now be able to access the count value using:
$result = $q->execute();

var_dump($result->my_count);

// Depending on how you have your models setup, you may have to do this instead:
var_dump($result->getFirst()->getMyCount();

Note, the table names are abstract for obvious reasons, but the snippet should demonstrate the ability to add a select subquery to a dql statement.

Here’s another example usage:

$q = Doctrine_Query::create()
    ->select('uh1.*');
        
$subQ = $q->createSubquery()
    ->select('uh2.user_id')
    ->from('UserHash uh2')
    ->where('uh2.hash = :hash');
        
$q->addSelect(sprintf('(%s) AS derived_user_id', $subQ->getDql()))
    ->from('UserHash uh1')            
    ->where('uh1.type_id = :typeId')
    ->having('uh1.user_id = derived_user_id', array('hash' => $hash, 'typeId' => $typeId));
        
return $q->execute();
symfony_logo

Symfony – Propel – Determine Propel Version

0

I was recently working on a project with Symfony 1.2 and wanted to know the version of Propel that was bundled with it. After some toying around with the `php symfony help` command, I noticed that no commands regarding version fell under the propel header, I was hoping for something like `php symfony propel:version`. Because I still needed to know the bundled Propel version I ended up issuing the following command:


$ -> ack -i version /path/to/symfony/lib/plugins/sfPropelPlugin/lib/vendor/propel/Propel.php

* @version $Revision: 989 $
 * The Propel version.
 const VERSION = '1.3.0-dev';

According to the source code, the version of Propel bundled with Symfony 1.2 is 1.3.0-dev (yikes). So if you want to see the version of Propel bundled with the version of Syfmony you are using, just use the above `ack` (or fgrep) command.

On a side note, if you are using Symfony 1.x, you should look into the sfPropelORMPlugin, it will allow you to use the latest version of Propel (1.6) with Symfony 1.x, which is good because there are several new features in Propel 1.6 that make developing easier and improve performance. And they actually added the ability to view the version without having to ack the codebase.

Symfony /w Propel – Table Joins && Manual Object Hydration

2

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.

Go to Top