|
|
Snippets |
|
The default sfDoctrinePager is very very slow when we have large tables. It seems to be something about count totals or so. Here I show a custom Pager I made that works faster and does exactly the same, with a slighty different way to call it.
Here is how you must call it:
$q = Doctrine_Query::create()->select('field1, field2')->from('yourTable'); $pager = new customDoctrinePager($q, $page, $rows_per_page); $pager->init(); $results = $pager->getResults();
And here you have the class code:
<?php class customDoctrinePager extends sfPager { private $resultSet = null; public function __construct($query, $page = 1, $maxPerPage = 25) { $this->setPage($page); $this->setMaxPerPage($maxPerPage); $this->setNbResults($query->count()); $this->setLastPage(ceil($this->getNbResults() / $this->getMaxPerPage())); $startIndex = (($this->getPage()) - 1) * $maxPerPage; $query->limit($maxPerPage); $query->offset($startIndex); $this->resultSet = $query->execute(); } public function init() {} public function getResults() { return $this->resultSet; } protected function retrieveObject($offset) {} }
This is just a version of the nonHydratingPager for Propel, adapted for Doctrine.
<?php /* Designed to be compatible with sfDoctrinePager only accept raw sql queries instead * of a Doctrine_Query object and spit back a resultset which you most likely want to * fill an array with instead of the standard array of hydrated objects. * * This solution is based on Propel's by Noel Tarnoff, Oz Basarir, dev AT (NOSPAM) naturalcapitalDOTorg * * @author David Morales, davidmoralesmojica AT (NOSPAM) gmailDOTcom * * Typical usage scenario: * 1) build 2 query strings with common WHERE clause, one for count one for selecting the rows * 2) pass the queries in with page and maxPerPage into the constructor ( no need to ->init() ) * 3) iterate through your result set (array of values) * 4) sit back and watch the fun * * ex. * $results = new nonHydratingPager($query_select, $query_count, $page, $max); * * foreach( $results as $result ) * { * ... * } */ class nonHydratingPager extends sfPager { private $resultSet = null; public function __construct($query, $query_count, $page = 1, $maxPerPage = 25) { $this->setPage($page); $this->setMaxPerPage($maxPerPage); $rs = Doctrine_Manager::getInstance()->getCurrentConnection()->fetchAssoc($query_count); $this->setNbResults($rs[0]['count']); $this->setLastPage(ceil($this->getNbResults() / $this->getMaxPerPage())); $startIndex = (($this->getPage()) - 1) * $maxPerPage; $query .= ' LIMIT ' . $maxPerPage . ' OFFSET ' . $startIndex; $this->resultSet = Doctrine_Manager::getInstance()->getCurrentConnection()->fetchAssoc($query); } public function init() {} public function getResults() { return $this->resultSet; } protected function retrieveObject($offset) {} }
When working with the admin generator in Symfony 1.2, I found I could no longer use the doctrine_admin_double_list field type. After hours of searching, I finally figured out how to get this functionality back. It can no longer be done from the generator.yml file, instead you need to modify your table's form class, e.g. lib/form/doctrine/XXXForm.class.php:
class XXXForm extends BaseXXXForm { public function configure() { $this->widgetSchema['field_name'] = new sfWidgetFormDoctrineChoice(array( 'model' => 'ModelName', 'add_empty' => false, 'renderer_class' => 'sfWidgetFormSelectDoubleList', )); } }
This requires the sfFormExtraPlugin plugin to be installed.
I assume this will work equally well with Propel. See symfony Forms in Action for more info.
This snippet is not a tutorial on symfony events, but you can find some documentation in the book and or in this good tutorial.
The event dispatcher is a very powerfull tool, but you might meet the same issue than me if you want to use it from a Model Class.
Let's take the classic Author/Article project sample.
When an article is added, it might be interesting to trigger an "article.new" event. This event coud be listened by different actors.
Like for instance :
The most obvious place to trigger the event is the Article save() method. Here is the code for Doctrine (the logic is exactly the same for Propel):
Article.class.php
public function save(Doctrine_Connection $conn = null) { if ($this->isNew()) { // Let's trigger the event sfContext::getInstance()->getEventDispatcher()->notify(new sfEvent($this, 'article.new')); } return parent::save($conn); }
Unfortunatly, this code won't work. If you try the "symfony doctrine:build-all-reload" command, here is the error that will be displayed :
Cli
sfException: The "default" context does not exist.
Why ? Because symfony tasks don't initialise the sfContext class. And for the moment (symfony version 1.2.4) I don't think there's a clean way to access to the dispatcher object from a Doctrine Class.
Here is my solution : let's build a (very) little singleton class that will provide the dispatcher ressource everywhere in the project, and even when sfContext is not avaible
yourProjectRoot/lib/Fedex.class.php
class Fedex { static protected $instance; protected $dispatcher; /** * The singleton logic http://en.wikipedia.org/wiki/Singleton_pattern#PHP_5 */ static public function getInstance() { if (!self::$instance instanceof self) { self::$instance = new self; } return self::$instance; } public function setEventDispatcher(sfEventDispatcher $dispatcher) { $this->dispatcher = $dispatcher; } public function getEventDispatcher() { return $this->dispatcher; } }
Of course, you first have to give to the Fedex class a link the delivery ressource. Let's do it with the very first class called by the script of the project :
yourProjectRoot/config/ProjectConfiguration.class.php
public function setup() { // add after all your setup stuffs require_once(dirname(__FILE__).'/../lib/Fedex.class.php'); Fedex::getInstance()->setEventDispatcher($this->dispatcher); }
That's it, you're done. The Fedex class will deliver the dispatcher ressource everywhere in your project through Fedex::getInstance()->getEventDispatcher() And it works even when the sfContext class is not avaible
Article.class.php
public function save(Doctrine_Connection $conn = null) { if ($this->isNew()) { Fedex::getInstance()->getEventDispatcher()->notify(new sfEvent($this, 'article.new')); } return parent::save($conn); }
The important thing is that this little class doesn't modify any part of the symfony event system. It just provide you an alternative way to access to it.
I wanted to add created_by and updated_by columns to my Doctrine schema. But the existing templates doesn't support that, so i've rewritten the Timestampable template.
Now Doctrine can automatically update these fields to the specific username oder userid.
ExampleUser:
tableName: example_user
columns:
id:
primary: true
autoincrement: true
type: integer(4)
name:
type: string(255)
password:
type: string(255)
actAs:
Userid:
created:
name: created_by
type: integer
updated:
name: updated_by
type: string
If you are writing integer as type the template will call $sf_user->getId(), if you write string it will call $sf_user->getUsername().
So the above example will write the ID to created_by column and the username to updated_by column.
<?php /* * $Id$ * * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR * A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT * OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, * DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY * THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE * OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. * * This software consists of voluntary contributions made by many individuals * and is licensed under the LGPL. For more information, see * <http://www.phpdoctrine.org>. */ /** * Doctrine_Template_Userid * * Easily add created and updated by ids or usernames to your doctrine records * * @package Doctrine * @subpackage Template * @license http://www.opensource.org/licenses/lgpl-license.php LGPL * @link www.phpdoctrine.org * @since 1.0 * @version $Revision$ * @author Thomas Boerger <tb@mosez.net> */ class Doctrine_Template_Userid extends Doctrine_Template { /** * Array of userid options * * @var string */ protected $_options = array( 'created' => array( 'name' => 'created_by', 'type' => 'integer', 'disabled' => false, 'expression' => false, 'options' => array() ), 'updated' => array( 'name' => 'updated_by', 'type' => 'integer', 'disabled' => false, 'expression' => false, 'onInsert' => true, 'options' => array() ) ); /** * __construct * * @param string $array. * @return void */ public function __construct(array $options) { $this->_options = Doctrine_Lib::arrayDeepMerge($this->_options, $options); } /** * setTableDefinition * * @return void */ public function setTableDefinition() { if(!$this->_options['created']['disabled']) { $this->hasColumn( $this->_options['created']['name'], $this->_options['created']['type'], null, $this->_options['created']['options'] ); } if(!$this->_options['updated']['disabled']) { $this->hasColumn( $this->_options['updated']['name'], $this->_options['updated']['type'], null, $this->_options['updated']['options'] ); } $this->addListener(new Doctrine_Template_Listener_Userid($this->_options)); } }
<?php /* * $Id$ * * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR * A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT * OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, * DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY * THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE * OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. * * This software consists of voluntary contributions made by many individuals * and is licensed under the LGPL. For more information, see * <http://www.phpdoctrine.org>. */ /** * Doctrine_Template_Listener_Userid * * @package Doctrine * @subpackage Template * @license http://www.opensource.org/licenses/lgpl-license.php LGPL * @link www.phpdoctrine.org * @since 1.0 * @version $Revision$ * @author Thomas Boerger <tb@mosez.net> */ class Doctrine_Template_Listener_Userid extends Doctrine_Record_Listener { /** * Array of userid options * * @var string */ protected $_options = array(); /** * __construct * * @param string $options. * @return void */ public function __construct(array $options) { $this->_options = $options; } /** * preInsert * * @param object $Doctrine_Event. * @return void */ public function preInsert(Doctrine_Event $event) { if(!$this->_options['created']['disabled']) { $createdName = $this->_options['created']['name']; $event->getInvoker()->$createdName = $this->getUserId('created'); } if(!$this->_options['updated']['disabled'] && $this->_options['updated']['onInsert']) { $updatedName = $this->_options['updated']['name']; $event->getInvoker()->$updatedName = $this->getUserId('updated'); } } /** * preUpdate * * @param object $Doctrine_Event. * @return void */ public function preUpdate(Doctrine_Event $event) { if( ! $this->_options['updated']['disabled']) { $updatedName = $this->_options['updated']['name']; $event->getInvoker()->$updatedName = $this->getUserId('updated'); } } /** * getUserId * * Gets the userid or username depending on field format * * @param string $type. * @return void */ public function getUserId($type) { $options = $this->_options[$type]; if ($options['expression'] !== false && is_string($options['expression'])) { return new Doctrine_Expression($options['expression']); } elseif (!class_exists("pakeApp")) { switch($options['type']) { case 'integer': if (class_exists('sfGuardUser')) { return sfContext::getInstance()->getUser()->getAttribute('user_id', null, 'sfGuardSecurityUser'); } else { return sfContext::getInstance()->getUser()->getId(); } break; case 'string': return sfContext::getInstance()->getUser()->getUsername(); break; default: return 'n/a'; break; } } } }
Sometimes a table needs to have non-autoincremented primary keys (for example, to be able to import data from other sources keeping the same id's for objects).
I use the following procedure:
DBDesigner -> Propel schema (config/schema.xml) through DBDesigner2Propel
Generate Doctrine schema (config/doctrine/schema.yml) through symfony doctrine-import.
The problem is that the resulting schema.yml no longer includes information about the primary keys, so doctrine-insert-sql and doctrine-build-model will create an id field as autoincremented primary key.
The solution is a small patch which doesn't change the default behaviour. Look at sfDoctrineSchemaPropelLoader.class.php around the line 52 and modify a few lines in the code to be as the following:
// columns foreach ($propelDatabaseSchema->getChildren($tableDesc) as $col_name => $columnDescription) { $docCol = new sfDoctrineColumnSchema($col_name, $columnDescription, true); if($col_name == 'id') { if($docCol->getColumnInfo()->get('autoIncrement') === true) { // We skip integer auto-increment primary keys, but we keep the rest continue; } } $class->addColumn($docCol); }
There is a bug in sfDoctrineColumnSchema.class.php which should be fixed too. The problem is that it is intersecting with the non-translated constraints instead the ones already translated from propel.
The fix is easy:
Original line:
if ($constraints = array_intersect_key($columnDescription, array_flip(self::$allowedConstraints)))
Change to this:
// Change to this: if ($constraints = array_intersect_key($this->columnInfo->getAll(), array_flip(self::$allowedConstraints)))
It's very annoying that the pake tasks @doctrine-build-all@ and @doctrine-build-all-load@ don't work. So I've thought, why don't write a shell script which executes the single steps for me.
Just create a new file in your batch directory. For example 'build' or 'doctrine'
#!/usr/bin/env bash
symfony doctrine-drop-db <your app>
symfony doctrine-build-db <your app>
symfony doctrine-build-model <your app>
symfony doctrine-build-sql <your app>
symfony doctrine-insert-sql <your app>
php batch/<your load-data-batch-file>.php
// This is meant to be in actions.class.php as one of your actions. // Note how it redirects to your view action after saving.
public function executeSave() { $req = $this->getRequest();
$q = new Doctrine_Query(); $user = $q->from('UserInfo')->where('id = ?',$this->getRequestParameter('id'))->execute()->getFirst(); $user->merge($req->getParameterHolder()->getAll()); $user->save(); $this->redirect('user/view?did=save&id='.$user->id);
}
This is a model function with a field named avatar_id, that links to the avatar table, id field, and allows you to set the id based on the name of the avatar.
static function getAvatarByName($name) { $query = new Doctrine_Query(); $query->select('a.id'); $query->from('Avatar a'); $query->where('name = ?', $name); $avatar = $query->execute(); return $avatar[0]; } public function setAvatarIdByName($name) { $avatar = AvatarTable::getAvatarByName($name); $this->setAvatarId($avatar->getId()); }
$query = new Doctrine_Query(); $query->select('c.*, COUNT(u.id) num_users'); // soon it may be possible to do COUNT(u.id) as num_users like in sql $query->from('Church c'); $query->leftjoin('c.User u'); $query->orderby('c.name asc'); $query->groupby('c.id'); $churches = $query->execute(); foreach($churches AS $church) { echo $church->name.' - '.$church->Users[0]->num_users.'<br/>'; }
That last part is tricky, no real way to know that from any of the documentation that num_users is in $church->users[0]->num_users.
$user = new User(); // new record $user = Doctrine_Manager::getInstance()->getTable('User')->find(1); // existing record $user['username'] = 'username'; $user->username = 'username'; $user->setUsername('username'); $user->set('username', 'username'); echo $user['username']; // username echo $user->username; // username echo $user->getUsername(); // username echo $user->get('username'); // username
Example Table static methods for retrieving data. Passing query objects to allow you to modify and reuse existing methods.
static function retrieveUsers($query = null) { if( $query === null ) { $query = new Doctrine_Query(); } $query->select('u.*'); $query->from('User u'); $users = $query->execute(); return $users; } static function retrieveUsersWithProfile($query) { if( $query === null ) { $query = new Doctrine_Query(); } $query->select('p.*') $query->innerJoin('u.UserProfile p'); return UserTable::retrieveUsers($query); } static function retrieveUserByUsername($username, $query = null) { if( $query === null ) { $query = new Doctrine_Query(); } $query->where('u.username = ?', $username); $users = UserTable::retrieveUsers($query); return $users[0]; }
$dateFormat = new sfDateFormat(); $value = $dateFormat->format(time(), 'I'); $user->set('last_login',$value);
