Posts tagged Doctrine

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();
doctrineLogo

Doctrine – PHP – Compare two Doctrine_Record Objects

0

As part of a recent project to reduce the number of “rogue” update statements issued when a Symfony form (with embedded forms) was saved I had to create an algorithm to compare two Doctrine_Record objects, to determine if an update should occur or not, so I thought I would share with the community.

Below is the logic, however it does not follow the DRY (Don’t Repeat Yourself) principle, I did it this way on purpose so you can easily implement into your codebase.


$array1 = array('id' => 1, 'name' => 'element1');

$array2 = array('id' => 1, 'name' => 'element1');

// For this too work, these objects must extend from Doctrine_Record class

$object1 = new Object();

$object2 = new Object();

// fromArray will hydrate the object with values in the array

$object1->fromArray($array1);

$object2->fromArray($array2);

// Due to PHP being a loosely typed language, there were instances where, after hydration, some is_numeric scalars were strings or ints

// So this will ensure all is_numeric scalars are ints, which ensures an apples to apples comparison

$objectArray1 = array_map(function($value) { return (is_numeric($value)) ? (int) $value : $value; }, $object1->toArray(false));

$objectArray2 = array_map(function($value) { return (is_numeric($value)) ? (int) $value : $value; }, $object2->toArray(false));

$hash1 = md5(serialize($objectArray1));

$hash2 = md5(serialize($objectArray2));

$compareStatus = ($hash1 === $hash2) ? true : false;

symfony_logo

Symfony – Doctrine – Call to a member function evictAll on a non-object

1

If you are working with Symfony and Doctrine and encounter a “Call to a member function evictAll() on a non-object” error message, check to see if you are using DQL for any delete queries. I just came across this error and after some googling (mostly about bad schema files) I couldn’t fix the issue. I then checked a custom DQL statement which unlinks rows and saw that I had aliased the main table with ‘caa’, but the other references were ‘ca’ (only one a, instead of two). Once I fixed the alias issue, everything worked as expected.

Doctrine – Fetching Objects

2

Last night I was reading up on doctrine and came across a link to some basic, yet helpful info.

You should always use array hydration when you only need to data for access-only purposes, whereas you should use the record hydration when you need to change the fetched data.

Interesting. I’ve always tried to always use objects for reading data etc, but. but apparently doctrine has optimized the array population algorithm.

FYI, their call to

$q->execute(array(), Doctrine::HYDRATE_ARRAY);

Appears to be the same as:

$q->fetchArray();

Symfony – Form Save Processes Outlined

1

In the never ending quest to figure out how to successfully embed symfony forms I found myself always opening /symfony/lib/plugins/sfDoctrinePlugin/lib/form/sfFormDoctrine.class.php to study the order of operations for saving data against a form object. Finally figured it was time to post it somewhere where I could easily get to it and may help other devs along the way.

Note: This is a living doc and may be updated over time.

  1. sfFormDoctrine::save($con)
    1. $con->beingTransaction()
    2. $this->doSave($con)
      1. $this->updateObject()
        1. $this->processValues()
        2. $this->object->fromArray($values)
        3. $this->updateObjectEmbeddedForms($values)
      2. $this->object->save($con)
      3. $this->saveEmbeddedForms($con)
    3. $con->commit()
Author_Book Many-To-Many

Symfony – Doctrine – Saving Many-To-Many (M:M) relationships

75

Pretty much one of the hardest things I had to get my head around, saving many-to-many (M:M) relationships in Symfony (using Doctrine), but after some mindset adjustment and a lot of googling, I found it to be very easy to implement. It took several weeks and perusing several different google searches to piece together enough information to finally get my head around this process, hopefully this post will save some babies from being punched due to frustration.

Schema first. Say you have a M:M relationship between books and authors, meaning that an author can write more than one book (many) and a book can have more than one author (many). The schema to support this requires that we have a 3rd table, to which I refer as a linking table. The linking table’s main purpose is to link data from the author’s table to the data from the book’s table, which forces us into 2NF (second normal form), by ensuring we are not saving duplicate data in either the author table or the book table.

Your schema should look something like the following (remember that your table engines must be innodb to establish a FK relationship):

Author_Book Many-To-Many

Author_Book Many-To-Many

Now we need to rebuild the schema so we can rebuild the model to take advantage of these relationships established by the schema.

php symfony doctrine:build-schema

Now that the schema has been rebuilt, we need to do some manual edits to ensure the model files are created correctly. So open /config/doctrine/schema.yml and you should see something like:

Author:
  tableName: author
  columns:
    ...
  relations:
    LinkingAuthorBook:
      local: id
      foreign: book_id
      type: many
Book:
  tableName: book
  columns:
    ...
  relations:
    LinkingAuthorBook:
      local: id
      foreign: author_id
      type: many

After

Author:
  tableName: author
  columns:
    ...
  relations:
    Book:
      class: Book
      refClass: LinkingAuthorBook #This will allow you to reference Book rows from an Author object
      local: author_id #Local value refers to the current object, in this case Author
      foreign: book_id #Foreign value refers to the object you wish to link to from Author, in this case Book
Book:
  tableName: book
  columns:
    ...
  relations:
    Author:
      class: Author
      refClass: LinkingAuthorBook #This will allow you to reference Author rows from a Book object
      local: book_id #Local value refers to the current object, in this case Author
      foreign: author_id #Foreign value refers to the object you wish to link to from Book, in this case Author

Note the changes made to the after schema.yml file. We added ‘class’ and most importantly, ‘refClass’. You can read more information about refClass @ doctrine’s website.

Now rebuild the model:

php symfony doctrine:build-model

The auto-generated classes created after rebuilding the model will be aware of the M:M relationship you have established in your schema which results in saving these with minimal effort.

As part of the rebuild “stack”, you must also rebuild your filters and forms, and clear your cache. So be sure to do the following as well:

php symfony doctrine:build-filters
php symfony doctrine:build-forms
php symfony cc

Now lets see saving a M:M relationship in action, lets add a select double list to your Author form (assuming you have sfFormExtraPlugin installed), just add the following to /lib/form/doctrine/AuthorForm.class.php:

$this->widgetSchema['book_list']->setOption('renderer_class', 'sfWidgetFormSelectDoubleList');

When you render your Author form, you should now have a double list with all the books you have in your book table. Select a few, then click save, you should see that they were saved against the Author you are currently editing. You can remove, add, etc as you see fit.

That’s it! You set up your app to handle the saving of M:M relationships with (hopefully) minimal effort.

—Edit 5.25.2010

Some people are having a problem with this example with respect to the linking table in the given example. As far as I know, the linking table MUST be present in your schema and available via the resulting model class, otherwise doctrine will not know how to store the link relationship between book and author.

Symfony /w Doctrine – Many to Many (M:M) – Primary Keys are Your Friend

4

If you are having problems with embedded forms for linking tables between many to many relationships, try adding a primary key to the linking table. At first I was using a composite primary key on my linking table and trying to figure out how to get it to work properly with form embedding, but was unable to. So I added a primary key, dropped the composite primary key and everything works as expected. This works well for forms you plan on embedding, if you just want to save M:M relationships via admin double list please visit my post on the topic: http://melikedev.com/2009/12/09/symfony-w-doctrine-saving-many-to-many-mm-relationships/

Symfony – Serializing Data for Save

0

There are times when form data must be serialized before being saved in a database. An example would be saving multiple parameters within an array, then serializing the array, which converts the array into one long string.

Working within Symfony the best place to add the serialize() method is as close to the data as possible, so any calling code will be forced to work the same way, serialize upon create/update, unserialize upon retrieve. To do this you will need to overload the setters in the object class file.

Before

class Object extends BaseObject
{
}

After

class Object extends BaseObject
{
    public function setData($data)
    {
        serialize($data);
        parent::setData($data);
    }

    public function getData()
    {
        return unserialize($this->data);
    }
}

As you can see, in the ‘after’ example, we have overloaded the setData() and getData() methods in the BaseObject class with our own custom methods, which serialize/unserialize the data. We call on the BaseObject::setData() b/c there is some extra data handling that is conducted and we want to maintain that behavior.

One last thing, if you submitted data via a form,  you will need to give the serialized data candidate a special validator:

Before

class BaseForm
{
     $this->setValidators(array(
         'formElement' => new sfValidatorString()
     );
}

class Form extends BaseForm
{
}

After

class BaseForm
{
    $this->setValidators(array(
        'formElement' => new sfValidatorString()
    );
}

class Form extends BaseForm
{
    public function configure()
    {
        $this->validatorSchema['formElement'] = new sfValidatorPass();
    }
}

Without making this change, your ‘formElement’ will be typecast as a string and you will see the word ‘Array’ in your database upon viewing saved records.

Symfony w/ Doctrine Package Over-ride in schema.yml

2

So doctrine allows for the ability to flag a table schema in schema.yml as a package using the following notation:

Table:
  tableName:
  package:
  package_custom_path:

For some reason Symfony does not allow this type of behavior via the /config/doctrine/schema.yml file. When you try to package your model files you get a “Cannot use package parameter in symfony Doctrine schema files.” error message. You may be asking “Why do I care if I can package model files?”, read on. By default, all the model files get stuffed in /lib/model/doctrine/…

Table1Object.class.php
Table2Object.class.php
Table3Object.class.php
base
  BaseTable1Object.class.php
  BaseTable2Object.class.php
  BaseTable3Object.class.php
sfDoctrineGuardPlugin
  sfGuardFiles
  ...
  base
    BasesfGuardFiles
    ...

Notice that TableXObject files are kind of free floating at the root of /lib/model/doctrine/ whereas the sfGuard files are tucked neatly within their own directory. Now this may seem like a nit-picky sort of situation, but it does make a big diff when you start adding more tables and more plug-ins. IMO it’s much easier if you can package your files accordingly (inside of /lib/model/doctrine/):

<package_name>
  Table1Object.class.php
  Table2Object.class.php
  Table3Object.class.php
  base
    BaseTable1Object.class.php
    BaseTable2Object.class.php
    BaseTable3Object.class.php
sfDoctrineGuardPlugin
  sfGuardFiles
  ...
  base
    BasesfGuardFiles
    ...

Much easier to see follow the logic behind the grouping of files. If you view the source code for sfDoctrineBuildModelTask::_checkForPackageParameter() which controls this behavior, you will see the comments:

/**
 * Check for package parameter in main schema files.
 * sfDoctrinePlugin uses the package feature of Doctrine
 * for plugins and cannot be used by the user
 *
 * @param string $path
 * @return void
 */

Why not? Why just for plug-ins? so I simply edited the method:

Before

protected function _checkForPackageParameter($path)
 {
 $files = sfFinder::type('file')->name('*.yml')->in($path);
 foreach ($files as $file)
 {
 $array = sfYaml::load($file);
 if (is_array($array) AND !empty($array))

After

protected function _checkForPackageParameter($path)
 {
  return true;
 $files = sfFinder::type('file')->name('*.yml')->in($path);
 foreach ($files as $file)
 {
 $array = sfYaml::load($file);
 if (is_array($array) AND !empty($array))

This will allow you to now edit your schema.yml file to group your files as described above. For example:

Before

Table1:
  tableName:
  columns:
    ...

After

Table1:
  tableName:
  package:
  columns:
    ...

When I rebuilt model, all my files were correctly stored as expected… I’m not sure why Symfony devs would restrict this behavior for doctrine whereas propel allows it, and it’s available to vanilla Doctrine.

Go to Top