[DC-674] NULL Dates are translated to '0000-00-00' after upgrading to 1.2.2 Created: 10/May/10  Updated: 06/Oct/10

Status: Open
Project: Doctrine 1
Component/s: Behaviors
Affects Version/s: 1.2.1, 1.2.2
Fix Version/s: 1.2.1, 1.2.2

Type: Bug Priority: Critical
Reporter: Ville Itämaa Assignee: Jonathan H. Wage
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Zend Framework, Ubuntu 9.10, MySQL



 Description   

Once the upgrade was done from Doctrine 1.2.1 to 1.2.2 we discovered that date related issues started to appear.
With dates that are persisted in DB as NULL are translated to "0000-00-00" when retrieved from DB. This has occurred in multiple places and is quite worrying as there is a lot of dates in our project. This means that everywhere in our codebase where we check a datevalue in our Models is NULL we need also to check for the string literal "0000-00-00".



 Comments   
Comment by Jonathan H. Wage [ 10/May/10 ]

Are you able to reproduce this in a test case?

Comment by Ville Itämaa [ 11/May/10 ]

We reverted to Doctrine 1.2.1 after realising the bug to confirm it was Doctrine 1.2.2 that was the cause for the problem. And as a result the records with NULL dates in the DB became NULL in the Models.
But when using Doctrine 1.2.2, the NULL dates became '0000-00-00' in the Models.
I don't have any other way to reproduce this error.

Comment by Jonathan H. Wage [ 11/May/10 ]

Were you able to identity which changeset it was? You can read about creating test cases here http://www.doctrine-project.org/documentation/manual/1_2/en/unit-testing

So far I am not able to reproduce the error you described.

Comment by Jonathan H. Wage [ 08/Jun/10 ]

I'd like to fix this. Did you ever figure out which changeset introduced the issue? I've been trying to figure it out myself.

Comment by Roland Huszti [ 06/Oct/10 ]

With 1.2.3 this works for me fine with both TIMESTAMP and DATE fields.

YAML

        date_of_birth:
            type: date

BASE MODEL

        $this->hasColumn('date_of_birth', 'date', null, array(
             'type' => 'date',

             // try these two
             // 'notnull' => false,
             // 'default' => null
         ));
YAML

        exported_at:
            type: timestamp(25)
            notnull: false
            default: null

            # in this model I have everything to make sure it accepts and defaults to NULL

BASE MODEL

        $this->hasColumn('exported_at', 'timestamp', 25, array(
             'type' => 'timestamp',
             'notnull' => false,
             'length' => '25',
             ));

You may try adding these to your YAML and (base) models

YAML

    fieldname:
         . . .
        notnull: false
        default: null

BASE MODEL

        $this->hasColumn('fieldname', . . .
             . . .
             'notnull' => false, // <<<<<<<
             // 'default' => null, // <<< maybe, probably not needed
             ));

I hope it helps.





[DC-799] Doctrine_Query::parseFunctionExpression() produces unexpected results if the expression contains a function Created: 27/Jul/10  Updated: 27/Jul/10

Status: Open
Project: Doctrine 1
Component/s: Query
Affects Version/s: 1.2.2
Fix Version/s: 1.2.2

Type: Bug Priority: Major
Reporter: Henning Glatter-Gotz Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Mac OS X, LINUX



 Description   

When Doctrine_Query::parseFunctionExpression() is called with the following parameter and $this->_conn->expression is of type Doctrine_Expression_MySql:

$expr = "DATE_FORMAT(datefield,'%Y-%m-%d'),someotherfield"

The expected result from Doctrine_Query::parseFunctionExpressoin() would be

DATE_FORMAT(l.datefield,'%Y-%m-%d'),l.someotherfield

However, the actual result is

DATE_FORMAT(l.datefield,'%Y-%m-%d'),someotherfiel

Note the missing leading "l." and the last character ("d") on "someotherfield".
Obviously this leads to fatal errors.

This example is a result of calling addGroupby("DATE_FORMAT(datefield,'%Y-%m-%d'),someotherfield") on a Doctrine_Query object.






[DC-795] Can't mix Soft and Hard deletes. Fix with patch provided. Created: 21/Jul/10  Updated: 21/Jul/10

Status: Open
Project: Doctrine 1
Component/s: Behaviors
Affects Version/s: 1.2.2
Fix Version/s: 1.2.2

Type: Bug Priority: Major
Reporter: John Wards Assignee: Jonathan H. Wage
Resolution: Unresolved Votes: 1
Labels: None
Environment:

Mac



 Description   

I reported this bug on the symfony site, but after investigation i have found that it is actually a doctrine SoftDelete issue. http://trac.symfony-project.org/ticket/8898

I have a sandbox replicating the problem here:
http://dl.dropbox.com/u/8354765/sf_sandbox.zip

Run the test:
./symfony test:unit Contact

The issue is I want to hard delete my M-M link table and soft delete the parents. This fails. I believe it is to do with the fact that the relations to the parent are marked as Doctrine_Record::STATE_TCLEAN which when the SoftDelete calls save on the parent object this flag trys to reinsert the relations that it has deleted. Things go very wrong at this point and the connection is rolled back.

The fix I have is the following, not sure what this would do to other things or not as I am not overly familiar with Doctrine internals...

Index: Doctrine/Template/Listener/SoftDelete.php
===================================================================
--- Doctrine/Template/Listener/SoftDelete.php	(revision 12962)
+++ Doctrine/Template/Listener/SoftDelete.php	(working copy)
@@ -95,6 +95,7 @@
     public function postDelete(Doctrine_Event $event)
     {
         if ( ! $this->_options['hardDelete']) {
+            $event->getInvoker()->clearRelated();
             $event->getInvoker()->save();
         }
     }

This works even if the relations have been marked as SoftDelete.



 Comments   
Comment by John Wards [ 21/Jul/10 ]

Formatting

Comment by Jonathan H. Wage [ 21/Jul/10 ]

Does this patch pass the test suite?

Comment by John Wards [ 21/Jul/10 ]

I have run the test suite and got the same 8 failures with and without the patch.

These are the failing tests:
Doctrine_Cache_Apc_TestCase
Doctrine_Cache_Abstract_TestCase
Doctrine_Ticket_1783_TestCase

Anything obvious I need to do to get these working, other than enabling apc...

Comment by John Wards [ 21/Jul/10 ]

It seems to be passing all the tests that have SoftDelete in them however, so I would say that it is working as expected.





[DC-769] Variable type different for return value from Doctrine_Record->toArray() depending on whether the object is from a select, or a save. Created: 27/Jun/10  Updated: 17/Apr/14

Status: Open
Project: Doctrine 1
Component/s: None
Affects Version/s: 1.2.1
Fix Version/s: 1.2.1, 1.2.2

Type: Bug Priority: Major
Reporter: Dennis Gearon Assignee: Jonathan H. Wage
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Ubuntu9.10, PHP 5.2.6, Symfony 1.4.1, Postgres8.4



 Description   

With a object that is created via a save(), and the record's primary key is a INT fed by a SEQUENCE, the type of the variable is an INT.

With an object that is hydrated from the database via a SELECT, the record's primary key INT will come back in 'toArray()' as a STRING.

That means that checking for type has to know what context it came from, user, INSERT, or SELECT. Not fun.

This also screws up converting arrays to JSON, 'cause the STRINGS get quotation marks and the INTS do not.

As a general rule, everything FROM the database seems to be strings. Yes, I know, everything 'on the wire' or 'through a socket' comes out as text. And it's a lot faster to leave it that way.

But having the type be different depending on the database operation? Not sure I like that.



 Comments   
Comment by Jonathan H. Wage [ 24/Aug/10 ]

Can you provide a test case so that we can see if we can come up with a patch?





[DC-753] doctrine generate-migrations-diff throws "No php or yml files found at path" Created: 19/Jun/10  Updated: 27/Jan/11

Status: Open
Project: Doctrine 1
Component/s: Migrations
Affects Version/s: 1.2.2
Fix Version/s: 1.2.2

Type: Bug Priority: Major
Reporter: David Vega Assignee: Jonathan H. Wage
Resolution: Unresolved Votes: 0
Labels: None
Environment:

WIndows 7 x64, WAMP, PHP 5.2.11 & 5.3



 Description   

I am trying to generate migrations from the CLI but I get the error mentioned in the title. I did some searching and this seems to have been a known problem and was fixed, however, I'm getting it and the path is correct, and there is even a schema.yml file I just generated, also through CLI.

Here is the error:

D:\wamp\www\project\src\administrator\components\com_project\doctrine>php doctrine generate-migrations-diff
No php or yml files found at path: "D:\wamp\www\project\src\administrator\components\com_project\doctrine\schema"

And here is what I have in my CLI file:

require_once('../../../../libraries/doctrine/lib/Doctrine.php');

spl_autoload_register(array('Doctrine','autoload'));

Doctrine_Manager::connection('mysql://root@localhost/project','default');

//Doctrine_Manager::getInstance()->setAttribute(Doctrine::ATTR_TBLNAME_FORMAT, 'jos_project_%s');
Doctrine_Manager::getInstance()->setAttribute(Doctrine::ATTR_VALIDATE, Doctrine::VALIDATE_ALL);
Doctrine_Manager::getInstance()->setAttribute(Doctrine::ATTR_QUOTE_IDENTIFIER, true);
Doctrine::loadModels('models/generated');
Doctrine::loadModels('models');

$cli = new Doctrine_Cli(array(
'data_fixtures_path' => dirname(_FILE_).DIRECTORY_SEPARATOR.'fixtures',
'models_path' => dirname(_FILE_).DIRECTORY_SEPARATOR.'models',
'migrations_path' => dirname(_FILE_).DIRECTORY_SEPARATOR.'migrations',
'sql_path' => dirname(_FILE_).DIRECTORY_SEPARATOR.'sql',
'yaml_schema_path' => dirname(_FILE_).DIRECTORY_SEPARATOR.'schema'
));

$cli->run($_SERVER['argv']);

I find this very weird because earlier today I was able to make a migration the same way but with Symfony's CLI, however, that is another project.

Regards,

David



 Comments   
Comment by Marcelo Saldanha [ 27/Jan/11 ]

I have the same problem, using symfony 1.4 latest sources. After reading about 20 (long) pages about similar issues, I've come up with a solution.

The problem appears when the project still don't have any Models defined. Im my case, they were all new projects in the plugin activation stage. Curiously, the behaviour were random, as in some projects I could activate my Contacts plugin (the first), and in others I couldn't.

After much consideration, the problem was that the var $extension in /doctrine/Doctrine/Migration/Diff.php was empty, and that was because the algorithm only considered the first entry of the directory. If it was a file, its extension was used. If not, the algorithm descended until it found a file as the first entry. BUT it never considered second (and following) entries, so as my first entry was the 'base' directory, and it was empty, no extension was ever found. This probably will not happen if the project have some models defined.

And so, I came up with a solution. In doctrine/Doctrine/Migration/Diff.php, enclose this code in the _getItemExtension method (near line 350, in my copy) with a loop, such as:
$idx = 0;
while (strlen($extension) == 0) {
if (isset($files[$idx])) {
if (is_dir($files[$idx]))

{ $extension = $this->_getItemExtension($files[$idx]); }

else

{ $pathInfo = pathinfo($files[$idx]); $extension = $pathInfo['extension']; }

$idx++;
}
else break; // no more entries to seek
}

Now it keep looking in every folder until it finds a file WITH an extension, and stops when all the tree is searched.

Hope that helps someone.

best regards.





[DC-740] issue with multiple connection handling Created: 16/Jun/10  Updated: 16/Nov/10

Status: Open
Project: Doctrine 1
Component/s: Connection
Affects Version/s: 1.2.2
Fix Version/s: 1.2.2

Type: Bug Priority: Major
Reporter: Ian P. Christian Assignee: Jonathan H. Wage
Resolution: Unresolved Votes: 3
Labels: None

Attachments: Text File doctrine_core.patch     Text File doctrine_manager.patch    
Sub-Tasks:
Key
Summary
Type
Status
Assignee
DC-618 [PATCH] Local key relations without m... Sub-task Open Jonathan H. Wage  

 Description   

I've found an issue where doctrine will use the wrong connection for tables under certain conditions.

In a template, I'm doing a $sf_user->hasCredential() - which is causing this to be run in sfGuardSecurityUser,

$this->user = Doctrine::getTable('sfGuardUser')->find($id);

When this execute, the calls find themselves to Doctrine_Manager::getConnectionForComponent($componentName)

This method calls Doctrine_Core::modelsAutoload($componentName);, which fails to load the class, and returns false (no checking is done to see if it should return true).

As this fails to include the sfGuardUser classes wher the component binding goes on, the getTAble call will use the default connection, then create the table fails to use the correct connection

Doctrine_Core::getTable()

return Doctrine_Manager::getInstance()->getConnectionForComponent($componentName)->getTable($componentName);

the binding is done after the call to getConectionForComponent, as it's getTable that will ultimately cause the autoloader to pull in the table classes.



 Comments   
Comment by Jonathan H. Wage [ 16/Jun/10 ]

Hmm, why is the autoloading of sfGuardUser failing? I don't understand that part. If it is failing do you get a cannot load class error?

Comment by Ian P. Christian [ 16/Jun/10 ]

The reason no autoload error is throw, is because symfony's autoloader loads the class for you, but it does it at the getTable() call, which as seen below from Doctrine_Core::getTable(), it's proxied though the connection - which is created before the gable is instanced, which of when the file is actaully loaded.

return Doctrine_Manager::getInstance()->getConnectionForComponent($componentName)->getTable($componentName);
Comment by Ian P. Christian [ 16/Jun/10 ]

Just to expand on this...

This obviously gets called when a call to getTable is made:

    public function getConnectionForComponent($componentName)
    {
        Doctrine_Core::modelsAutoload($componentName);

        if (isset($this->_bound[$componentName])) {
            return $this->getConnection($this->_bound[$componentName]);
        }

        return $this->getCurrentConnection();
    }

The autoload fails, as you can see from the code...

    public static function modelsAutoload($className)
    {
        if (class_exists($className, false) || interface_exists($className, false)) {
            return false;
        }

        if ( ! self::$_modelsDirectory) {
            $loadedModels = self::$_loadedModelFiles;

            if (isset($loadedModels[$className]) && file_exists($loadedModels[$className])) {
                require $loadedModels[$className];

                return true;
            }
        } else {
            $class = self::$_modelsDirectory . DIRECTORY_SEPARATOR . str_replace('_', DIRECTORY_SEPARATOR, $className) . '.php';

            if (file_exists($class)) {
                require $class;

                return true;
            }
        }
        return false;
    }

$_modelsDirectory is never set, and $_loadedModelFiles is an empty array. The $_modelsDirectory, even if set, wouldn't handle loading for plugins, which put their models in places like lib/model/doctrine/sfDoctrineGuardPlugin/sfGuardUser.class.php.

Comment by Ian P. Christian [ 16/Jun/10 ]

This was not a problem before r7668 (at least, not for most use cases)....

It used to be that null was passed as the first arg in the D_Query::create() method call, causing the query to figure out itself which connection to use, which was done after the component was bound, so that's fine!

However, the code below is how it is in the current head

    public function createQuery($alias = '')
    {
        if ( ! empty($alias)) {
            $alias = ' ' . trim($alias);
        }

        $class = $this->getAttribute(Doctrine_Core::ATTR_QUERY_CLASS);

        return Doctrine_Query::create($this->_conn, $class)
            ->from($this->getComponentName() . $alias);
    }

Here , the connection of the table (as explained above is previously set incorerctly) is passed to the query.

Comment by Ian P. Christian [ 16/Jun/10 ]

I've found a work around to this, not sure if it's a desirable fix though...

In the project configuration class, I've added this to the setup()


    $this->dispatcher->connect('doctrine.configure', array($this, 'doctrineBinder'));

and the following method is also added, were I'm manually doing my bindings...

  public function doctrineBinder(sfEvent $event)
  {
    $manager = Doctrine_Manager::getInstance();
    $manager->bindComponent('sfGuardUser', 'nosp');
    $manager->bindComponent('Incident', 'nosp');
    $manager->bindComponent('ServiceIp', 'ip');

  }

The overhead here isn't really that high (it just sets an element in the array) - I'd also wonder if a bindComponents($array); should be added to simplify this call, but that's another method.

Comment by Marcel Berteler [ 06/Jul/10 ]

After a long and hard look at the sfDoctrinePlugin and Doctrine code I can to the same conclusion. The Doctrine autoload is not working in sfDoctrinePlugin. I think this is more a sfDoctrinePlugin bug than a Doctrine bug.

Instead of manual binding, a better way is the actually make sure the intended flow of the code is working like it should be.

To be able to use sfDoctrineGuard with multiple connections you need to ensure that the connection name is added to the Schema of sfDoctrineGuard. Once this is done, rebuilding the model will put a bindComponent in the class files.

This works fine if the autoload is working like it should.

To get the autoload to work, you can extend the autoload function of Doctrine_Core in Doctrine:

The Doctrine.php file is empty by default, so its easy to add your code to it (until the problem is fixed without having to edit Doctrine code)

lib/plugins/sfDoctrinePlugin/lib/vendor/doctrine/Doctrine.php:

 
class Doctrine extends Doctrine_Core
{
  public static function modelsAutoload($className)
  {
    sfAutoload::getInstance()->autoload($className);

    parent::modelsAutoload($className);
  }
  
}
Comment by Marcel Berteler [ 06/Jul/10 ]

Sorry... the above does not work.

Doctrine_Core->autoload() is called and not Doctrine->autoload().

sfAutoload::getInstance()->autoload($className);

can be added to Doctrine_Core line 1133

public static function modelsAutoload($className)
    {
        if (class_exists($className, false) || interface_exists($className, false)) {
            return false;
        }

        sfAutoload::getInstance()->autoload($className);
Comment by Marcel Berteler [ 06/Jul/10 ]

Oh, and to make sfDoctrineGuard work properly, you might have to ensure the sfBasicSecurityUser is bound to the correct model.

You can do this in plugins\sfDoctrineGuardPlugin\lib\user\sfGuardSecurityUser.class.php or in apps\xxxxx\lib\myUser.class.php

 
Doctrine_Manager::getInstance()->bindComponent('sfGuardUser', 'connectionName');
Comment by Marcel Berteler [ 07/Jul/10 ]

The patch to Doctrine_Core

This is a hack that only works when used in sfDoctrinePlugin / Symfony

Not intended as the final patch to fix this bug but as a work around to make multiple connections usable.

Comment by Ian P. Christian [ 08/Jul/10 ]

This effects migrations too it seems:

Even doing this:


    $manager = Doctrine_Manager::getInstance();                                                          
    $manager->bindComponent('ChangeRequest', 'nosp');                                                    
    $manager->bindComponent('change_request', 'nosp');                                                   
class Addstatetochangerequest extends Doctrine_Migration_Base                                            
{                                                                                                        
  public function up()                                                                                   
  {                                                                                                      
    $this->addColumn('change_request', 'change_state', 'enum', array('values' => array('draft', 'submitted', 'approved', 'rejected', 'closed')));

This results in:


# ./symfony doctrine:migrate
>> doctrine  Migrating from version 0 to 1
                                                                                                                                                                                    
  The following errors occurred:                                                                                                                                                    
                                                                                                                                                                                    
   - SQLSTATE[42S02]: Base table or view not found: 1146 Table 'nosp_test_radius2.change_request' doesn't exist. Failing Query: "ALTER TABLE change_request ADD change_state TEXT"  

The database attempted to be used there is not the correct one.

Comment by Marcel Berteler [ 27/Sep/10 ]

second required patch to make Symfony work with 2 concurrent databases

Comment by Eugeniy Belyaev [ 21/Oct/10 ]
Another bad way to get it working in symfony:


ProjectConfiguration.class.php
  public function configureDoctrine(Doctrine_Manager $manager)
  {
    $files = sfFinder::type('file')
      ->maxdepth(0)
      ->not_name('*Table.class.php')
      ->name('*.class.php')
      ->in(sfConfig::get('sf_lib_dir') . '/model/doctrine');

    foreach ($files as $file) {
      $class_name = str_replace('.class.php', '', basename($file));
      Doctrine_Core::loadModel($class_name, $file);
    }
  }
Comment by Dean de Bree [ 16/Nov/10 ]

I found that if I changed the getTable function inside the Core.php file it seemed to work. Basically it forces the autoloader to load the object file, and when it does this it runs the bound connection statement to bind a table to a connection.

Core.php
/**
     * Get the Doctrine_Table object for the passed model
     *
     * @param string $componentName
     * @return Doctrine_Table
     */
    public static function getTable($componentName)
    {
        if (!class_exists($componentName)) {
            new $componentName();
        }

        return Doctrine_Manager::getInstance()->getConnectionForComponent($componentName)->getTable($componentName);
    }




[DC-708] Wrong definition for MySQL string primary column Created: 28/May/10  Updated: 12/Jul/10

Status: Open
Project: Doctrine 1
Component/s: Import/Export
Affects Version/s: 1.2.2
Fix Version/s: 1.2.2

Type: Bug Priority: Major
Reporter: Claudio Nicora Assignee: Jonathan H. Wage
Resolution: Unresolved Votes: 0
Labels: None
Environment:

PHP 5.3.1, WinXP-SP3, Doctrine 1.2.2, MySQL 5.1.41


Attachments: File export_mysql_patch.diff    

 Description   

If you define a primary column, the attribute notnull is removed from the column definition because Doctrine assumes that primary columns are always not null.

Now suppose you have a schema like this, with a string primary column.

Client:
  columns:
    serial:    { type: string(50), notnull: true, primary: true }
    name:      { type: string(36), notnull: true }

That's fine, but causes problems with MySQL where the column is created with a default value of "" (empty string) and not <none>.

CREATE TABLE client (serial VARCHAR(50), name VARCHAR(36) NOT NULL, PRIMARY KEY(serial)) ENGINE = INNODB;

Note that the 2nd column is well defined and has <none> as default value (as seen from phpMyAdmin).

I attached a quick-workaround to disable the code which removes the notnull attribute from column definition.

After that the SQL code is like this:

CREATE TABLE client (serial VARCHAR(50) NOT NULL, name VARCHAR(36) NOT NULL, PRIMARY KEY(serial)) ENGINE = INNODB;


 Comments   
Comment by Claudio Nicora [ 28/May/10 ]

Attached a better patch where the notnull attribute is removed only if the primary column type is string

Comment by Claudio Nicora [ 28/May/10 ]

The same behaviour happens even for integer columns, so the notnull attribute should never be removed, not only for string columns. New patch attached, old one removed.

Comment by Jonathan H. Wage [ 08/Jun/10 ]

The patch I see currently just comments out the offending code. Is that intended? It cannot be committed if so

Comment by Claudio Nicora [ 08/Jun/10 ]

My patch only removed the effect, but it's not surely the best solution.

I've no sufficient knowledge on Doctrine to say that commenting out (or removing) that line will not affect other parts; that's why I only commented out the code instead of removing it (both on my side and on the attached patch).

If you think I'm not adding new bugs, I agree that removing the offending code is the cleanest way.

Comment by Jonathan H. Wage [ 08/Jun/10 ]

We can't just remove the code. It will change the behavior of the builder drastically which breaks backwards compatibility.

Comment by Claudio Nicora [ 08/Jun/10 ]

That's what I was afraid of.
Maybe you should change the code that generates the SQL for MySQL to make it include the "NOT NULL" clause to primary keys.

Comment by Jonathan H. Wage [ 08/Jun/10 ]

Hi, if you want to provide a patch that fixes your situation I can test it against our test suite and see if we can include it. You can also run your changes against the test suite to see if it causes any problems.

Comment by Claudio Nicora [ 13/Jun/10 ]

Hi, I attached a patch against Export/Mysql.php instead of the previous against Import/Builder.php.
It works on my side; can you please test it with your test suite?





[DC-651] [PATCH] Doctrine_Record::option('orderBy', ...) of join's right side being applied to refTable in m2m relationship Created: 26/Apr/10  Updated: 17/Apr/14

Status: Open
Project: Doctrine 1
Component/s: Query, Relations
Affects Version/s: 1.2.2, 1.2.3
Fix Version/s: 1.2.2, 1.2.3

Type: Bug Priority: Major
Reporter: suhock Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 2
Labels: None
Environment:

CentOS 5.4
PHP 5.3.2
MySQL 5.1.44, for unknown-linux-gnu (x86_64)


Attachments: File DC651TestCase.php     File Query_orderBy_relation.diff     Text File Ticket_DC651.patch    

 Description   

When using the Doctrine_Record::option('orderBy', ...) feature on a table definition, where that table is the target of a many-to-many join, the specified orderBy columns are applied to the relation table's alias. So for example, given the following definitions:

class User extends Doctrine_Record {
  public function setTableDefinition() {
    $this->hasColumn('uid', 'integer', null, array('primary' => true));
    $this->option('orderBy', 'uid');
  }

  public function setUp() {
    $this->hasMany('Group as groups', array('refClass' => 'UserGroup', 'local' => 'user_uid', 'foreign' => 'group_id'));
  }
}

class Group extends Doctrine_Record {
  public function setTableDefinition() {
    $this->hasColumn('gid', 'integer', null, array('primary' => true));
  }

  public function setUp() {
    $this->hasMany('User as users', array('refClass' => 'UserGroup', 'local' => 'group_gid', 'foreign' => 'user_id'));
  }
}

class UserGroup extends Doctrine_Record {
  public function setTableDefinition() {
    $this->hasColumn('user_uid', 'integer', null, array('primary' => true));
    $this->hasColumn('group_gid', 'integer', null, array('primary' => true));
  }

  public function setUp() {
    $this->hasOne('User as user', array('local' => 'user_uid', 'foreign' => 'uid'));
    $this->hasOne('Group as group', array('local' => 'group_gid', 'foreign' => 'gid'));
  }
}

the following queries:

$query = Doctrine_Query::create()
  ->select('u.*')
  ->from('User u')
  ->leftJoin('u.groups g WITH g.gid=?', 1);
echo $query->getSqlQuery() . "\n";

$query = Doctrine_Query::create()
  ->select('g.*')
  ->from('Group g')
  ->leftJoin('g.users u WITH u.uid=?', 1);
echo $query->getSqlQuery() . "\n";

will output the following:

SELECT u.uid AS u__uid FROM user u LEFT JOIN user_group u2 ON (u.uid = u2.user_uid) LEFT JOIN group g ON g.gid = u2.group_id AND (g.gid = ?) ORDER BY u.uid
SELECT g.gid AS g__gid FROM group g LEFT JOIN user_group u2 ON (g.gid = u2.group_gid) LEFT JOIN user u ON u.uid = u2.user_id AND (u.uid = ?) ORDER BY u.uid, u2.uid

The orderBy option() call is applied to the User definition. The SQL for the first query is correct (where User is on the left side of the join). The SQL for the second query (where User is on the right-most side of the join), however, is obviously incorrect (UserGroup doesn't even have a uid column). Basically, User's orderBy option is being applied to both the User table and its respective reference table, UserGroup, when it is the target of a join.

After digging through the source for a while, I believe I've come up with a patch for this issue (which should be checked by someone more knowledgeable of Doctrine's internals). Basically, in the Doctrine_Query::buildSqlQuery() function, a call is made to Doctrine_Relation::getOrderByStatement() with the reference table (UserGroup)'s alias (u2), which in turn makes a call to Doctrine_Table::getOrderByStatement() on the referenced table (User), filling in the ORDER BY clause with User columns using UserGroup's alias. My solution was to reorder the logic so that the test for a reference class is made before the initial call to getOrderByStatement() is made. It seems to work against my test case and the test cases in the repository. I'll post my patch momentarily.

This bug was first mentioned in the comments in DC-313, but the original ticket comes across as more of a feature request for the hasMany() orderBy feature.



 Comments   
Comment by suhock [ 26/Apr/10 ]

attached a test case for this bug

Comment by suhock [ 26/Apr/10 ]

patch against /branches/1.2 HEAD (should also work apply to 1.2.2 tag)

Comment by Dan Ordille [ 30/Aug/10 ]

I can confirm this as an issue. However I don't think the above patch adequately fixes the problem it seems like with it an order by is still added for the ref column however the relation alias is lost.

My query with the patch became
SELECT g.gid AS g__gid FROM group g LEFT JOIN user_group u2 ON (g.gid = u2.group_gid) LEFT JOIN user u ON u.uid = u2.user_id AND (u.uid = ?) ORDER BY u.uid, uid

I made an another patch that prevents this extra order by clause from being added and have attached it.

Comment by suhock [ 21/Sep/10 ]

I tried out the new patch (Query_orderby_relation.diff), but it provides a reversed diff (patching goes from a patched version to the original). After applying it manually, it fails the provided test case and several additional test cases from the repository.

The original patch DOES pass the provided test case, when applied against 1.2.2, 1.2.3, or the 1.2 branch from the repository. It does not pass, however, Doctrine_Query_Orderby_TestCase. As the previous poster mentioned, it fails to resolve aliases in instances where the 'orderBy' option is specified in a relation definition.

I deleted the original patch and am providing a revised patch (Ticket_DC651.patch) against branch 1.2 HEAD (also works with 1.2.3), which fixes this issue. It passes all working test cases, including Doctrine_Query_Orderby_TestCase and DC651TestCase.

Comment by José De Araujo [ 31/Aug/11 ]

I had this issue recently on a application I'm working on as described the oderBy option was applied on the joined table on a column that even doesn't exist in it. I used the DC651 patch provided and it solved the issue, so far I haven't seen any side effect to it.





[DC-1006] Custom geometric query error with orderBy Created: 22/May/11  Updated: 17/Apr/14

Status: Open
Project: Doctrine 1
Component/s: Attributes
Affects Version/s: None
Fix Version/s: 1.2.2

Type: Bug Priority: Minor
Reporter: Leonardo Lazzaro Assignee: Jonathan H. Wage
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Symfony 1.4.11 and Doctrine 1.2.4. Ubuntu 11.04 Apache2 with mod php5. php5 version PHP 5.3.5-1ubuntu7.2 with Suhosin-Patch



 Description   

Mi Doctrine_Query with this Geometric Query fails with orderBy , but with where works.

$distance = "glength(linestringfromwkb(linestring(GeomFromText('POINT( ".$object->getLatitude()." ".$object->getLongitude() .")'),l.point))) "

This works:
SomeObjectTable::getInstance()>createQuery()>where($distance.' < ?',0.05 )

But this one fails at version 1.2.4, with older version was working.
SomeObjectTable::getInstance()>createQuery()>where($distance.' < ?',0.05 )->orderBy($distance)

Well the problem is at line 74 of OrderBy.php :
$componentAlias = implode('.', $e);

the rendered query in the distance has some ".", for example:
POINT( -34.470829 -58.5286102)

then the after line 74 in OrderBy tries to search por '58' class.

I manually added to DataDict in Mysql.php the POINT datatype (this fix solve me the problem in older versions of Doctrine).






[DC-803] Syntax error in MySQL migration to drop constraint (patch supplied) Created: 29/Jul/10  Updated: 29/Jul/10

Status: Open
Project: Doctrine 1
Component/s: Migrations
Affects Version/s: 1.2.2
Fix Version/s: 1.2.2

Type: Bug Priority: Minor
Reporter: Gavin Davies Assignee: Jonathan H. Wage
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Debian, PHP 5.3.2, MySQL


Attachments: Text File SyntaxFixForMySQLDropConstraintInExport.patch    

 Description   

I have a migration that adds constraints correctly. When migrating down, however, I get a syntax error

ErrorMessage
  Error #1 - SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CONSTRAINT unique_username_idx' at line 1. Failing Query: "ALTER TABLE conUser DROP CONSTRAINT unique_username_idx"

Here is the down migration:

DownMigration
    public function down() {
        $this->dropConstraint('conUser', 'unique_username_idx');
    }

The SQL generated is "ALTER TABLE conUser DROP CONSTRAINT unique_username_idx". This post (http://forums.mysql.com/read.php?98,70887,70974#msg-70974) suggests that in MySQL the syntax should be "ALTER TABLE conUser DROP INDEX unique_username_idx" as constraints are basically indexes in MySQL (unlike MSSQL and Oracle). Doctrine's lib/Doctrine/Export/MySql.php appears to have a syntax error in the dropConstraint method. I attach a patch for this, but the only change is replacing "$name = 'CONSTRAINT '" with "$name = 'INDEX '" in dropConstraint. The migration then runs as I would expect.

Sorry if this has been fixed elsewhere, I did a search but couldn't find a similar ticket.



 Comments   
Comment by Gavin Davies [ 29/Jul/10 ]

fixing syntax





Generated at Sun Nov 23 05:03:35 UTC 2014 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.