In some circumstances Doctrine_Core::getTable('%Name%') returns Doctrine_Table instance instead of %Name%Table one.
So custom %Name%Table's methods become unavailable.

In order to give a demonstration of this improper behavior : here is a schema of small issue tracking system

User:
actAs: [Timestampable]
tableName: issue_user
columns:
user_id:

user_name:

user_role:

managed_by:

password:

salt:

relations:
Manager:
foreignAlias: Workers
class: User
local: managed_by
foreign: user_id
type: one
foreignType: many

Issue:
actAs: [Timestampable]
columns:
issue_id:

from_ceh:

from_name:

from_phone:

from_location:

comp_name:

comp_serial:

comp_os:

issue_title:

comment:

owner_id:

is_executed:

relations:
Owner:
onDelete: CASCADE
foreignAlias: Issues
class: User
local: owner_id
foreign: user_id
type: one
foreignType: many

When I just call Doctrine_Core::getTable('User') it returns UserTable instance, but if I call it after such a query:

Doctrine_Query::create()
->select('u.user_id, ws.user_id, i.*')
->from('User u, u.Workers ws, ws.Issues i')
->where('u.user_id=', $manager_id)
->fetchArray();

calling Doctrine_Core::getTable('User') returns Doctrine_Table instance

I've copy & pasted example from http://www.doctrine-project.org/projects/orm/1.2/docs/manual/defining-models/1_0#relationships:join-table-associations:self-referencing-nest-relations:non-equal-nest-relations .
I've created User backend module using doctrine:generate-admin backend User task. On how to reproduce the error:

1. Add 3 User objects (A,B,C)
2. Open generated edit form for User A.
3. Set User B as Children from Children list and Save
4. Set User B and C as Chidren from Children list and Save

As a result you will see only C set as Children, and strange situation in database :

UserReference Table:

parent_id | child_id
pk_B | pk_B (!!!)
pk_A | pk_C

I find no way to define automatic orderBy in m2m relations with column not from reference table, but actual related table.

E.g. BlogPost <= m2m through BlogPostCategory => BlogCategory
I need BlogPost->Categories ordered by BlogCategory.name

class BlogPost extends Doctrine_Record
{
    public function setTableDefinition()
    {
        $this->hasColumn('title', 'string', 128);
        $this->hasColumn('content', 'string');
    }
    
    public function setUp()
    {
        $this->hasMany('BlogCategory as BlogCategories', array('local' => 'id_blog_post', 'foreign' => 'id_blog_category', 'refClass' => 'BlogPostCategory', 'orderBy' => 'name'));        
    }
}

class BlogCategory extends Doctrine_Record
{
    public function setTableDefinition()
    {
        $this->hasColumn('name', 'string', 128);
    }
    
    public function setUp()
    {
        $this->hasMany('BlogPost as BlogPosts', array('local' => 'id_blog_category', 'foreign' => 'id_blog_post', 'refClass' => 'BlogPostCategory'));
    }
}

class BlogPostCategory extends Doctrine_Record
{
    public function setTableDefinition()
    {
        $this->hasColumn('id_blog_post', 'integer', null, array('primary' => true));
        $this->hasColumn('id_blog_category', 'integer', null, array('primary' => true));
    }
    
    public function setUp()
    {
        $this->hasOne('BlogPost', array('local' => 'id_blog_post', 'foreign' => 'id', 'onDelete' => 'CASCADE'));
        $this->hasOne('BlogCategory', array('local' => 'id_blog_category', 'foreign' => 'id', 'onDelete' => 'CASCADE'));
    }
}

The resulting query contains doubled 'name' column in ORDER BY clause, both from reference table and related table, e.g. ORDER BY t2.name, t3.name

I tried putting the following code in BlogCategory::setTableDefinition() instead of attribute in relation definition in BlogPost record:

$this->option('orderBy', 'name');

but the result was the same.

Maybe I'm doing something wrong? Is there a possibility to define an alias, where to get column name from - in orderBy attribute?

Thanks in advance.

Doctrine_Relation_ForeignKey::fetchRelatedFor() executes the following code at line 70:

$coll = $this->getTable()>getConnection()>query($dql, $id);
$related = $coll[0];

As you can see it accesses the first element by using index "0" in $coll and hence ignores a modified ATTR_COLL_KEY-setting, for instance:

$this->setAttribute(Doctrine_Core::ATTR_COLL_KEY, 'id');

Fortunately I had two models (ForeignA and ForeignB) in my project which both have an one-to-one relation to a third model (Main). That helped me finding this bug which causes the following strange behavior:

// program 1:

$m = new Main();
$m->name = 'M1';
$m->setForeignA(new ForeignA()); // has ATTR_COLL_KEY changed to 'id'
$m->setForeignB(new ForeignB());
$m->save();

// program 2:

$m = Doctrine_Core::getTable('M1')->findOneBy('name', 'M1');
$m->getForeignA()->exists(); // false
$m->getForeignB()->exists(); // true

-------------------------

The big problem about this issue is that behavior is inconsistent. If you don't split the example above into two separate programs/processes you won't have problems, since Doctrine accesses the reference which was stored when calling save().

You will get into trouble using functional tests in Symfony. Since there is only a single process for all requests I wasn't able to reproduce a problem caused by this bug which appeared in the production environment.

-------------------------

Edit:

Doctrine_Connection::queryOne() is affected as well!

A solution is to replace

$coll = $this->getTable()>getConnection()>query($dql, $id);
$related = $coll[0];

by

$related = $this->getTable()>getConnection()>query($dql, $id)->getFirst();

Here's my YAML-file for the model: http://pastie.org/1290649

I'm using the following command to build the whole model: symfony doctrine:build --all --and-load
By this command symfony will create the model using Doctrine.

Please have a closer look at the class BaseTicketHasHardware: http://pastie.org/1290737
If I compare it to the class BaseTicketHasNote I expect a class like this: http://pastie.org/1290765
But Doctrine has created this class: http://pastie.org/1290766

Some time ago Jon Wage suggested that one can override the 8-byte default integer type for IDs by setting Doctrine_Core::ATTR_DEFAULT_IDENTIFIER_OPTIONS in configureDoctrine (in a Symfony project), like this:

public function configureDoctrine(Doctrine_Manager $manager)

This works for primary key id columns. However it is not respected by foreign key id columns, which do not consult ATTR_DEFAULT_IDENTIFIER_OPTIONS.

I looked at working around this using ATTR_DEFAULT_COLUMN_OPTIONS, however it is not type-specific. So if you set a length of 4 with that option, it applies not just to all integers but also to dates, datetimes, booleans and many other things that definitely should not be 4 bytes.

The correct fix seems to be for foreign key id columns to respect ATTR_DEFAULT_IDENTIFIER_OPTIONS.

Also, ATTR_DEFAULT_COLUMN_OPTIONS should probably let you specify different defaults for each column type as the length option is basically not usable in its current form. But that would not be a particularly clean solution to the foreign key id problem since limiting non-ID integers to 4 bytes should not be necessary.

• * *

The motivation for this bug report:

The new stable release of sfDoctrineGuardPlugin (for Symfony) does not specify an integer size as it formerly did, so the size of integers now defaults to 8 bytes. This breaks backwards compatibility with existing code that adds foreign key relationships to sfGuard objects like sfGuardUser, etc. Creating migrations to deal with changing this across all tables involved is quite difficult (all foreign key indexes must be dropped and recreated - doctrine:migrations-diff is unable to figure it out, understandably).

First run of generate-migrations-diff and migrate creates 2 migration diff files. First one for new tables, second one for new indexes and foreign keys. Than if I run generate-migrations-diff again another version is created although nothing was changed and following is inside:

• 1st entry tries to drop a foreign key never been created and not existing in file
• next entry tries to create a foreign key already existing
• 3rd entry tries to create an existing index

After a long try and errorI found out that it's only happening with I18n plus Versionable behavior.

As I already have spent much time for a report please have also a look at: http://forum.diem-project.org/viewtopic.php?f=2&t=173&sid=5e0e3349c0e15a169bc9990a3104b3f6#p465

As I'm quite new to Doctrine and Symfony systems I cannot get further, but willing for more investigation if just one could give me a hint where to start.

We are working on a symfony/doctrine project and have come to a near halt on development.

We feel that now, it may be a bug/feature in doctrine, regarding foreign keys

Part of our model includes a join table that references three different table. Below is a diagram of what the model looks like, and the relevant portion of our schema.yml is at the bottom.

Image of our schema: http://imgur.com/dfFYI.png

We have a form that contains a set of embedded forms that attempt to create a new Person entry and add rows to the join table, adding items to the PersonName table as needed.

The form attempts to do this by creating and saving PersonName objects with NameType parameters, but are running into the problem of Doctrine not including that column when trying to do an insert into the join table.

Part of the problem seems to be caused by the Doctrine_Connection_UnitOfWork::saveAssociations method:

foreach ($v->getInsertDiff() as $r)

Are we correct in understanding that this means that Doctrine 1.2 does not support tables with multiple foreign keys in this scenario?

Here is the relevant portion of schema.yml:

agPerson:
columns:
id:
primary: true
type: integer(5)
autoincrement: true
relations:
agPersonName:
class: agPersonName
refClass: agPersonMjAgPersonName
local: person_id
foreign: person_name_id
agPersonNameType:
class: agPersonNameType
refClass: agPersonMjAgPersonName
local: person_id
foreign: person_name_type_id
agPersonNameType:
columns:
id:
primary: true
type: integer(2)
autoincrement: true
person_name_type:
unique: true
type: string(30)
notnull: true
app_display:
default: 1
type: boolean
notnull: true
relations:
agPerson:
class: agPerson
refClass: agPersonMjAgPersonName
local: person_name_type_id
foreign: person_id
agPersonName:
class: agPersonName
refClass: agPersonMjAgPersonName
local: person_name_type_id
foreign: person_name_id
agPersonName:
columns:
id:
primary: true
type: integer(5)
autoincrement: true
person_name:
unique: true
type: string(64)
notnull: true
relations:
agPerson:
class: agPerson
refClass: agPersonMjAgPersonName
local: person_name_id
foreign: person_id
agPersonNameType:
class: agPersonNameType
refClass: agPersonMjAgPersonName
local: person_name_id
foreign: person_name_type_id
agPersonMjAgPersonName:
columns:
id:
primary: true
type: integer(5)
autoincrement: true
person_id:
type: integer(5)
notnull: true
person_name_id:
type: integer(5)
notnull: true
person_name_type_id:
type: integer(2)
notnull: true
is_primary:
type: boolean
notnull: true
indexes:
UX_ag_person_mj_ag_person_name:
fields: [person_name_id, person_name_type_id, person_id]
type: unique
relations:
agPerson:
local: person_id
foreign: id
agPersonName:
local: person_name_id
foreign: id
agPersonNameType:
local: person_name_type_id
foreign: id
actAs:
Timestampable:

As a caveat: we've noticed that sfdoctrineguard group table, has such a relationship:

mysql> describe sf_guard_user_group;
---------------------------------------+

---------------------------------------+

---------------------------------------+

i.e. IT has two foreign keys taken into account

Sorry for the lengthy explanation, couldn't make it more straight forward:

I have a model which is similiar to a nestet set but the tree structure needs to overlap:

For Model A, every Object A1 can have multiple descendant objects A2 and in turn can be a descendant of multiple objects A0.

Since I saw no way to do this with Nested-Set Relations (or Equal-Nested-Sets) I have set up my Model like this:

modules:
columns: ..<do not matter>
relations:
Children:
class: modules
refClass: modules_required
Parents:
class: modules
refClass: modules_required

modules_required:
columns: <do not matter here, just 2 foreign key columns>
relations:
Children:
Parents:

I needed to specify the Relations on both tables, to use onDelete/onUpdate CASCADE rules. Generated Models look fine, just as intended.
(Every Class has many Children and has many Parents...)

Now the strange part:
When I update an object of modules (say id=18), Doctrine issues the following queries:
DELETE FROM modules_required WHERE (required_id = ? AND module_id IN (?, ?, ?, ?, ?)) - (18, 25, 26, 32, 34, 35)
// where 25 to 35 are CHILDREN of 18
UPDATE modules_required SET required_id = ? WHERE module_id = ? AND required_id = ? - (25, 25, 10)
UPDATE modules_required SET required_id = ? WHERE module_id = ? AND required_id = ? - (26, 26, 10)
UPDATE modules_required SET required_id = ? WHERE module_id = ? AND required_id = ? - (32, 32, 10)
UPDATE modules_required SET required_id = ? WHERE module_id = ? AND required_id = ? - (34, 34, 10)
UPDATE modules_required SET required_id = ? WHERE module_id = ? AND required_id = ? - (35, 35, 10)
UPDATE modules_required SET required_id = ? WHERE module_id = ? AND required_id = ? - (25, 25, 12)
//where 10 and 12 are PARENTS of 18
and somewhen, Doctrine encounters an MySQL ERROR because of the previous update marathon:
SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '25-25' for key 'PRIMARY'

The point is:
1. why is Doctrine trying to create self-referencing relations, and
2. why is it touching the relation at all, when i only did change some text fields in the object?

Is there a better way to solve my problem?

Ok, so the object relations go like this:

• Comparison
  • [1:N] Product (FK:comparison_id)
    • [1:N] Rules (FK:product_id, FK:option_id)
    • [1:N] Parameters (FK:product_id)
      • [1:N] Options (FK:parameter_id)

The testing code looks like following:

== CODE START ==

$comp = new Application_Model_Comparison();

/* Filling $comp with data */

for ($i = 0; $i < 10; $i++) {

    $product = new Application_Model_Product();

    // Options referenced in Rules
    $options = array();

    for ($j = 0; $j < 10; $j++) {

        $param = new Application_Model_Parameter();

        for ($k = 0; $k < 10; $k++) {

            $option = new Application_Model_Option();

            $param->Options->add($option);

            // Register a single option for this parameter
            if (!isset($options[$j])) {
                $options[$j] = $option;
            }
        }

        $product->Parameters->add($param);
    }

    for ($j = 0; $j < 10; $j++) {
        $rule = new Application_Model_Rule();

        $rule->Option = $options[$j];
        $product->Rules->add($rule);
    }

    $comp->Products->add($product);
}

/**
 * The first save() goes nicely, all objects
 * are created (INSERTed)
 */ 

$comp->save();

// Remove every second product
$pCount = $comp->Products->count();

for ($i = 0; $i < $pCount; $i += 2) {
    $comp->Products->remove($i);
}

/**
 * Fails while trying to save
 *
 * Comparison->Product->Parameter->Option
 * INSERT ... `parameter_id` cannot be NULL
 *
 * Comparison->Product->Rule
 * INSERT ... `product_id` cannot be NULL
 */

$comp->save();

== CODE END ==

The first save() cleans up the relation information in the graph. All child objects are INSERTED instead of UPDATE.

In PHP 5.4 there is a change in the way the object references are serialized:

Quote:
"Support for object references in recursive serialize() calls
Prior to PHP 5.4, object references where not saved in recursive serialize calls."

This minor change, breaks down serialization of collections when column of type "array" is present - double serialization occurs.
I'm attaching a patch fixing the issue.

Hello,

Here's a small usage example of linking many-to-many relationships. The group is not linked with the user after calling link() as can be seen by comparing the group ids before and after the call.

User:
  tableName: users
  columns:
    username:      { type: string(255), notnull: true }
    email:         { type: string(255), notnull: true }
  indexes:
    username_idx:
      fields: [username]
      type:   unique
    email_idx:
      fields: [email]
      type:   unique
  actAs: [Timestampable]
  relations:
    groups:
      class:    Group
      alias:    Groups
      refClass: UserGroupRelation

Group:
  tableName: groups
  columns:
    name: { type: string(255), notnull: true }
  actAs: [Timestampable, NestedSet]
  relations:
    users:
      class:    User
      alias:    Users
      refClass: UserGroupRelation

UserGroupRelation:
  tableName: user_group_relations
  columns:
    user_id:  { type: integer, notnull: true, primary: true }
    group_id: { type: integer, notnull: true, primary: true }
  relations:
    Group:
      class:        Group
      foreignAlias: UserGroupRelations
      onDelete:     CASCADE
    User:
      class:        User
      foreignAlias: UserGroupRelations
      onDelete:     CASCADE

$u = Doctrine_Core::getTable('User')->findOneByUsername('james');
$g = Doctrine_Core::getTable('Group')->findOneByName('group');
var_dump($g['id']);
$current = array();
foreach ($u['UserGroupRelations'] as $r) {
    $current[] = $r['group_id'];
}
var_dump($current);
$u->link('Groups', array($g['id']), true);
$u = Doctrine_Core::getTable('User')->findOneByUsername('james');
$current = array();
foreach ($u['UserGroupRelations'] as $r) {
    $current[] = $r['group_id'];
}
var_dump($current);

string(1) "2"
array(3) {
  [0]=>
  string(1) "1"
  [1]=>
  string(1) "5"
  [2]=>
  string(1) "6"
}
array(3) {
  [0]=>
  string(1) "1"
  [1]=>
  string(1) "5"
  [2]=>
  string(1) "6"
}

If the schema has an entity MyMain with all its fields i18n and an entity MySecondary related to MyMain and you try to create a new MySecondary item using Symfony forms you get an error.

I think the error is doctrine and in i18n because if you remove a field from the i18n behavior (fields: [a,b,c,d], for example) all works ok.
I've included a symfony project to help developpers replicate the error.

My Schema

 
MyMain:
  actAs:
    I18n:
      fields: [a,b,c,d, e]
  columns:
    a:  { type: string(255), notnull: true }
    b:  { type: string(255), notnull: true }
    c:  string
    d:  string
    e:  string

MySecondary:
  columns:
    my_main_id:  integer
    a:        string(255)
    b:        string(255)
  relations:
    MyMain:
      local: my_main_id
      foreign: id
      foreignType: one
      cascade: [delete]

And the form code

class MyMainForm extends BaseMyMainForm
{
  public function configure()
  {
    $this->embedI18n(array('en', 'es'));
  }
}

class MySecondaryForm extends BaseMySecondaryForm
{
  public function configure()
  {
    $this->widgetSchema['my_main_id'] = new sfWidgetFormInputHidden();
    $this->embedRelation('MyMain as main_subform');
  }
}

And last the queries (and its order when you get the error)

INSERT INTO my_secondary (my_main_id, a, b) VALUES (?, ?, ?) - (, my_secondary[a], my_secondary[b])
INSERT INTO my_main_translation (id, lang, a, b, c, d, e) VALUES (?, ?, ?, ?, ?, ?, ?) - 
  (, en, my_secondary[main_subform][en][a], my_secondary[main_subform][en][b], my_secondary[main_subform][en][c], 
   my_secondary[main_subform][en][d], my_secondary[main_subform][en][e])
Error Doctrine_Connection_Mysql_Exception	SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'id' cannot be null 

Doctrine's cloning feature is based on the Doctrine_Record::copy($deep = false) method, while the $deep param says whether to clone the related objects as well, or not.

However, considering the perspective of the application logic, the functional need can also be stated as: "I wish you to clone yourself, expecting you to know yourself which related objects you need to clone as well, or even to de-reference, or whatever is needed (e.g. to remove the relation)."
For this, the $deep argument is somehow inaccurate, as it does not take into consideration the different meanings of the relations:

• some relations (namely aggregations in UML terminology) need that the cloned object shall still refer to the same object (like referenced by the cloned object)
• some relations (namely compositions) need to clone the related objects as well, as the latter are owned by the main object.

Practical example: when cloning an invoice (Doctrine_Record), application logic expects that its invoice line items be cloned as well (compositions), but not the reference to the customer (cloning my customers this way will not increase my business .

Thus it would be great if there were a means to specify the needed behaviour in the schema.yml etc.
Thinkable approach: extend the "application-level cascade" feature of Doctrine by adding a "clone" or "copy" parameters. Then the copy() method in Doctrine_Record will know which related objects to clone as well, or not, or to clear (null). I guess it's quite analogous to the "delete" cascade stuff.

So far my proposal. In case it will find support, I will then be happy to provide an implementation proposal.

I have been using Doctrine 1.1.6.

I have two related models, which are always saved together. In my application, save is called on class A and that save cascades to class B. I have a save listener which in some cases can intercept class A's save operation and call Doctrine_Event::skipOperation() to prevent class A from being persistently saved. However, in such cases, class B is still saved - the skipOperation() does not skip the entire operation, just class A's save operation, allowing subsequent operations to occur as normal.

This is the desired functionality and has been working fine. However, I have just upgraded to 1.2.1. only to find that something has changed, causing the skipOperation() to skip the entire operation, therefore not saving class B when class A's listener interferes.

I presume this is a bug. If not, what precisely has changed with skipOperation(), and how can I keep the traditional functionality?

When building models and database structure from YAML schema, Doctrine ignores the behaviors and relations on models if it is named "User":

...

User:  
  actAs:
    Timestampable:
    Sluggable:
      unique: true
      fields: username
      canUpdate: true
  columns:
    id:
      type: integer(4)
      primary: true
      autoincrement: true
    company_id
      type: integer(4)
    timezone_id:
      type: integer(1)
    role_id:
      type: integer(1)
    email:
      type: string(255)
    username:
      type: string(255)
      unique: true
    password:
      type: string(40)
    firstname:
      type: string(255)
    lastname:
      type: string(255)
    last_login:
      type: datetime
  relations:
    Company:
      local: company_id
      foreign: id
    Timezone:
      local: timezone_id
      foreign: id
    Role:
      local: role_id
      foreign: id

...

This creates the following table structure:

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `company_id` int(11) DEFAULT NULL,
  `timezone_id` tinyint(4) DEFAULT NULL,
  `role_id` tinyint(4) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `username` varchar(255) DEFAULT NULL,
  `password` varchar(40) DEFAULT NULL,
  `firstname` varchar(255) DEFAULT NULL,
  `lastname` varchar(255) DEFAULT NULL,
  `last_login` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The table structure is both missing foreign key constraints to Company, Timezone and Role. It is also missing columns updated_at and created_at for Timestampable behavior. The slug column for Sluggable behavior is missing, as well.

When looking at the BaseUser::setUp() method it looks as it is supposed to:

class BaseUser extends Doctrine_Record
{

    ....

    public function setUp()
    {
        parent::setUp();
        $this->hasOne('Company', array(
             'local' => 'company_id',
             'foreign' => 'id'));

        $this->hasOne('Timezone', array(
             'local' => 'timezone_id',
             'foreign' => 'id'));

        $this->hasOne('Role', array(
             'local' => 'role_id',
             'foreign' => 'id'));

        $timestampable0 = new Doctrine_Template_Timestampable();
        $sluggable0 = new Doctrine_Template_Sluggable(array(
             'unique' => true,
             'fields' => 'username',
             'canUpdate' => true,
        ));
        $this->actAs($timestampable0);
        $this->actAs($sluggable0);
    }

    ....

}

So the only area where it goes wrong, is when generating the queries for creating the model tables. I don't know if the same problem appears with other model names, but it doesn't like models named "User".

If I rename the model to "Person" and rebuild, it all works perfectly as it should.

There is a problem when you save Many to Many Self Referencing (Nest Relations).

I used the same example that it's used on Doctrine 1.2 Self Referencing (Nest Relations) Documentation:

My Schema:

User:
columns:
name:

Unknown macro: { type}

relations:
Parents:
class: User
local: child_id
foreign: parent_id
refClass: UserReference
foreignAlias: Children

UserReference:
columns:
parent_id:
type: integer
primary: true
child_id:
type: integer
primary: true

Fixtures:

User:
james:
name: James
alexander:
name: Alexander
david:
name: David

The problem happens with the Children. The first time I assigned children and saved the User, there is no problem; but when I saved the User again (without any change on him) the values in the UserReference? table changes: the "parent_id" takes the value of the "child_id".

I test it with Sf 1.4 and with Sf 1.3 stable versions, and I've got the same error.

If it not clear to understand what I'm saying, I put a "very ilustrative" image about the form and how change the values of the UserReference? table. Please see it, because it would be very usefull for understanding (explains better than my terrible English)

Thanks for all your work!

P. D.

I forgot to to say that watching the stack trace I think that the problem is in:

symfony/lib/plugins/sfDoctrinePlugin/lib/vendor/doctrine/Doctrine/Connection/UnitOfWork.php

In the line 429:

public function saveAssociations(Doctrine_Record $record)

I would like to help more, but I couldn't figure out how this function works.

While implementing a Record_Template and manipulating a query by using the preDqlSelect Event, i got massive problems if the query was generated by a refresh() or refreshRelated() call. I found out that the issue appears because the query has no alias for the table then. This makes it hard to add forther (sub-)selects to the query via preDqlSelect, e.g.

$params = $event->getParams();
		$alias = $params['alias'];

                if(!$query->isSubquery() && $alias == $queryClone->getRootAlias()) {	
				
				
				$countQuery = '(SELECT COUNT('.$alias.'_tcount.uid) FROM '.$modelName.' '.$alias.'_tcount WHERE '.$alias.'.'.$groupId.'='.$alias.'_tcount.'.$groupId.') AS '.$this->_options['terminationCount'];
				
				$query->addSelect($countQuery);
			}

The issue is solved by adding an alias in the foreignKey and localKey relation types' fetchRelatedFor methods (at least that's sufficient for my issue, maybe there is a need to add similar lines to the other relation types).

Doctrine_Relation_ForeignKey (from line 60):

$alias = $this->getTable()->getComponentName().'_rel';
            	
                $dql  = 'FROM ' . $this->getTable()->getComponentName()
                      .' '.$alias. ' WHERE ' . $this->getCondition($alias) . $this->getOrderBy(null, false);

Doctrine_Relation_LocalKey (from line 58):

$alias = $this->getTable()->getComponentName().'_rel';
        	
            $dql  = 'FROM ' . $this->getTable()->getComponentName()
                 .' '.$alias. ' WHERE ' . $this->getCondition($alias) . $this->getOrderBy(null, false);

After query a record from the DB, where a foreign key is null, and changing only the reference belonging to this foreign key, and only these reference no other value of the record, a save of the record doesn't lead to a change in the foreign key field.

For example: I'll save a user record with some data of a regestration and give him the possibillity to add a city where he cames from later in his account settings, if no other user attribute is changed, the state of the user record is still CLEAN. The result is that I can access the user->City property correctly only as long as no refresh from the db is done. So the changing of the foreign key belonging to the city isn't persistent in the DB.

Example Code:
$user = new Model_User();
$user->firstName = md5(rand());
$user->save();

$id = $user->id;
unset($user);
assert(! $user);

$user = Model_UserTable::getInstance()->find($id);
assert(!$user->relatedExists('City'));

$user->City->name = 'Mainz';
$user->save();
unset($user);

$user = Model_UserTable::getInstance()->find($id);
$user->refresh(true);
assert($user->City->name == 'Mainz');

The last assert failse. I realized the problem while updating just a referenz using the fromArray() method like $user->City->fromArray(). The city would be saved correctly in DB but the foreign key in the user table would not be updated. After debugging I recognized that the $user is not replaced in UnitOfWork::saveGraph() because the $user is still in CLEAN state.

Using " $user->coreSetRelated('Country', $user->Country);" or "$user->state(Doctrine_Record::STATE_DIRTY);" after "$user->City->name = 'Mainz'" would fix the prob in this case. As well as creating a new City instance an set $user->City with this instance like:

$city = new Model_City();
$city->name = 'Mainz';
$user->City = $city;

Also $user->isModified(true) is true. So I fixed the issues in my case by changing the state of my records to DIRTY if the "$this->state() == CLEAN && $user->isModified(true) == true" in the preSave() method of an abstract class that my Base classes are extending....

I have 2 tables.

t1 :

id [Primary Key]
field1
field2
...
fieldn
t2_id [related to t2.id]
—
primary key: id

t2:
—
id [related to t1.t2_id]
fixed_set [predefined set of values (for example "a", "b", "c")]
field1
field2
...
fieldn
—
primary key: id + fixed_set

I need to use relation like follows:
SELECT *
FROM t1
LEFT JOIN t2 ON (t1.t2_id = t2.id AND t2.fixed_set = "a")

There is followed text in phpdoc from Relation.php:
—

• local the local field(s)
  *
• foreign the foreign reference field(s)
  —

But it is unable to use several fields in "local" and "foreign", only one "local" and "foreign" field.

Here the definition of the Domain Model : http://pastebin.com/i80d73s8

To resume, Show is a mother class. Video, is a direct extend from Show.
ShowContainer is an abstract class that expend Show. It define an hasMany relation to Video, through CompoShowShow.
Program and Playlist are extends ShowContainer.

the following DQL Query work fine.

$query = Doctrine_Query::create ()
->from ("Model_Program program")
->leftJoin ("program.children children");
$oShow = $query->fetchOne ();
var_dump (get_class ($oShow->children[0])); //Output Model_Video

But, the following code isn't working :

$oProgram = Doctrine_Core::getTable ("Model_Program")->find (2);
$oProgram->children;

//Throw Exception
//Message: Unknown record property / related component "children" on "Model_Program"
//Doctrine\Record.php(1381)

The Program#2 is in the database.

Here we are.

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.

When trying to use a refclass property - i.e. normalization table column - in a query, an unnecessary join is added to the query created, which is then used to "access" the property.

This issue has already been discussed back in May 2008 but still seems to persist.
Please see the google group discussion http://groups.google.com/group/doctrine-user/browse_thread/thread/c655832a450ccd1a/9cb6eca6ae1c3dee for example + comments.

Unfortunately it is hardly possible to write a test case for this, as working SQL is generated, but the query is just not right (test case basically would have to assert the query string, wich does not make much sense).
Another way to test if it does "what it should", could be to assert contents of $q->getTableAliasMap() - but does not seem ideal either.

John Wage commented on this issue
"Can you create a ticket for this with a failing test case? I believe this
should be possible and we should investigate a fix for it.
Thanks, Jon "
http://groups.google.com/group/doctrine-user/browse_thread/thread/c655832a450ccd1a/9cb6eca6ae1c3dee#msg_c73934a38515cadb

Two queries:

1. $ab1 = Doctrine_Query::create()
->from('Model_UserAddressbook ab')
->leftJoin('ab.UserPersons up')
->leftJoin('ab.AdminUsers au'); // second one doesn't have this join

2. $ab2 = Doctrine_Query::create()
->from('Model_UserAddressbook ab')
->leftJoin('ab.UserPersons up');

When executing the following fetchOne:

A.) $x1 = $ab2->fetchOne()->toArray(); // has fields from addressbook, and subarray UserPersons, OK
B.) $x2 = $ab1->fetchOne()->toArray(); // has fields from addressbook, and subarray UserPersons and AdminUsers, OK
C.) $x3 = $ab2->fetchOne()->toArray(); // has fields from addressbook, and subarray UserPersons and AdminUsers, NOT OK

$x1 and $x3 should have the same set of fields, but $x3 has somehow received set of fields from $x2.

I haven't turned on any caching, but even if the caching is on, the result sets shouldn't mix, right?

I'm talking just about field names (set of fields), not fields data.

Hi All
I am running into a strange problem using Doctrine-1.2.2-- I have a multi row result set but only the first row of it is returned in the hyrdated array that is generated.
I have tracked this down in the code to get a basic idea of whats going on – it seems that the $id variable in the hydrateResultSet function in Doctrine_Hydrator_Graph isn't being populated properly with data about the fields that are returning from my query. The _gatherRowData function seems to never detect that one of my columns is an identifier ("if ($cache[$key]['isIdentifier'])

$q = Doctrine_Query::create();
$q->from('Customer Customer');
$q->leftJoin('Customer.Zip Zip');
$q->addSelect('Customer.firstname as first_name');
$q->addSelect('Customer.postalcode as postalcode');
$q->setHydrationMode(Doctrine::HYDRATE_ARRAY);

Generates this DQL: 

SELECT Customer.firstname as first_name, Customer.postalcode as postalcode FROM Customer Customer LEFT JOIN Customer.Zip Zip

And this SQL:

SELECT c.firstname AS c_0, c.postalcode AS c_1 FROM customers c LEFT JOIN zips z ON c.postalcode = z.postalcode

Which results in this return after hyrdration: 

array('0'=>array('first_name'=>'Armando', 'postalcode'=>'00659'))

However the following code hydrates just fine: 

$q = Doctrine_Query::create();
$q->from('Customer Customer');
$q->addSelect('Customer.firstname as first_name');
$q->setHydrationMode(Doctrine::HYDRATE_ARRAY);

As does this code: 

$q = Doctrine_Query::create();
$q->from('Customer Customer');
$q->leftJoin('Customer.Zip Zip');
$q->addSelect('Customer.firstname');
$q->addSelect('Customer.postalcode as postalcode');
$q->setHydrationMode(Doctrine::HYDRATE_ARRAY);

Here is the yaml for the sample data I am testing on: 

detect_relations: false
package: Example
options:
type: INNODB
charset: utf8
Customer:
tableName: customers
columns:
customer_id:
type: integer(4)
primary: true
notnull: true
autoincrement: true
firstname:
type: string(45)
lastname:
type: string(45)
streetaddress:
type: string(45)
city:
type: string(45)
state:
type: string(45)
postalcode:
type: string(45)
relations:
Order:
type: many
local: customer_id
foreign: customer_id
Zip:
type: one
local: postalcode
foreign: postalcode
options:
type: InnoDB
Zip:
connection: default_schema
tableName: zips
columns:
postalcode:
type: varchar(30)
primary: true
latitude: 'float(10,6)'
longitude: 'float(10,6)'
city: string(50)
state: string(50)
country: string(50)
type: string(50)
relations:
Customer:
type: many
local: postalcode
foreign: postalcode

Perhaps there is something simple I am overlooking. To get around this I am just always selecting the primary key from my main table in every query.

Thanks in advance for any advice.
Will Ferrer

I think that this bug has been reported already here:
http://www.doctrine-project.org/jira/browse/DC-329?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
But I thought that it might be useful to submit a very simple test case, with the bug completely isolated.
When you create a self referenced non equal many-to-many relation like: person has many parents AND has many children (taken from the documentation).

In the attached test case, after both collections of related records are altered within the foreach statement, Doctrine somehow messes up the Ids in the reference class. Now, it's important to note, that if you loop through just one of the collections, it won't fail.

Thanks in advance!

it seems that the naming is important:

in a User, Group, UserGroup relation (where the UserGroup defines the many to many relation for User & Group) the constraints are defined well

but if the tables are Order, Service, OrderService it doesn't work => just one constraint is created... please find my example:

Order:
  tableName:          orders
  columns:
    name:             string(50)
  relations:
    Services:
      class:          Service
      local:          order_id
      foreign:        service_id
      refClass:       OrderService

Service:
  actAs:
    Versionable:      ~
  columns:
    name:             string(50)
  relations:
    Orders:
      class:          Order
      local:          service_id
      foreign:        order_id
      refClass:       OrderService

# WTF: it takes alphbetically the constraints building  !?
# if I used ZorderService instead of OrderService, Z is after S (O is before S) the constraints are defined fine!
OrderService:
  columns:
    order_id:         { type: integer, primary: true }
    service_id:       { type: integer, primary: true }

==> the generated SQL

CREATE TABLE orders (id BIGINT AUTO_INCREMENT, name VARCHAR(50), PRIMARY KEY(id)) ENGINE = INNODB;
CREATE TABLE order_service (order_id BIGINT, service_id BIGINT, version BIGINT, PRIMARY KEY(order_id, service_id, 
        version)) ENGINE = INNODB;
CREATE TABLE service_version (id BIGINT, name VARCHAR(50), version BIGINT, PRIMARY KEY(id, version)) ENGINE = 
        INNODB;
CREATE TABLE service (id BIGINT AUTO_INCREMENT, name VARCHAR(50), version BIGINT, PRIMARY KEY(id)) ENGINE = 
        INNODB;
ALTER TABLE order_service ADD CONSTRAINT order_service_order_id_orders_id FOREIGN KEY (order_id) REFERENCES 
        orders(id);
ALTER TABLE service_version ADD CONSTRAINT service_version_id_service_id FOREIGN KEY (id) REFERENCES service(id) 
        ON UPDATE CASCADE ON DELETE CASCADE;

=> as you can see for TABLE order_service just one constraint is defined (order_service_order_id_orders_id) the other one (order_service_service_id_service_id) is missing !!!

However if i change the names for tables (actually i think alphabetically order) and use ZorderService instead of OrderService (lik UserGroup for User & Group) the two constraints are created!

This also affects the "getRelations()" when i tried to write a behavior (it gives back just one relation):

  foreach ($event->getInvoker()->getTable()->getRelations() as $relation)

ps:
also the same bug reported on the list: http://groups.google.com/group/doctrine-user/browse_thread/thread/ad48db71b71e043b

_{(edit G.X: split too long lines in SQL code)}

Doctrine_Validator_ForeignKeys_TestCase#testForeignKeyIsValidIfLocalRelationIsSet()

This test passes fine as is. But it fails if I make the following change:

public function testForeignKeyIsValidIfLocalRelationIsSet()
{
    //$person = new TestPerson();
    $address = new TestAddress();
        
    //$address->Person = $person;    
    $address->Person->first_name = "John";
        
    $table = $address->getTable();
    $errors = $table->validateField('person_id', $address->person_id, $address);
        
    $this->assertEqual(0, $errors->count());
}

The only difference is that instead of explicitly assigning $address->Person, I'm letting it happen automatically when assigning the first name. What is it about the property chaining when creating the related record that screws up doctrine's internal reference tracking?

Attached is a self-contained 80 line test. In the test, Foo has an one to one relation to Bar. There is a null value in the foreign key column (at least one Foo without a Bar). And the relation is being used via this DQL:

Doctrine_Query::create()->from('Foo f')->leftJoin('f.Bar b')->execute();

At around line 254 of Doctrine_Hydrator_Graph/Graph.php, the hydrator sets the value of the relation column to Doctrine_Null if there is no related record:

$prev[$parent][$relationAlias] = $this->getNullPointer();

This causes Doctrine_Record to check if the value of the foreign key field was updated, which it does by calling Doctrine_Record::_isValueModified(). However, that method considers an update from null to Doctrine_Null as a modification. So, the record (and eventually the entire Collection returned) is marked as Doctrine_Record::STATE_DIRTY, even though it contains no modifications. This occurs during hydration, so the caller never sees the record as being clean.

The workaround I'm using is to subclass Doctrine_Record, and return false from _isValueModified() when moving from null to Doctrine_Null. But I think this could also be fixed in the hydrator.

When working with a many to many record, if you toArray then synchronizeWithArray/fromArray the result - you get an exception.

This appears to be due to the fact that toArray returns collections of array(0 => false) (because a record is in a locked state) and fromArray will attempt to link() the refClass (which has multiple identifiers).

Here's a test that should show this behavior:

class Doctrine_Ticket_DC774_TestCase extends Doctrine_UnitTestCase
{
    public function prepareTables()
    {
        $this->tables[] = 'EntityAddress';
        $this->tables[] = 'Book';
        parent::prepareTables();
    }

    public function prepareData()
    {
        $user = new User();
        $user->name = 'TestUser';

        $address = new Address();
        $address->address = 'TestAddress';

        $user->Addresses[] = $address;
        
        $user->save();
    }

    public function testToArraySynchronizeWithManyToMany() {
        $user = Doctrine_Core::getTable('User')->findOneByName('TestUser');
        $user->refreshRelated();
        $user->synchronizeWithArray($user->toArray());
    }

    public function testToArrayFromManyToMany() {
        $user = Doctrine_Core::getTable('User')->findOneByName('TestUser');
        $user->refreshRelated();
        $user->fromArray($user->toArray());
    }
}

Let's say I have following tables:

article

• art_id

article_version

• art_id
• art_version

category

• cat_id

category2article

• cat_id
• art_id

category2article_version

• cat_id
• art_id
• art_version

As I know I can describe relation between tables `article` and `category`, but I cannot describe relation between `artcile_version` and `category`.
The problem is Doctrine supports only following relation mapping:
$this->hasMany('Category as categories', array(
'local' => 'art_id',
'foreign' => 'cat_id',
'refClass' => 'Category2Article'
));

It would be nice to have also something like that:
$this->hasMany('Category as categories', array(
'local' => array('art_id', 'art_version')
'foreign' => 'cat_id',
'refClass' => 'Category2ArticleVc'
));

In the first (supported) example Doctrine produce something like that, when I add LEFT JOIN STATEMENT:
SELECT *
FROM article a
LEFT OUTER JOIN category2article c2a ON c2a.art_id = a.art_id
LEFT OUTER JOIN category c ON c.cat_id = c2a.cat_id

But I would like to have also support for following example:
SELECT *
FROM article_version a_vc
LEFT OUTER JOIN category2article_version c2a_vc ON c2a_vc.art_id = a_vc.art_id AND c2a_vc.art_version = a_vc.art_version
LEFT OUTER JOIN category c ON c.cat_id = c2a_vc.cat_id

–
Very best,
Tomasz Kuter

PS. I will check Doctrine code.. maybe I will solve it by myself..

It would be handy to return a relation array as the result of Doctrine_Table::bind(), because the relation parser's bind() already returns this value.

When a many-tomany relation has been defined on only one of the end of the relation, and ofcourse in the cross-refClass, things get weird. I'll use the user-groups relation (as found in the documentation) as example.

If in the group model has not defined the hasMany, then a user can be instantiated and saved. When instantiated, the relation groups would return an empty array, groups can ben added like:

$u = new User();
var_dump($u->groups);
$u->groups[] = new Group();
var_dump($u->groups);

This would output an empty array, and subsequently an array containing the new group.

However, if a user would be retrieved from the database, and the relation groups would be called, then the following message will appear (which is not helpfull at alllll, I've spend hours figuring it out!):

Uncaught exception 'Doctrine_Record_UnknownPropertyException' with message 'Unknown record property / related component "groups"' .....

I will never forget this, but it's not described in the documentation and the error is not helping either. So either one of these step hvae to be taken. Or even better, making it work without requiring the hasMany in group, to users.

Object1->Unlink(Relation, Id2) doesn't work, if primary key order in table of m2m reference isnt 1:Object 2: Id to unlink, when relation set to EQUAL= TRUE

User:
  actAs: 
    Timestampable: ~
  columns:    
    name:         { type: string(255), notnull: false }
    last_name:    { type: string(255), notnull: false }
    picture:      { type: string(255), notnull: false }
    birthday:     { type: timestamp, notnull: false }
    gender:       { type: string(255) }
    rating:       { type: integer, default:0} 
    num_comments: { type: integer, default:0} 
    num_groups:   { type: integer, default:0} 
  inheritance:
    extends: Member
    type: column_aggregation
    keyField: type
    keyValue: 1                    
  relations:
    Friends:    { onDelete: CASCADE, class: User, foreignAlias: Friends, refClass: FriendReference, local: user1, foreign: user2, equal: true }
    Request:    { onDelete: CASCADE, class: User, foreignAlias: Pending, refClass: PendingReference, local: user1, foreign: user2 }
    Blocker:    { onDelete: CASCADE, class: User, foreignAlias: Blocked, refClass: BlockedReference, local: user1, foreign: user2 }

 
			if(isset($refToRemove))
			{
				switch ($refToRemove)
				{
					case 'FriendReference': $references = $currentUser->Friends; $relation = 'Friends';	break;
					case 'PendingReference': $references = $currentUser->Pending; $relation = 'Pending'; break;
					case 'BlockerReference': $references = $currentUser->Blocker; $relation = 'Blocker'; break;
					case 'RequestReference': $references = $currentUser->Request; $relation = 'Request'; break;
				}
				 
				foreach($references as $reference)
				{

					if ($reference->id == $user2->id)
                        $currentUser->unlink($relation, array($user2->id), $now = true);
				}
                
			}

when you count relations from one side and the other it show that when set to EQUAL = TRUE relations are reciprocal, but unlink still minds primary keys order in order to function

I want to bookmark ads on my site. So i have Ads table, users table (sfDoctrineGuard) and a refclass Bookmarks. I've created an ajax link to an action, where i retrieved $ad object, $user_id and
$ad->link('Bookmarks', $user_id, true);
So I expected a record in a database to be created.

Bookmarks:
...
columns:
user_id:

ad_id:

relations:
sfGuardUser:

Ads:

As my Bookmarks table has 2 primary keys in the link method

if (count($ids) > 0)

$rel->getTable()->getIdentifier() returned me an array('user_id', 'ad_id') instead of a string. So the query which was generated looked like
UPDATE bookmarks SET ad_id = ? WHERE (Array IN )
And Array is not the name of a column.

My schema:
http://pastie.org/private/2lr9gy1h3mtdwdsiwrwylq

I've solved an issue by creating a new Bookmark instance and saving it, but i presume that issue might distract someone.

$record instanceof Doctrine_Record;
$collection instanceof Doctrine_Collection;

Using the $record object I can find out if it has been modified : $record->isModified().
I can also have this check all the relations also by passing true : $record->isModified(true);

However, the $collection object only support the first level and not the relations of its member records,
$collection->isModified() returns false if one of the entry has one its relations modified (as it should).

The improvement would be to allow this method to accept true, like it's $record counterpart, which would return true instead.

See attached patch.

In class Doctrine_Relation_Parser, in method completeDefinition(), at line 508 (for svn r.7676) there is the code:

                foreach ((array) $this->_table->getIdentifierColumnNames() as $id) {
                    // ?? should this not be $this->_table->getComponentName() ??
                    $column = strtolower($table->getComponentName())
                            . '_' . $id;

The comment on line 509 seems right, since $table is a "temporary" variable created in a previous foreach loop a few lines before (l. 472) and not used anywhere else... (I didn't make a test case though, that's why I tagged it as "Task" rather than "Bug")

I attached a patch, just in case.

G.X