Setting:
$conn->setAttribute(Doctrine_Core::ATTR_PORTABILITY, Doctrine_Core::PORTABILITY_FIX_CASE)
$conn->setAttribute(Doctrine_Core::ATTR_FIELD_CASE, CASE_UPPER)

I expect column names in built queries to be uppercase, but they remain to be lowercase.

Schema in yml format
in Symfony it looks more simple, but i'm going to show relation in detail

PrType:
  columns:   
    name:                   { type: string(255), notnull: true }                          

PrTypeTranslation:
  columns:   
    id:                     { type: integer, notnull: true }
    name:                   { type: string(255), notnull: true }
    lang:                   { type: string(255), notnull: true }
  relations:
    PrType:                 { onDelete: CASCADE, local: id_id, foreign: id, foreignAlias: Translation }

When i try to execute this code:

   $q = Doctrine_Query::create()
            ->from('PrType tp')
            ->leftJoin('tp.Translation t WITH t.lang = ?', 'ru')
            ->orderBy('t.name')
            ->limit(10);

doctrine executes next statement:

SELECT "p"."id", "p2"."name" AS "p2__name", "p2"."lang" AS "p2__lang"
FROM "pr_type" "p"
LEFT JOIN "pr_type_translation" "p2" ON "p"."id" = "p2"."id" AND ("p2"."lang" = :oci_b_var_1)
WHERE "p"."id" IN (
              SELECT a."id" FROM ( 
                          SELECT DISTINCT "p3"."id"
                          FROM "pr_type" "p3"
                          INNER JOIN "pr_type_translation" "p4" ON "p3"."id" = "p4"."id" AND ("p4"."lang" = 'ru') 
                          ORDER BY "p4"."name" ) a 
                          WHERE ROWNUM <= 10) 
ORDER BY "p2"."name"

This sql code produces next error

ORA-01791: not a SELECTed expression

Error occures, because (from ORACODE)

There is an incorrect ORDER BY item. The query is a SELECT DISTINCT query with an ORDER BY clause. In this context, all ORDER BY items must be constants, SELECT list expressions, or expressions whose operands are constants or SELECT list expressions

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

In revision 7691 something has happened. Ever since I updated my Doctrine to that revision all my queries having " ... fieldname AS aliasname ... " go crazy and make the PHP to throw an exception, like this:

'Doctrine_Connection_Mysql_Exception' with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 't.`id`' in 'field list'. Failing Query:
"SELECT `t`.`id` AS `t_id`, `t`.```id``` AS `t0`, `t`.```name``` AS `t1`, `t`.`id` AS `t0`, `t`.`name` AS `t_1` FROM `territoryCombined` `t` ORDER BY `t`.`name` asc"'
in ...path here.../doctrine/lib/Doctrine/Connection.php:1082

The problem is that the DQL parser somewhere along the process encapsualtes aliases in ticks, but then it does it again in lib/Doctrine/Formatter.php : quoteIdentifier() , which is called in lib/Doctrine/Connection : quoteIdentifier() , which is called in lib/Doctrine/Query.php : processPendingFields() @ between lines 485 and 512. The problem is that by the time the alias name gets to line 507 it is already encapsualted in ticks, but it does it again. At the end we end up with ```alias``` , which is not good.

It only happens to aliases. If I say select('*') or select("t.id, t.name") then it executes properly. Only the aliases couse problems.

A test query:

$vTerritories = Doctrine_Query::create()
->select("t.id as territory_id, t.name as territory_name")
->from('TerritoryCombined t')
->orderBy('t.name asc')
->fetchArray();

MY PROPOSED PATCH:

If I change the Formatter::quoteIdentifier() to this:

public function quoteIdentifier($str, $checkOption = true)

then it works correctly. Please note I only tested that in MySQL, as we use MySQL in all our projects.

Problem exists when Doctrine formulates a subquery to perform a limit when a join in included.

The problem is that the where clause that doctrine creates for the subquery (the WHERE IN clause) is inserted as the first where clause.
This will break the parameter duplicate done at Doctrine_Query_Abstract:969, as the order of the parameters is now wrong.

Consider:

 
select * from table
join metadata WITH c = ?
where a = ? and b = ?
limit 1

with parameters be (1, 2, 3)

Doctrine will translate this to

 
select * from table
join metadata WITH c = ?
where table.id IN (
    select id from table
    join metadata WITH c = ?
    where a = ? and b = ?
    limit 1
)
and a = ? and b = ?

Doctrine will duplicate the params (Doctrine_Query_Abstract:969) to (1, 2, 3, 1, 2, 3), but now they are in the wrong order completely.
They should be (1,1,2,3,2,3).

The easy fix is to move the limit subquery to the LAST where clause, which would reuslt in a query like so:

 
select * from table
join metadata WITH c = ?
where a = ? and b = ?
and table.id IN (
    select id from table
    join metadata WITH c = ?
    where a = ? and b = ?
    limit 1
)

Attached is a patch to fix this issue, along with a patch that fixes all unit tests referring to the old query format.

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

When trying to query a table which contains spaces I get the following exception

I have attached an simple example to reproduce

C:\Documents and Settings\daniel\Dokumenter\NetBeansProjects\test>php doctrineTest.php

Fatal error: Uncaught exception 'Doctrine_Query_Exception' with message 'Unknown table alias with' in C:\Doctrine-1.2.3\Doctrine\Query\Abstract.php:856
Stack trace:
#0 C:\Doctrine-1.2.3\Doctrine\Query.php(1022): Doctrine_Query_Abstract->getComponentAlias('with')
#1 C:\Doctrine-1.2.3\Doctrine\Query.php(1239): Doctrine_Query->_buildSqlFromPart()
#2 C:\Doctrine-1.2.3\Doctrine\Query.php(1133): Doctrine_Query->buildSqlQuery(true)
#3 C:\Doctrine-1.2.3\Doctrine\Query\Abstract.php(958): Doctrine_Query->getSqlQuery(Array)
#4 C:\Doctrine-1.2.3\Doctrine\Query\Abstract.php(1026): Doctrine_Query_Abstract->_execute(Array)
#5 C:\Documents and Settings\daniel\Dokumenter\NetBeansProjects\test\doctrineTest.php(18): Doctrine_Query_Abstract->execute()
#6

thrown in C:\Doctrine-1.2.3\Doctrine\Query\Abstract.php on line 856

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.

After upgrading to Doctrine 1.2.2 queries with named arguments doesn't work anymore.
Whenever querying like
$query = Doctrine_Query::create();
$customer = $query->from('Customers')
->where("id = :id"
, array('id'=>$user['customer_id']))
->fetchOne(array(), Doctrine::HYDRATE_ARRAY);

I get the error
SQLSTATE[HY000]: General error: 10007 Incorrect syntax near ':'. [10007] (severity 5)

If changing the query to
$query = Doctrine_Query::create();
$customer = $query->from('Customers')
->where("id = ?"
, array($user['customer_id']))
->fetchOne(array(), Doctrine::HYDRATE_ARRAY);
it works fine.
Testing with MySQL works fine, so it seems contained to MSSQL

Looking at the attachment to see the little schema.
Tree table : items, items_children and children.

The link-table items_children is here to create a many-to-many relationships between Items and Children.

So, after configure the YAML and generated Models.
We can do something like that :

$query = Doctrine_Query::create ()
->select ("items.title, children.title")
->from ("Item items")
->leftJoin ("item.children children");
$items = $query->execute ();

$items //An array of items
$items[0]->title //A string
$items[0]->children //An array of children

The SQL Output of the query is :

SELECT `i`.`id_item` AS `i_id_item`, `i`.`title` AS `ititle`, `c`.`id_child` AS `cid_child`, `c`.`title` AS `c_title` FROM `items` `i` LEFT JOIN `items_children` `i2` ON (`i`.`id_item` = `i2`.`id_item`) LEFT JOIN `children` `c` ON `i2`.`id_child` = `c`.`id_child`

All array are order by something, in this case it's a natural order which come from the items_children order into database.

Now, I want to apply a condition, or a filter on the items_children table.
If I translate my demand to SQL Query :

SELECT `i`.`id_item` AS `i_id_item`, `i`.`title` AS `ititle`, `c`.`id_child` AS `cid_child`, `c`.`title` AS `c_title` FROM `items` `i` LEFT JOIN `items_children` `i2` ON (`i`.`id_item` = `i2`.`id_item`) LEFT JOIN `children` `c` ON `i2`.`id_child` = `c`.`id_child` ORDER BY `c1`.`index`

Or something crazy :

SELECT `i`.`id_item` AS `i_id_item`, `i`.`title` AS `ititle`, `c`.`id_child` AS `cid_child`, `c`.`title` AS `c_title` FROM `items` `i` LEFT JOIN `items_children` `i2` ON (`i`.`id_item` = `i2`.`id_item`) LEFT JOIN `children` `c` ON `i2`.`id_child` = `c`.`id_child` WHERE `c1`.`index` > 2

But how can I do that with DQL ? Because I have no access to the Link-table.

Link-Table are use to create many-to-many, that could be OOP array. So we have to be allowed to add "index" to order the result. Also, we have to be allowed to add condition on this tables, like "datePromote" or simply "enabled".

The solution should be on the following DQL Query :

$query = Doctrine_Query::create ()
->select ("items.title, children.title")
->from ("Item items")
->leftJoin ("item.children children")
->leftJoin ("ItemsChildren link_table")
->orderBy ("link_table.index")
$items = $query->execute ();

But the SQL Output is :

SELECT `i`.`id_item` AS `i_id_item`, `i`.`title` AS `ititle`, `c`.`id_child` AS `cid_child`, `c`.`title` AS `c_title` FROM `items` `i` LEFT JOIN `items_children` `i2` ON (`i`.`id_item` = `i2`.`id_item`) LEFT JOIN `children` `c` ON `i2`.`id_child` = `c`.`id_child`, `items_children` `i3` ORDER BY `i3`.`index`

To conclude, it's a blocker problem. Because I can't use a standard DB schema.

Regards,
Armetiz.

For models using the 'package' attribute in the schema definition, the version classes do not get created. However, the version table gets created.

There is no problem during the build, but when loading fixtures, there is a fatal error: Class TaxCodeVersion not found

TaxCode:
package: Masters
tableName: Fin_Tax_Codes
actAs:
Activateable: ~
SoftDelete: ~

1. Versionable:
2. tableName: fin_tax_codes_version
   1. versionColumn: version
3. className: %CLASS%Version
   1. auditLog: true
      Auditable: ~
      Timestampable: ~
      .......

Doctrine result cache isn't working properly.

Here a simple example, when I'm calling query->execute (); parentProgram is related to a video.
Into the Doctrine_Query_Abstract.php file

line 1014 : I have just add some code to output data.
[code]
if ($cached === false)

else

[/code]

The output of a query execution (the first with an empty APC cache) with "useResultCache" is :

• not cached int(1)
• cached int(0)

The problem is coming from the serialize php function that can't serialize protected properties..
getReferences is a getter to a protected property.. So data result can't work properly.

A solution could be use __sleep function, and a public property that contain all important protected data.

Actually, I've a dead simple schema.yml, with two tables:

T_Media:
actAs:
Timestampable: ~
columns:
media_id:

name: string(25)

J_Acl:
columns:
media_id: integer
relations:
Media:

I have some fixtures:
T_Media:
m1:
name: foobar

J_Acl:
a1:
Media: m1

And then, the DQL query I want to execute:

"From T_Media m INNER JOIN m.J_Acl order by m.created_at limit 1"

But if I run this query, for instance in CLI, I got an error:
./symfony doctrine:dql "From T_Media m INNER JOIN m.J_Acl order by m.created_at limit 1"

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'test_noel.t2__media' doesn't exist.
Failing Query: "SELECT DISTINCT t2.media_id FROM t2_media t2 INNER JOIN j2_acl j2 ON t2.media_id = j2.media_id ORDER BY t2.created_at LIMIT 1"

Notes:

• the query works if I do not apply the limit clause.
• if I remove the underscore from the model, or if I set manually the tableName to remove the double underscore, it works

I'm trying to use sfDoctrineMasterSlavePlugin for database replication with Symfony 1.4 and PHP 5.3. But facing problem while selecting I18n records and receiving "Unknown relation alias Translation" error.

I've also tried same with implementation solution given in master-slave chapter of doctrine cookbook but no success.

Is anyone facing same problem with sfDoctrineMasterSlavePlugin and i18n behavio? Is there any solution of the problem?

When objects are hydrated with a join to a one to one relation, if the queried object is stored in the table's cache, and ATTR_HYDRATE_OVERWRITE set to false, then the one to one relation's postHydrate method will never be called.

This is due to this line.

 
} else if ( ! isset($prev[$parent][$relationAlias])) {

...which will always evaluate to false, and postHydrate will ever be called, as the record has been pulled from the table cache here

When I want to do a simple update like this :

$batches = ExamResultsBatchTable::getInstance()->retrieveByExamBatchStatus(ExamResultsBatch::valid_status_code);
foreach($batches as $batch)
{
$batch->setExamBatchStatusId($batchStatusId);
$batch->setStatusDate(date('Y-m-d'));
$batch->save();
}
Only exam_batch_status_id and status_date should be updated (see screenshot before), but columns exam_batch_status_id ,status_date AND exam_subject_id are updated,with the same value (23) (screenshot after_with_save).

If I run this:
$toto = Doctrine_Query::create()
->update('ExamResultsBatch erb')
->set('erb.exam_batch_status_id', 23)
->set('erb.status_date', date('Y-m-d'))
>where('erb.id = ?' , $batch>getId())
->execute();
Everything is correctly done.

here is the simpliest case.

The same problems are signaled on other tables in the database, but different tables can be impacted by one save() (the execute() query still works fine).

Example : 2 foreign tables will be updated , even if the save() action should only concern the main table, and one field (which is not a foreign key).
The corresponding foreign key fields in the 2 foreign tables, will be updated with the value given (here 23).

Because save() is used in a lot of different places in our app, I need to find a solution to fix save(), or if not possible to override it to run a execute()like query.

Thanks for your help.
Don't hesitate to ask if you want more details.

Yan

Looking at the latest version of Doctrine_Connection_Mssql in git repo:
https://github.com/doctrine/doctrine1/blob/b4dc8e66a89a7e17cd195c489b18005e19ca9ea5/lib/Doctrine/Connection/Mssql.php

In Doctrine_Query:getLimitSubquery() there is a call to Doctrine_Connection_Mssql::modifyLimitSubquery().

public function modifyLimitSubquery(Doctrine_Table $rootTable, $query, $limit = false, $offset = false, $isManip = false)
{
	return $this->modifyLimitQuery($query, $limit, $offset, $isManip, true);
}

This in turn calls Doctrine_Connection_Mssql::modifyLimitQuery() wihtout passing the $queryOrigin parameter:

    public function modifyLimitQuery($query, $limit = false, $offset = false, $isManip = false, $isSubQuery = false, Doctrine_Query $queryOrigin = null)
    {
        if ($limit === false || !($limit > 0)) {
            return $query;
        }

        $orderby = stristr($query, 'ORDER BY');

        if ($offset !== false && $orderby === false) {
            throw new Doctrine_Connection_Exception("OFFSET cannot be used in MSSQL without ORDER BY due to emulation reasons.");
        }
        
        $count = intval($limit);
        $offset = intval($offset);

        if ($offset < 0) {
            throw new Doctrine_Connection_Exception("LIMIT argument offset=$offset is not valid");
        }

        $orderbySql = $queryOrigin->getSqlQueryPart('orderby');
        $orderbyDql = $queryOrigin->getDqlPart('orderby');

        if ($orderby !== false) {
            $orders = $this->parseOrderBy(implode(', ', $queryOrigin->getDqlPart('orderby')));

            for ($i = 0; $i < count($orders); $i++) {
...

From just looking at the above code, the query chokes on the first call to a $queryOrigin method. It seems like there is a lot of missing code here which should work with the $query directly when $queryOrigin is not available...

What is the point of $orderbySql and $orderbyDql variables when they are not used anywhere?

This code looks like it's half way done and untested.

Doctrine Cache store data into a persistence storage.

Regarding APC, Doctrine use a share storage.
Doctrine is able to cache SQL from DQL, and DOM from SQL.
To do this, Doctrine create a DQL hash, and store the SQL result refer to the hash.

I'm using a server to host two Doctrine project, a preproduction & production Website. In some case, DQL is the same on both project, but the data model definition isn't.

Preproduction convert DQL to SQL using data model definition, and store the SQL result into APC cache refer to the DQL hash.
Production create a DQL hash, this is the same hash as preproduction.. So production instance use the SQL refer to the preproduction..

I'm not sure about the quality of this explanation... But I can add some information is needed.

The solution of this problem is easy. Just add a SALT to any cache id's. It's a Doctrine_Cache problem, not only a Doctrine_Cache_APC problem..
For Query cache it's not really important because this cache is optional, but for result cache.. It's more critical.
The SALT can be define when instantiate the Doctrine_Cache object, it's just an option..

$cacheDriver = new doctrine_Cache_Apc ();
$cacheDriver->setOption ("salt", "domain.tld");

Hallo, i get the error
Warning: Invalid argument supplied for foreach() in /var/www/phverbose/vendor/doctrine/Doctrine/ORM/Query/SqlWalker.php line 899

Here is the line
foreach ($assoc['relationToTargetKeyColumns'] as $relationColumn => $targetColumn) {

Here are the relations and the query
http://pastie.org/4352511
http://pastie.org/4352498

Here is the dump of $assoc before warning

array(16) {
["fieldName"]=>
string(5) "sites"
["joinTable"]=>
array(0) {
}
["targetEntity"]=>
string(13) "Entities\Site"
["mappedBy"]=>
string(6) "emails"
["inversedBy"]=>
NULL
["cascade"]=>
array(1)

["orphanRemoval"]=>
bool(false)
["fetch"]=>
int(2)
["type"]=>
int(8)
["isOwningSide"]=>
bool(false)
["sourceEntity"]=>
string(14) "Entities\Email"
["isCascadeRemove"]=>
bool(false)
["isCascadePersist"]=>
bool(true)
["isCascadeRefresh"]=>
bool(false)
["isCascadeMerge"]=>
bool(false)
["isCascadeDetach"]=>
bool(false)
}

Actual in case of using multi database configuration and foreign keys between them.

Now build-sql task generates SQL query for database by database in alphabetical order. It's ugly when we have multidatabase configuration and foreign keys between their tables. It's impossible to do 'import-sql' without errors beucase foreign keys constrains to nonexisting tables are in next database in order.

I have added some code to strings 1176-...

Consider the following schema:

MyTable:
  columns:
    some_text:        string

Doctrine creates the table with:

CREATE TABLE `my_table` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `some_text` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Now, the following migration should rename the field from some_text to just_text:

<?php
class Version1 extends Doctrine_Migration_Base
{
    public function up()
    {
        $this->renameColumn('my_table', 'some_text', 'just_text');
    }

    public function down()
    {
      $this->renameColumn('my_table', 'just_text', 'some_text');
    }
}

...however the field gets renamed and the type becomes VARCHAR(255), as the resulting SHOW CREATE TABLE my_table shows:

CREATE TABLE `my_table` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `a_varchar` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Causes data in the column greater than 255 bytes to get truncated

$strSql = UserFeedTable::getInstance()>createQuery('q')>
select('q., f., fi., fav.')->
leftJoin('q.Feed f')->
leftJoin('f.FeedItem fi')->
leftJoin('fi.Favorite fav')->
andWhere('q.profile_id = ?', $intUserId)->
andWhere('q.is_active = ?', true)->
limit(10)->getSqlQuery();
var_dump($strSql);

string(1075) "SELECT u.id AS u_id, u.name AS uname, u.image AS uimage, u.lead AS ulead, u.headline AS uheadline, u.sort AS usort, u.is_active AS uis_active, u.is_favorite AS uis_favorite, u.feed_id AS ufeed_id, u.profile_id AS uprofile_id, u.category_id AS ucategory_id, u.created_at AS ucreated_at, u.updated_at AS uupdated_at, f.id AS fid, f.url AS furl, f.name AS fname, f.created_at AS fcreated_at, f.updated_at AS fupdated_at, f2.id AS f2id, f2.lead AS f2lead, f2.description AS f2description, f2.image AS f2image, f2.pub_date AS f2pub_date, f2.link AS f2link, f2.feed_id AS f2feed_id, f2.created_at AS f2created_at, f2.updated_at AS f2updated_at, f3.id AS f3id, f3.profile_id AS f3profile_id, f3.feed_item_id AS f3feed_item_id, f3.created_at AS f3created_at, f3.updated_at AS f3_updated_at FROM user_feed u LEFT JOIN feed f ON u.feed_id = f.id LEFT JOIN feed_item f2 ON f.id = f2.feed_id LEFT JOIN favorite f3 ON f2.id = f3.feed_item_id WHERE u.id IN ('7', '8', '9', '10', '11') AND (u.profile_id = ? AND u.is_active = ?)"

As you can see, the limit is missing.

Just downloaded symfony 1.4

First of all I have a query:

$q = \Doctrine_Query::create()
->select('u., ur.')
->from('UserDb u')
->leftJoin('u.RealUserDetailsDb ur')
->leftJoin('u.MockUserDetailsDb um')
->where('u.id = :user_id')
;
$user = $q->fetchOne(array(':user_id' => $uid));

After that I'm accessing the fields of this object:

$userArray = array(
'id' => $this->getUser()->getId(),
'real_user_details_id' => $this->getUser()->getRealUserDetailsId(),
'mock_user_details_id' => $this->getUser()->getMockUserDetailsId(),
'real_user_details' => array(),
'mock_user_details' => array()
);

This is the actual queries into DB:

NR1:
SELECT u.id AS u_id, u.user_real_id AS uuser_real_id, u.user_mock_id AS uuser_mock_id, u2.id AS u2id, u2.nickname AS u2nickname, u2.email AS u2_email FROM user u LEFT JOIN user_real u2 ON u.user_real_id = u2.id LEFT JOIN user_mock u3 ON u.user_mock_id = u3.id WHERE (u.id = :user_id)

NR2:
SELECT u.id AS u_id, u.user_real_id AS uuser_real_id, u.user_mock_id AS u_user_mock_id FROM user u WHERE (u.id = '1') LIMIT 1

As you can see there are TWO queries however there should be only one query. The problem is that u.user_real_id is NULL in database and when I do 'real_user_details_id' => $this->getUser()->getRealUserDetailsId() doctrine does not have enough intelligence to understand that these fields have been already requested in NR1. If I comment this field, everything works well.

SURPRISE!
And now a surprise... if I modify a little bit my first query: ">select('u.')" instead of , ">select('u., ur.*')" it WON'T make TWO queries. It will make ONLY ONE!

As you understand this a very critical bug and of course our system won't go to production with this bug.

P.S. Is it possible to turn off the lazy loading in doctrine?

If the alias in a from-query is equal to the model name, I get an exception when executing the query after adding a "pendingJoinCondition".

See attached testCase.

Trying to migrate from doctrine 1 to 1.2 and this problem came up to me, i cant map a column to integer(7) (which should create a number(7) column) using Oracle, he always create a NUMBER(20) field.

Taking a look at Doctrine_DataDict_Oracle i realize the problem is here.

The Sluggable behavior has the following code:

    /**
     * Set the slug value automatically when a record is inserted
     *
     * @param Doctrine_Event $event
     * @return void
     */
    public function preInsert(Doctrine_Event $event)
    {
        $record = $event->getInvoker();
        $name = $record->getTable()->getFieldName($this->_options['name']);

        if ( ! $record->$name) {
            $record->$name = $this->buildSlugFromFields($record);
        }
    }

However, this can lead to problems...

If the user incorrectly assigns a duplicate slug to the record then there is no uniqueness checking in doctrine and you get an uncaught SQL error looking something like this:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'my-slug-en_GB' for key 'foo_i18n_sluggable_idx'

If this kind of "don't do a preInsert check if I manunally set the slug" behavior is a FEATURE then it would be best to have an option to allow it to be disabled. If it is a BUG then I would suggest that the preInsert method should be changed to:

    /**
     * Set the slug value automatically when a record is inserted
     *
     * @param Doctrine_Event $event
     * @return void
     */
    public function preInsert(Doctrine_Event $event)
    {
        $record = $event->getInvoker();
        $name = $record->getTable()->getFieldName($this->_options['name']);

        if ( ! $record->$name) {
            $record->$name = $this->buildSlugFromFields($record);
        } else { // Still check for slug uniqueness when you insert
            $record->$name = $this->buildSlugFromSlugField($record);
        }
    }

C

My scripts have excessive memory consumption and I've often saw in my logs:

PHP Fatal error: Allowed memory size of 268435456 bytes exhausted (tried to allocate 2097152 bytes) in /proj/lib/vendor/symfony/lib/plugins/sfDoctrinePlugin/lib/vendor/doctrine/Doctrine/Cache/Apc.php on line 111

Looking into the code I've found which function to blame:

protected function _getCacheKeys()
{
$ci = apc_cache_info('user');
$keys = array();

foreach ($ci['cache_list'] as $entry)

Unknown macro: { $keys[] = $entry['info']; ######### THIS IS THE LINE }

return $keys;
}

My server extensively uses APC caching and it's normal to have many cache keys.
Obviously retrieving ALL of them is time and memory consuming.
As I'm not well versed with Doctrine's code, I didn't want to dive further in.

Is there another way to avoid this pitfall?

I have a Doctrine model which connects to a MSSQL database. I was trying to run the following query:

$q = Doctrine_Query::create()
    ->select('*')
    ->from('Comment c')
    ->innerJoin('c.RecordType')
    ->innerJoin('c.Department')
    ->limit(10)
    ->orderBy('c.Counter');

The code failed with a SQL Syntax exception so I took a look at the generated query and found the following (SELECT fields shortened for readabilty):

SELECT * FROM (
	SELECT TOP 10 * FROM (
		SELECT TOP 10 [c].[counter] AS [c__counter], [c].[loanid] AS [c__loanid]... ... ...
		FROM comments c
			INNER JOIN [SystemTypes] [s] ON [c].[recordtype] = [s].[code] AND [s].[fieldname] = 'RecordType'
			INNER JOIN [SystemTypes] [s2] ON [c].[department] = [s2].[code] AND [s2].[fieldname] = 'Department'
		ORDER BY [c].[counter]
	) AS [inner_tbl]
	ORDER BY [inner_tbl].[counter] AS [c__counter] DESC
) AS [outer_tbl]
ORDER BY [outer_tbl].[counter] AS [c__counter] ASC

As you can see, the ORDER BY clauses on the inner_tbl and outer_tbl segments have AS clauses which do not belong there. If you fix the ORDER BY statements the query runs just fine.

So I decided to prod around the Mssql.php connection class and found the following:

140 public function modifyLimitQuery($query, $limit = false, $offset = false, $isManip = false, $isSubQuery = false)
141 {
...
169                    $field_array = explode(',', $fields_string);
170                    $field_array = array_shift($field_array);
171                    $aux2 = preg_split('/ as /', $field_array);
172                    $aux2 = explode('.', end($aux2));
173
174                    $aliases[$i] = trim(end($aux2));
...
232 }

Line 171 seems to be in charge of setting up the orderBy aliases but it is looking for a lower case ' as ' string which doesn't exist in this SQL expression. Changing that to a case insensitive regular expression search seems to fix the problem:

171                    $aux2 = preg_split('/ as /i', $field_array);

Here is the resulting SQL with the change:

SELECT * FROM (
	SELECT TOP 10 * FROM (
		SELECT TOP 10 [c].[counter] AS [c__counter], [c].[loanid] AS [c__loanid]... ... ...
		FROM comments c
			INNER JOIN [SystemTypes] [s] ON [c].[recordtype] = [s].[code] AND [s].[fieldname] = 'RecordType'
			INNER JOIN [SystemTypes] [s2] ON [c].[department] = [s2].[code] AND [s2].[fieldname] = 'Department'
		ORDER BY [c].[counter]
	) AS [inner_tbl]
	ORDER BY [inner_tbl].[c__counter] DESC
) AS [outer_tbl]
ORDER BY [outer_tbl].[c__counter] ASC]

This seems to fix the problem but I don't know if it'll create a regression. It's a start though. Anyone have any thoughts on this?

DB schema:

Category:
actAs:
NestedSet:
hasManyRoots: true
rootColumnName: root_id
columns:
id:
type: integer(4)
primary: true
autoincrement: true
name:
type: string(64)
notnull: true
image: string(64)
indexes:
tree:
fields: [lft, rgt, root_id]

Sample data:

id: '1'
name: 'Przykładowa kategoria 1'
image: null
root_id: '1'
lft: '1'
rgt: '6'
level: '0'

• id: '2'
  name: 'Przykładowa kategoria 2'
  image: null
  root_id: '2'
  lft: '1'
  rgt: '6'
  level: '0'
  -
  id: '3'
  name: 'Przykładowa podkategoria 1'
  image: null
  root_id: '2'
  lft: '2'
  rgt: '5'
  level: '1'
  -
  id: '4'
  name: 'Przykładowa podkategoria 2'
  image: null
  root_id: '2'
  lft: '3'
  rgt: '4'
  level: '2'
  -
  id: '5'
  name: teset1
  image: null
  root_id: '1'
  lft: '2'
  rgt: '5'
  level: '1'
  -
  id: '6'
  name: test2
  image: null
  root_id: '1'
  lft: '3'
  rgt: '4'
  level: '2'

When using HYDRATE_RECORD_HIERARCHY, the first top-level category is empty. Everything is assigned to the other one. Only single-root trees work properly.

So lets see the table:

User:
tableName: users
columns:
id:
type: integer(1)
fixed: false
unsigned: true
primary: true
autoincrement: true
username:
type: string(32)
fixed: false
unsigned: false
primary: false
notnull: true
autoincrement: false
password:
type: string(48)
fixed: false
unsigned: false
primary: false
notnull: true
autoincrement: false
role:
type: enum
fixed: false
unsigned: false
values:

• admin
• support
  default: support <----- Unable to render embedded object: File (ROOT OF EVIL) not found.
  primary: false
  notnull: true
  autoincrement: false

So lets say we have a user with `role` = 'support' and want to set em
'admin' we wrote

$user = new App_Model_User();
$user->assignIdentifier(1);
$user->role = 'admin';
$user->save();
var_dump($user->toArray());

in debugger we see SQL query been made:

1. [0.55 ms] UPDATE users SET role = ? WHERE id = ?

• bindings: admin
• 1

array(6)

Superb! Works as it should! So lets now set role of this user back to
'support':

$user = new App_Model_User();
$user->assignIdentifier(1);
$user->role = 'support'; // This value defined as default in scheme,
thats why have problems
$user->save();
var_dump($user->toArray());

in debugger we didnot see any UPDATE queries! However object is been
changed, results just has not been flushed to database.

array(6)

I cant overcome this problem right now, unfortunatelly (well I can
just remove all default values from table definitions or use
Doctrine_Query for updating staff.. but I'd like to use models

However if I use Doctrine_Query of even

$user = Doctrine_Core::getTable('App_Model_User')->find(1);

instead of

$user = new App_Model_User();
$user->assignIdentifier(1);

updates works well...

It works except subqueries. Main table's order is added to subquery and vice versa. SQL query looks like this:

{{
SELECT t.id AS t_id FROM ticket_d_c9999_record t WHERE (t.id = (SELECT MAX(t2.id) AS t20 FROM ticket_d_c9999__record t2 ORDER BY t.id ASC, t2.id ASC)) ORDER BY t.id ASC, t2.id ASC
}}

Query fails because there is >>no such column "t.id"<<.

I include a testcase when it is all visible.

BTW. there is nothing about that feature (and relation orderBy) in docs. It is only in UPGRADE file.

i have query

$q = Doctrine_Query::create()
->select('g.,gp.,st.,np.,v.,s.,max(gp.card_date) as md')
->from('gragdans as g')
->innerJoin('g.Pribs_ gp')
->leftJoin('gp.Streets_ st')
->leftJoin('gp.Viddocs_ v')
->leftJoin('gp.Sobits_ s')
->leftJoin('st.Npunkts_ np')
->where('g.grid in '.$idlst,1)
->orderby('fam')
->having('gp.card_date=md');

When it runs i have error:

<b>Fatal error</b>: Uncaught exception
'Doctrine_Connection_Mysql_Exception' with message 'SQLSTATE[42S22]:
Column not found: 1054 Unknown column 'md' in 'having clause'' in Z:
\home\new\www\system\application\plugins\doctrine\lib\Doctrine
\Connection.php:1082

SQL for it looks like:

SELECT g.grid AS g_grid, g.fam AS gfam, g.nam AS g_nam, g.otc AS
g_otc, g.date_rogd AS gdate_rogd, g.gosgrid AS g_gosgrid,
g.rogd_place AS g_rogd_place, g.pol AS g_pol, g.reg_date AS
g_reg_date, g.deesp AS gdeesp, p.pribid AS p_pribid, p.grid AS
p_grid, p.strid AS pstrid, p.hom AS phom, p.cor AS p_cor, p.kva
AS p_kva, p.reg_date AS preg_date, p.vidid AS p_vidid, p.pas_ser
AS p_pas_ser, p.pas_no AS ppas_no, p.org_name AS p_org_name,
p.pas_date AS p_pas_date, p.sobid AS p_sobid, p.reg_expire AS
p_reg_expire, p.card_date AS pcard_date, s.strid AS s_strid,
s.npid AS s_npid, s.name AS sname, v.vidid AS v_vidid, v.name AS
v_name, s2.sobid AS s2sobid, s2.cod_s AS s2_cod_s, s2.cod_oi AS
s2_cod_oi, s2.name AS s2name, n.npid AS nnpid, n.name AS n_name,
n.sid AS n_sid, MAX(p.card_date) AS p_0 FROM gragdans g INNER JOIN
prib p ON g.grid = p.grid LEFT JOIN streets s ON p.strid = s.strid
LEFT JOIN viddoc v ON p.vidid = v.vidid LEFT JOIN sobit s2 ON p.sobid
= s2.sobid LEFT JOIN npunkt n ON s.npid = n.npid WHERE (g.grid in (4,
13, 19, 20)) HAVING p.card_date=md ORDER BY g.fam

But i need Query looks like:

SELECT g.grid AS g_grid, g.fam AS gfam, g.nam AS g_nam, g.otc AS
g_otc, g.date_rogd AS gdate_rogd, g.gosgrid AS g_gosgrid,
g.rogd_place AS g_rogd_place, g.pol AS g_pol, g.reg_date AS
g_reg_date, g.deesp AS gdeesp, p.pribid AS p_pribid, p.grid AS
p_grid, p.strid AS pstrid, p.hom AS phom, p.cor AS p_cor, p.kva
AS p_kva, p.reg_date AS preg_date, p.vidid AS p_vidid, p.pas_ser
AS p_pas_ser, p.pas_no AS ppas_no, p.org_name AS p_org_name,
p.pas_date AS p_pas_date, p.sobid AS p_sobid, p.reg_expire AS
p_reg_expire, p.card_date AS pcard_date, s.strid AS s_strid,
s.npid AS s_npid, s.name AS sname, v.vidid AS v_vidid, v.name AS
v_name, s2.sobid AS s2sobid, s2.cod_s AS s2_cod_s, s2.cod_oi AS
s2_cod_oi, s2.name AS s2name, n.npid AS nnpid, n.name AS n_name,
n.sid AS n__sid, MAX(p.card_date) AS md FROM gragdans g INNER JOIN
prib p ON g.grid = p.grid LEFT JOIN streets s ON p.strid = s.strid
LEFT JOIN viddoc v ON p.vidid = v.vidid LEFT JOIN sobit s2 ON p.sobid
= s2.sobid LEFT JOIN npunkt n ON s.npid = n.npid WHERE (g.grid in (4,
13, 19, 20)) group by fam HAVING p.card_date=md

This query run fine and give me what i need.

Doctrine dont use 'md' alias instead it convert it to 'p__0'

Replicating the bug:
1. Set ATTR_MODEL_CLASS_PREFIX non-null
2. create schema file with entity
3. run doctrine build-all
4. copy schema file
5. edit schema file to add column to entity
6. run generate-migrations-diff from copy of schema file to edited schema file

Expected (previous) behaviour:
Migration is generated to add the new column to entity

Real behaviour:
Drops entity from database and creates new

I moved our project from doctrine 1.2.1 to 1.2.4. The build process stops because of this patch. We are using primary keys with an alias. It seems that the generation of the sequence name in the build-process is different to the one used in UnitOfWorks.

Example:

Authority:
columns:
a_id:

name:

This will generate a sequence called "authority_a_id", but it will try no "currval" the sequence "authority_id".

I'll try to provide a UnitTest. The current seems broken.

php -l Ticket/DC521TestCase.php
PHP Parse error: syntax error, unexpected $end, expecting T_FUNCTION in Ticket/DC521TestCase.php on line 143

Parse error: syntax error, unexpected $end, expecting T_FUNCTION in Ticket/DC521TestCase.php on line 143
Errors parsing Ticket/DC521TestCase.php

Hello,

I had a problem when i try to import data with an extended accessors when i try to insert a content with a relation. I discovered this problem in symfony.

For example, here is my table :

 
News:
  tableName: ne_news
  columns:
    id:           { type: integer(4), primary: true, autoincrement: true }
    author_id:    { type: integer(4), notnull: true }
    name:         { type: string(255) }
    description:  { type: text }
  relations:
    author: { class: sfGuardUser, onDelete: NULL, local: author_id, foreign: id, foreignAlias: sfGuardUser }

And the fixture :

 
SfGuardUser:
  sadmin:
    username:       admin
    password:       admin
    is_super_admin: true
  author1:
    username: myname
    
News:
  News1:
    name: Test 1
    description: Description of news 1
    author: author1

I import it with symfony doctrine:data-load and it works.

If i add a news.class.php and extends the autogenerated class it fails.

 
    public function setAuthor($v)
    {
        //__log('extending setter');
        return $this->_set('author', $v);
    }

WhenDoctrine_Data_Import finds the setAuthor function, it wont transform author1 in object so $v will be a string, not an sfGuardUser object.

What do you think? Is a common behavior, how can i extends my accessor?

If you set a setter method for a model which is for a relation the data import no longer works. This seems to be because in the _processRow method it checks if a method exists and then passes the default value rather than checking whether a relation exists first and passing the imported object.

This effectively means you can't overload a setter method and still use the data import.

Imagine a simple case. Contact can have many categories.
Doing thoses calls:

$c = Doctrine::getTable('Contact')->findOneById($id);
$c->get('Categories', false);
$c->save();

Generate the following error

PHP Fatal error:  Call to a member function save() on a non-object in /lib/vendor/symfony/lib/plugins/sfDoctrinePlugin/lib/vendor/doctrine/Doctrine/Connection/UnitOfWork.php on line 443
PHP Stack trace:
PHP   1. {main}() /test/doctrine/get_with_no_load_corrupt_many_to_many_assoc_.php:0
PHP   2. Doctrine_Record->save() /test/doctrine/get_with_no_load_corrupt_many_to_many_assoc_.php:51
PHP   3. Doctrine_Connection_UnitOfWork->saveGraph() /lib/vendor/symfony/lib/plugins/sfDoctrinePlugin/lib/vendor/doctrine/Doctrine/Record.php:1705
PHP   4. Doctrine_Connection_UnitOfWork->saveAssociations() /lib/vendor/symfony/lib/plugins/sfDoctrinePlugin/lib/vendor/doctrine/Doctrine/Connection/UnitOfWork.php:137

When using softdelete behaviour with cascading deletion on a one-to-one relationship, Doctrine will create a 'child' record if it doesn't exist already, during the cascading deletion. Eg:

• Models Foo, Bar, both SoftDelete
• Foo hasOne Bar
• $myFoo->delete()

Result is:

• $myFoo->deleted_at is set correctly as expected
• New Bar record is created & saved in the process (but is not set to deleted)

Is this expected behaviour? I've attached a test case script, tested against export from SVN of Doctrine 1.2.3 that demonstrates this.

With a valid Json file :
PHP Warning: explode() expects parameter 2 to be string, object given in Doctrine/Doctrine/Import/Schema.php on line 381
PHP Catchable fatal error: Object of class stdClass could not be converted to string in Doctrine/Doctrine/Import/Schema.php on line 391

It's due to this line, line, in Doctrine/Parser/Json.php (#65) :
$json = json_decode($contents);

It should be:
$json = json_decode($contents, true);

Because casting the result as array will only affect the top-level element. You must use the second parameter of json_decode() to force every objects (including sub-objects) to be converted to indexed arrays.

Doctrine_Task_BuildAllReload never calls generate models-from-yaml. This does not coincide with the logic of Doctrine_Task_BuildAll and Doctrine_Task_BuildAllLoad.

BuildAllReload suggests that it will be building all (everything) and then reloading the database.
But instead it only rebuilds the database and loads data

public function __construct($dispatcher = null)
    {
        parent::__construct($dispatcher);

        $this->rebuildDb = new Doctrine_Task_RebuildDb($this->dispatcher);
        $this->loadData = new Doctrine_Task_LoadData($this->dispatcher);
        
        $this->requiredArguments = array_merge($this->requiredArguments, $this->rebuildDb->requiredArguments, $this->loadData->requiredArguments);
        $this->optionalArguments = array_merge($this->optionalArguments, $this->rebuildDb->optionalArguments, $this->loadData->optionalArguments);
    }
    
    public function execute()
    {
        $this->rebuildDb->setArguments($this->getArguments());
        $this->rebuildDb->execute();
        
        $this->loadData->setArguments($this->getArguments());
        $this->loadData->execute();
    }

Instead, I think it would be more efficient and understanding to follow the same logic as build-all and build-all-load by calling drop-db and build-all-load.

public function __construct($dispatcher = null)
    {
        parent::__construct($dispatcher);

        $this->dropDb = new Doctrine_Task_DropDb($this->dispatcher);
        
        $this->buildAllLoad = new Doctrine_Task_BuildAllLoad($this->dispatcher);
        
        $this->requiredArguments = array_merge($this->requiredArguments, $this->dropDb->requiredArguments, $this->buildAllLoad->requiredArguments);
        $this->optionalArguments = array_merge($this->optionalArguments, $this->dropDb->optionalArguments, $this->buildAllLoad->optionalArguments);
    }
    
    public function execute()
    {
        $this->dropDb->setArguments($this->getArguments());
        $this->dropDb->execute();
        
        $this->buildAllLoad->setArguments($this->getArguments());
        $this->buildAllLoad->execute();
    }

I attached a patch with the above changes... I got a little lost in the test area for Doctrine_CLI, so that is not included = )

The loadMigrationClassesFromDirectory() method in Doctrine_Migration uses array_diff on get_declared_classes() between including each classes script.

When a new migration class is generated by Doctrine_Core::generateMigrationsFromDiff it's class is loaded, which means loadMigrationClassesFromDirectory silently fails to load the newly generated migration on the same request. This means that scripts that first generate migrations and then apply them must be executed twice - first to generate then to apply.

The following example code is used to check if the database has been modifed, generate migrations between the base version and the latest models, and then migrate the database if needed:

Doctrine_Core::generateYamlFromModels(ROOT_PATH.'tmp/yaml/', ROOT_PATH.'models/');
$result = Doctrine_Core::generateMigrationsFromDiff(ROOT_PATH.'tmp/migrations/', ROOT_PATH.'data/yaml/', ROOT_PATH.'tmp/yaml/');

unlink(ROOT_PATH.'data/yaml/schema.yml');
rename(ROOT_PATH.'tmp/yaml/schema.yml', ROOT_PATH.'data/yaml/schema.yml');

$migration = new Doctrine_Migration(ROOT_PATH.'tmp/migrations');

$currentVersion = $migration->getCurrentVersion();
$latestVersion = $migration->getLatestVersion();
if ($currentVersion < $latestVersion) {
	$migration->migrate();
	$this->app->addMessage("Database migration completed (from version $currentVersion to version $latestVersion)","success");
} else {
	$this->app->addMessage("Database is up to date and doesn't require migration (at version $currentVersion)","success");
}

In the class Doctrine_Export the functions for creating and dropping indexes do not use the same logic to get the name of the index to be created or dropped.
When creating an index $this->conn->quoteIdentifier() is called on the index name.
When dropping an index $this->conn->quoteIdentifier($this->conn->formatter->getIndexName()) is called on the name, which by default adds '_idx' to the index name. Hence, when an index should be dropped in a migration an index with that name is not found because it was created without the '_idx'.

There is a problem in the following code in Doctrine/Query.php (lines 1257-1279) inside the buildSqlQuery() method:

            $subquery = $this->getLimitSubquery();

            // what about composite keys?
            $idColumnName = $table->getColumnName($table->getIdentifier());

            switch (strtolower($this->_conn->getDriverName())) {
                case 'mysql':
[...]
                case 'pgsql':
                    $subqueryAlias = $this->_conn->quoteIdentifier('doctrine_subquery_alias');

                    // pgsql needs special nested LIMIT subquery
                    $subquery = 'SELECT ' . $subqueryAlias . '.' . $this->_conn->quoteIdentifier($idColumnName)
                            . ' FROM (' . $subquery . ') AS ' . $subqueryAlias;

                    break;
            }

The above code is executed when a query consist of DISTINCT and LIMIT clauses. The most common situation is using pager.
The problem is in the subquery variable. The $idColumnName variable often has value "id". In such a situation in case of $subquery consist of some JOINs and some tables have column named "id", we have:

SELECT doctrine_subquery_alias.id FROM ((SELECT DISTINCT d1.id, d2.id FROM ...)) AS doctrine_subquery_alias

It, of course, causes the "ambiguous column name" pgsql exception.

When removing the not null from a column the migration does not change anything in the database. This is due to the following check on line 162 of lib/vendor/symfony/lib/plugins/sfDoctrinePlugin/lib/vendor/doctrine/Doctrine/Export/Pgsql.php
if ( ! empty($field['definition']['notnull']))

So if notnull is not there or set to false or '0' or 0 the code does not enter into that if statement and therefore no changes are done to the not null value of the column.

under certain circumstances, Doctrine will only return one result out of a bunch of results, for example:

$ symfony doctrine:dql "from Tafel t, t.Reservering r where t.restaurant_id=4 select date(t.tijd), count(t.id) tafels, count(r.id) reserveringen group by date(t.tijd)"
>> doctrine executing dql query
DQL: from Tafel t, t.Reservering r where t.restaurant_id=4 select date(t.tijd), count(t.id) tafels, count(r.id) reserveringen group by date(t.tijd)
found 2 results
-
date: '2010-11-14'
tafels: '1'
reserveringen: '1'

Expected outcome:
found 2 results
-
date: '2010-11-14'
tafels: '1'
reserveringen: '1'
-
date: '2010-11-16'
tafels: '1'
reserveringen: '0'

The query works fine without the left join:
$ symfony doctrine:dql "from Tafel t where t.restaurant_id=4 select date(t.tijd), count(t.id) tafels group by date(t.tijd)"
>> doctrine executing dql query
DQL: from Tafel t where t.restaurant_id=4 select date(t.tijd), count(t.id) tafels, group by date(t.tijd)
found 2 results
-
date: '2010-11-14'
tafels: '1'
-
date: '2010-11-16'
tafels: '1'

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

I recently discovered that I could greatly optimize some of the queries that were being run through our system by adding a STRAIGHT_JOIN keyword to the front of the select

I added a feature to doctrine which allows me to inject sql into the query in the right place to enable features such as "STRAIGHT_JOIN" but I can't post the patch because my patches are starting to run together – the syntax with in the generated patch would also contain parts of other patches I have posted to jira but have not yet been included in the doctrine svn.

I still wanted to make this post because it will give me a ticket number to base my test cases around.

Will Ferrer

I figured it would be handy to have a WITH ROLLUP be add able to the group by clause.

I added this feature but I can't post the patch because my patches are starting to run together - the syntax with in the generated patch would also contain parts of other patches I have posted to jira but have not yet been included in the doctrine svn.

I still wanted to make this post because it will give me a ticket number to base my test cases around.

Will Ferrer

Hi,

this issue was reported at the symfony project which uses Doctrine 1.2.3:
http://trac.symfony-project.org/ticket/9152
"php symfony doctrine:build-schema failure with PostgreSQL for 1.4.7 and 1.4.8 version"

The SQL Statement 'listTableColumns' fails with an SQL-Error "missing from-clause"
http://trac.doctrine-project.org/browser/tags/1.2.3/lib/Doctrine/Import/Pgsql.php#L96
I can reproduce the error directly in psql or pgadmin. The SQL Statement seems related to DC-697

Even when i turn on the add_missing_from option on the postgres-server it fails with "missing relation".

Now it seems to me, you already fixed this bug in the current 1.2 branch, because the current SQL-Statement is different and it works for me in psql/pgadmin.
http://trac.doctrine-project.org/browser/branches/1.2/lib/Doctrine/Import/Pgsql.php#L96

Could you please close this ticket, if you already fixed this issue, or confirm if it's still an issue?
Attached you find my proposed patch at the symfony project . the current statement in the branch looks too different from my version, so i am not sure to use this patch directly. Tell me if I should work out a proper patch.

SQLSTATE[42P01]: Undefined table: 7 ERROR:  missing FROM-clause entry for table "t"                                               
 	  LINE 6: ...                                                  t.typtype ...                                                       
 	                                                               ^. Failing Query: "SELECT                                           
 	                                                       ordinal_position as attnum,                                                 
 	                                                       column_name as field,                                                       
 	                                                       udt_name as type,                                                           
 	                                                       data_type as complete_type,                                                 
 	                                                       t.typtype AS typtype,                                                       
 	                                                       is_nullable as isnotnull,                                                   
 	                                                       column_default as default,                                                   
 	                                                       (                                                                           
 	                                                         SELECT 't'                                                                 
 	                                                           FROM pg_index, pg_attribute a, pg_class c, pg_type t                     
 	                                                           WHERE c.relname = table_name AND a.attname = column_name                 
 	                                                           AND a.attnum > 0 AND a.attrelid = c.oid AND a.atttypid = t.oid           
 	                                                           AND c.oid = pg_index.indrelid AND a.attnum = ANY (pg_index.indkey)       
 	                                                           AND pg_index.indisprimary = 't'                                         
 	                                                           AND format_type(a.atttypid, a.atttypmod) NOT LIKE 'information_schema%' 
 	                                                       ) as pri,                                                                   
 	                                                       character_maximum_length as length                                           
 	                                                     FROM information_schema.COLUMNS                                               
 	                                                     WHERE table_name = 'matable'                                   
 	                                                     ORDER BY ordinal_position"  

I have problems with different connection
i am using doctrine with symfony, and i work with 2 connections
in base class i have bind to my connection

Doctrine_Manager::getInstance()->bindComponent('Datasource', 'doctrine');

symfony generate me

$this->datasources = Doctrine_Core::getTable('datasource')
->createQuery('a')
->execute();

and when i execute it show me error error that can find this table but it take wrong connection

by test i tried to add bind component as datasource (first is lower character and it works pretty cool)

then i change getTable('datasource') => getTable('Datasource') but it doesn't work
then i have added
test function to my datasource table

public static function test()

and it works.

Query fetchOne method now retrieves and hydrates all collection, which can be time consumable. I suggest to add limit 1 in fetchOne method.

I found some problem when I tried to define custom query for results counting. Defined custom COUNT query is totally ignored and executed default one. I will give you full description of problem bellow.

We have following source code:

 
$q_select = Doctrine_Query::create ()
->select ( 'DISTINCT p.product_name AS product_name' )
->from ( 'Product p' )
->where( 'p.product_name LIKE ?', '%motorola%');
				
$q_count = Doctrine_Query::create ()
->select ( 'COUNT (DISTINCT p.product_name) num_results' )
->from ( 'Product p' )
->where( 'p.product_name LIKE ?', '%motorola%');
												
$pager = new Doctrine_Pager( $q_select, 1, 25 );										
$pager->setCountQuery($q_count);

Let's check custom query before calling $pager->execute() method:

 
echo $pager->getCountQuery(); 

Output:

 
SELECT COUNT (DISTINCT p.product_name) num_results FROM Product p WHERE p.product_name LIKE ?

Looks like until now is everything is correct. Let's call $pager->execute() method:

 
$products = $pager->execute(); 

Let's check executed queries using Symfony SQL queries log panel:

SELECT COUNT(*) AS num_results FROM product p WHERE p.product_name LIKE '%motorola%'
7.27s, "doctrine" connection

SELECT DISTINCT p.product_name AS p__0 FROM product p WHERE (p.product_name LIKE '%motorola%') LIMIT 25
3.25s, "doctrine" connection

Executed COUNT query is not same we set using $pager->setCountQuery($q_count). Our defined custom COUNT query is totally ignored and executed default one:

INSTEAD OF THIS CUSTOM COUNT QUERY:

SELECT COUNT (DISTINCT p.product_name) num_results FROM Product p WHERE p.product_name LIKE '%motorola%'

EXECUTED DEFAULT COUNT QUERY:

SELECT COUNT(*) AS num_results FROM product p WHERE p.product_name LIKE '%motorola%'

I am using PHP 5.3.2 and PostgreSQL 8.4.5, Diem passed all checks in green - OK.

I started with "A week of Diem Ipsum" and all went ok until I reached building of blog engine. Blog engine example fails in step:

php symfony doctrine:migrate

with error message:

The following errors occurred:

    * SQLSTATE[42830]: Invalid foreign key: 7 ERROR: there is no unique constraint matching given keys for referenced table "article_translation". Failing Query: "ALTER TABLE article_translation_version ADD CONSTRAINT article_translation_version_id_article_translation_id FOREIGN KEY (id) REFERENCES article_translation(id) ON UPDATE CASCADE ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE"
    * SQLSTATE[25P02]: In failed sql transaction: 7 ERROR: current transaction is aborted, commands ignored until end of transaction block. Failing Query: "CREATE INDEX article_image ON article (image)"
    * SQLSTATE[25P02]: In failed sql transaction: 7 ERROR: current transaction is aborted, commands ignored until end of transaction block. Failing Query: "CREATE INDEX article_author ON article (author)"
    * SQLSTATE[25P02]: In failed sql transaction: 7 ERROR: current transaction is aborted, commands ignored until end of transaction block. Failing Query: "CREATE INDEX article_translation_id ON article_translation (id)"
    * SQLSTATE[25P02]: In failed sql transaction: 7 ERROR: current transaction is aborted, commands ignored until end of transaction block. Failing Query: "CREATE INDEX article_translation_version_id ON article_translation_version (id)"

I removed i18n support in blog engine example and after that migrate went ok. But in Admin interface when I wanted to add
blog article, although Diem confirmed it saved article, article would not show up in the list, I checked db table, it was empty also.
Further, if I try to loremize, for 1 fixture I get no error but table is again empty, and for more fixtures then 1, Diem reports error:

SQLSTATE[25P02]: In failed sql transaction: 7 ERROR: current transaction is aborted, commands ignored until end of transaction block.

Again I reviewed the model and removed DmVersionable, migrated again and after that I could loremize or create articles without errors.

Additionally, not related directly to this blog engine example but doctrine related, I noticed errors in Diem Admin interface
itself when I try to access System->Configuration->Settings . If I access System settings over link
admin_dev.php/system/configuration/settings/index it shows settings. But when I click on
dmin_dev.php/system/configuration in menu path and after again to settings
admin_dev.php/system/configuration/settings/index, error is generated:

500 | Internal Server Error | Doctrine_Connection_Pgsql_Exception
SQLSTATE[08P01]: <>: 7 ERROR: bind message supplies 1 parameters, but prepared statement "pdo_stmt_00000008" requires 2

Are this bugs corrected?

When subqueries are used in the on part of a join clause the Doctrine_Query_JoinCondition class does not always create the proper sql. For instance when there are 2 subqueries used in a between doctrine tries to parse the statement as 1 subquery rather 2 subqueries with an "and".

I will post my patch that fixes this issue after I make some test cases for it. I also fixed an issue where "(SQL:" syntax was breaking the join as well.

Will

For my project I needed to be able to reassign connections to models when they are autoloaded – this had to be able to happen during a conservative model loading process before the models had been instantiated. My solution was to build in a hook to a "autoloadSetUp" method which can be attached to any model (or class that is the base for a model).

I will post my patch after I make a test case for it.

Will Ferrer

I needed a way to check if a model has been loaded — checking to see if the model was included in the _loadedModelFiles property of core.

I put in a simple function that allows me to test for this.

I will post the patch after building a test case for this ticket.

Will Ferrer

$membre = new Model_TMembre();
$membre->password = new Doctrine_Expression("AES_ENCRYPT(\"".htmlspecialchars($password,ENT_QUOTES)."\",\""._MYSQL_CRYPT."\")");
$membre->save();

Doesn't works id password field is encoded into utf8_general_ci .

Works fine id password field is encoded into latin1 .

If I have a Doctrine object and I try something like $book->getNonexistantThing(), I always get an error like this:
PHP Fatal error: Class 'Doctrine_Record_UnknownPropertyException' not found in /home/jason/projects/mcif/lib/vendor/symfony/lib/plugins/sfDoctrinePlugin/lib/vendor/doctrine/Doctrine/Record/Filter/Standard.php on line 55

It makes it hard to track down the source of the error. Why not have the error include the offending method call?

I've created new symfony 1.4.8 project:

$ ./symfony -V
symfony version 1.4.8 (/home/marcin.dryka/htdocs/leak/lib/vendor/symfony/lib)

$ php -v
PHP 5.3.2-1ubuntu4.5 with Suhosin-Patch (cli) (built: Sep 17 2010 13:41:55)
Copyright (c) 1997-2009 The PHP Group
Zend Engine v2.3.0, Copyright (c) 1998-2010 Zend Technologies
with Xdebug v2.1.0, Copyright (c) 2002-2010, by Derick Rethans

and set the database schema as follows:

$ cat config/doctrine/schema.yml
Example:
columns:
col1: string(255)
col2: string(255)
col3: string(255)
col4: string(255)
col5: string(255)
col6: string(255)

I created a task that contains a Doctrine_query call

(...)
protected function execute($arguments = array(), $options = array())
{
// initialize the database connection
$databaseManager = new sfDatabaseManager($this->configuration);
$connection = $databaseManager->getDatabase($options['connection'])->getConnection();

while(1)
{
$m = xdebug_memory_usage();
$q = Doctrine_Query::create()
->from('Example');
$o = $q->fetchOne();

if (false !== $o))

unset($q, $o);

printf("Delta: %s Value: %s\n",
xdebug_memory_usage()-$m,
xdebug_memory_usage()
);
}
}
(...)

Unfortunately, memory usage is increasing:
./symfony leak
Delta: 3285264 Value: 10651596
Delta: 12944 Value: 10664448
Delta: 12952 Value: 10677308
Delta: 12932 Value: 10690148
Delta: 12932 Value: 10702988
Delta: 12932 Value: 10715828
Delta: 12932 Value: 10728668
Delta: 12932 Value: 10741508
Delta: 12932 Value: 10754348
Delta: 12932 Value: 10767188

Tested with and without data in database - result is the same.