Native SQL Query in Doctrine

Examples...

// get Doctrine_Connection object
$con = Doctrine_Manager::getInstance()->connection();
// execute SQL query, receive Doctrine_Connection_Statement
$st = $con->execute("...............");
// fetch query result
$result = $st->fetchAll();

Or with parameter...

$db = Doctrine_Manager::getInstance()->connection();
$query = $db->prepare("INSERT INTO search_term (term, counter) VALUES (:term, '1') ON DUPLICATE KEY UPDATE counter=counter+1;");
$query->execute(array('term' => $search));

Doctrine Schema: float(18,4) ?

Use decimal instead of float in this situation:

    sale_price_eur:
      type: decimal(18)
      scale: 4

Get Field Comments with Doctrine in Symfony

In Action:

$t = Doctrine::getTable('Product'); 
foreach($t->getColumns() as $key => $column) {
   $comments[$key] = $column['comment'];     
}

Symfony Build: Fatal error: Allowed memory size of x bytes exhausted

If you run a symfony command, it is run by php cli, not apache.

So increase the memory_limit to 128M in the /etc/php5/cli/php.ini file. Building will work fine.:)

Create Object with More Doctrine Translation

$country = new Country();

// set a language-independent value
$country->setCountryCode('H');

// set translations
$country->Translation['hu']->name = 'Magyarország';
$country->Translation['en']->name = 'Hungary';
$country->Translation['de']->name = 'Ungarn';

$country->save();

Truncate Table with Doctrine

$doctrine = Doctrine_Manager::getInstance()->getCurrentConnection()->getDbh();
$doctrine->query('TRUNCATE TABLE tableName');
unset($doctrine);

Work with Doctrine Migration in Symfony

To update your schema, classes, database without losing data, try to do this...

1. Change your schema.yml file.
2. Use the symfony doctrine:generate-migrations-diff command to generate the differences between the current classes and the changed schema. These changes are converted by Doctrine into a migrations files, you can find them in the lib/migration directory. In the filename you will see a version number.
3. Type the symfony doctrine:migrate x command to update your database. x is the version number you found in the filename of the last migration file.
4. Type the symfony doctrine:build --all-classes command to generate new classes for symfony.

Symfony will create a migration_version table in the database to store the current migration version number.

Sort By Foreign Key or Custom Column in Symfony Admin Generator

This solution is works with Doctrine ORM.

1. Put this to the backend module's actions.class.php

   protected function addSortQuery($query)
     {
       $rootAlias = $query->getRootAlias();
       if (array(null, null) == ($sort = $this->getSort()))
       {
         return;
       }
       $s = $sort[0];
       $fields = $this->varHolder->get('configuration')->getFieldsDefault();
       if ($fields != null)
       {
         $field = $fields[$s];
         if ($field != null)
         {
           if (isset($field['sortBy']))
           {
             $criterion = $field['sortBy'];
             if ($criterion != null)
             {
               $s = $criterion;
             }
           }
         }
       }
       
       if (isset($field['noRootAlias']) && $field['noRootAlias'] == true)
       {
         $query->addOrderBy($s . ' ' . $sort[1]);
       }
       else
       {
         $query->addOrderBy($rootAlias.'.'.$s . ' ' . $sort[1]);
       }
     }
   

2. Let's see first the foreign key sorting with an example. If you want to show the related object's name instead of its id, you must use the relation name (in this case 'Group'). To enable sorting, add the sortBy parameter to the field.
   

   config:
         ...
         fields:
           name:    { label: Name }
           Group:   { label: Group, sortBy: Group.name }  
         list:
           title:   Users
           display: [id, name, Group]
   

   That's all, the sorting now works, but the column's name is not clickable yet. You have to change the generator's template. As I use the sfAdminThemejRollerPlugin, I will do this with that.
3. Open the \ plugins \ sfAdminThemejRollerPlugin \ data \ generator \ sfDoctrineModule \ jroller \ template \ templates \ _list_th_tabular.php file and change the 4th row from this:

   <?php if ($field->isReal()): ?>

   to this:

   <?php if ($field->isReal() || $field->getConfig('sortBy')): ?>

   Now the foreign key sorting must work.
4. Let's see the custom column sorting with an example. The data you will want to see in the custom column is somewhere in the database. You have to add your custom column to the original sql statement. Use joins, relations or simply subqueries. You can overwrite the sql in backend module's actions.class.php:
   

   protected function buildQuery()
     {
        $query = parent::buildQuery();
        // do what ever you like with the query    
        // use the die($query->getSqlQuery()); row to see the original sql statement
        return $query->addSelect('*, (SELECT foo FROM bar) as foobarcolumn');
     }

   As you can see I didn't change the query output, I only add an extra column to the query.
5. Now we have to add this column to the generator.yml...
   

   config:
         ...
         fields:
           name:    { label: Name }
           Group:   { label: Group, sortBy: Group.name }
           foobar:  { label: FooBar, sortBy: foobarcolumn, noRootAlias: true }
         list:
           title:   Users
           display: [id, name, Group, foobar]
   

I hope this helps. The noRootAlias param is created by me, perhaps you won't need it or you can avoid using it. If you have an easier/better solution, please tell me!

SQLSTATE[HY000]: General error: 1005 Can't create table

Let's see a very simple CORRECT relation and reasons why it can throw SQLSTATE[HY000]: General error...

relations:
    User:
      foreignAlias: Phonenumbers
      local: user_id
      foreign: id
      type: one
      foreignType: many

1. Did you forget to define the user_id field in the Phonenumber table?
2. Did you check the type of the user_id field and the Phonenumber table's id field? They must be exactly the same integer. If one of them is int(4) and the other is int(8), the building will fail.

sfDoctrinePager: Fatal error: Call to undefined method Doctrine_Collection::offset()

If you set a sfDoctrinePager object and give an EXECUTED query to the $this->pager->setQuery() method, you get a fatal error. Don't forget, sfDoctrinePager will execute the query, not you. Remove the ->execute() command from the end of the query.

Fatal error: Call to undefined method Doctrine_Collection::offset() in C:\webserv\www\phpcrm\lib\vendor\symfony\lib\plugins\sfDoctrinePlugin\lib\pager\sfDoctrinePager.class.php on line 84

Create MySQL Dump to Save Data Before doctrine:build

If you use the symfony doctrine:build --all command, all data will be deleted in the database. To fix this, you have to save the data before the building and then import the dump file...

1. mysqldump -t -c -u root -p DBNAME > dump.sql
2. symfony doctrine:build --all
3. mysql -u root -p DBNAME < dump.sql

Note: On Windows, you have to add the path (the mysql/bin directory) to the environmental variables.
Note2: The -t parameter removes the drop table and create table queries from the dump. It is needed if you change the structure of a table, otherwise drop table query will delete what symfony doctrine:build created.
Note3: The -c parameter adds the column names to INSERT INTO statements.