[DC-701] Aggregates functions do not return proper values when using many relationships and limits Created: 24/May/10  Updated: 17/Apr/14

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

Type: Bug Priority: Blocker
Reporter: will ferrer Assignee: Jonathan H. Wage
Resolution: Unresolved Votes: 0
Labels: None
Environment:

XP Xamp


Attachments: Text File DC_701_adds_disbaleLimitSubquery_testcase.patch     Text File DC_701_adds_hydrateArrayShallow_testcase.patch     Text File DC_701_fix_adds_arrayShallow.patch     Text File DC_701_fix_adds_disableLimitSubQuery.patch    

 Description   

Hi All

I have encountered a problem that seems very core to the way that doctrine works – if you apply an aggregate function to a column in a table and then join to another table via a many relationship while also using a limit, like so:

$q = Doctrine_Query::create();
$q->from('Customer Customer'); 
$q->addSelect('COUNT(Customer.id) as COUNT_customer_id');
$q->addSelect('Customer_Order.id as order_id'); 
$q->leftJoin('Customer.Order Customer_Order'); // Many relationship here
$q->limit(20);

It produces this correct DQL:

SELECT COUNT(Customer.id) as COUNT_customer_id, Customer_Order.id as order_id 
FROM Customer Customer 
  LEFT JOIN Customer.Order Customer_Order 
LIMIT 20

However the SQL it produces will not return an accurate count – the count is restricted by the limit:

SELECT COUNT(p.id) AS p__0, p2.id AS p2__1 
FROM product_customers p 
  LEFT JOIN product_orders p2 ON p.id = p2.customer_id 
WHERE p.id IN ('1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12', '13', '14', '15', '16', '17', '18', '19', '20')

This produces a count of 21 instead of what it should be (1000).

The reason for this is because Doctrine's internal functionality does an intermediary query where it gets gets the ids needed from the customer table in order to use them as the IN portion of the constraints on the final query – like so:

SELECT DISTINCT p3.id FROM product_customers p3 LIMIT 20

It may seem strange that I am applying a limit to a query which will aggregate to 1 to row . In the actual queries that alerted me to this problem I am using a group by. The reason I am not reporting this bug with a group by in my example however is that you can not use a group by with a many relationship and a limit in doctrine 1.2.2 as it works currently (see bug: DC-594). However I am personally able to use a limit with a group by and many relationship since I am using a version of the code I patched my self to repair bug DC-594.

Here is my sample schema in case its helpful.

detect_relations: false
package: Example
options:
  type: INNODB
  charset: utf8
Order:
  tableName: orders
  columns:
    order_id:
      type: integer(4)
      primary: true
      notnull: true
    customer_id:
      type: integer(4)
    order_date: timestamp
  relations:
    Customer:
      type: one
      local: customer_id
      foreign: customer_id
  options:
    type: InnoDB
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
  options:
    type: InnoDB

Thanks much.

Will Ferrer

edit: split SQL code to make the discussion readable without huge horizontal scrolling...



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

Is this still a problem? I am not sure if this can be patched easily. The limit subquery algorithm is flawed deeply but also a core part of the current way Doctrine 1 works.

Comment by will ferrer [ 08/Jun/10 ]

Hi Jon

This bug is still an issue for me but I think it may be VERY hard to patch since it seems very core to the way Doctrine 1 works.

The project I am working on at the moment is a visual query builder that is highly reliant on Doctrine. In order to prevent users from making queries that would trigger this bug I am currently giving an alert when ever a query that would trigger this bug is created.

It would be great if this were patchable but if not I should be able to get by.

Does Doctrine 2 fix this problem?

Thanks for the help.

Will Ferrer

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

In Doctrine 2 the limit subquery algorithm does not exist. It is now up to the developer to write the query to handle the scenario instead of Doctrine "trying" to automate it for you. Since the situation where it is needed it is so rare, and each case can be slightly different it is better to let the developer handle it in those cases.

Comment by will ferrer [ 08/Jun/10 ]

Hi Jon

Does that mean that Doctrine 2 can no longer intelligently handle limits with many relationships, and doesn't have the ability to hydrate a return with sub arrays in it for many relationships?

Thanks again for your help.

Will Ferrer

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

That is correct. We do not automatically try and limit the relationships with a "limit subquery" as it causes more problems then it helps.

Comment by will ferrer [ 10/Jun/10 ]

Hi Jon

I was thinking about what you said here – that the "limit subquery" causes more harm than good. It occur to me that I really do like being able to use this method (it comes in very handy very often) but some times it would really help to be able to turn it off (the bug in this thread is a good example such a time). So I figured why not just make an option to turn it off and have the best of both worlds? I looked at the code and found it was really very easy to put in a property which can be set on the query object to enable or disable the use of the limit subquery.

I made the change in my code base and found it very useful for several situations I was dealing with in my own project.

The one thing I couldn't do is make a test case for this new feature – I couldn't find an existing test case that was actually triggering the limit subquery as I understood it to work (I couldn't find a test case that would put an WHERE IN constraint with all the ids gathered in the limit subquery. I tried some test cases that I THOUGHT would activate this feature but it didn't seem that they did).

After adding this feature to my code base I also wanted a new hydrator – one that worked like scalar but would put in array key names that were the same as the ones you would see in HYDRATE_ARRAY. I noticed that HYDRATE_SCALAR already had the ability to do this but it required that a value of false be passed into the $aliasPrefix argument of the _gatherRowData function. I made a custom hydrator I call HYDRATE_ARRAY_SHALLOW that passes in this false value. I then realized this might be handy to add to doctrine so I integrated it into my code base and made a few test cases for it.

There are 2 patches I am now attaching this to thread:

disableLimitSubquery_2010-06-10_Doctrine_1.2_SVN.patch – this patch adds the disableLimitSubquery property to query objects so that users can turn off the use of the subquery for those times when they don't want to use that behavior (fixing the bug in this thread and probably others)

and

disableLimitSubquery_and_HYDRATE_ARRAY_SHALLOW_2010-06-10_Doctrine_1.2_SVN.patch – this is the same as the first patch but also contains the HYDRATE_ARRAY_SHALLOW hydration type I mentioned above (which tends to go well with disableLimitSubquery turned on).

I put these in 2 patches incase you liked 1 feature but disliked the other.

If you like either of this features I would be very happy to see them added to doctrine.

Also if you have any advice on how to improve these features (or info on how to make a good test case for disableLimitSubquery) please let me know.

Hope you are well.

Will Ferrer

Comment by will ferrer [ 10/Jun/10 ]

Reopened because I added a patch that I think in essence fixes the issues.

Comment by Jonathan H. Wage [ 01/Sep/10 ]

Thanks for the issue and patches.

Fixed here http://github.com/doctrine/doctrine1/commit/2ad78e62e360133efc04bf6897bf679c7f3d833b

Comment by will ferrer [ 01/Sep/10 ]

individual patch for this issue with out other features included

Comment by will ferrer [ 01/Nov/10 ]

adding test cases for these features

Comment by will ferrer [ 01/Nov/10 ]

reopened because I posted some test cases to add to doctrine along with the patchs previously posted





[DC-515] HYDRATE_RECORD_HIERARCHY broken with many roots Created: 22/Feb/10  Updated: 09/Jun/10

Status: Reopened
Project: Doctrine 1
Component/s: Nested Set
Affects Version/s: 1.2.0
Fix Version/s: None

Type: Bug Priority: Critical
Reporter: Kamil Rojewski Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 0
Labels: None


 Description   

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.



 Comments   
Comment by Kamil Rojewski [ 17/Mar/10 ]

If you look at Doctrine_Collection::toHierarchy() you'll notice that there is NO reference to root_id, therefore it treats the entire collection as 1 tree (which is false). The bug is 100% repeatable. I've made a fast walkaround ba adding a multi-tree hydrator:

class MultiRootHydrator extends Doctrine_Hydrator_RecordDriver
{
  public function hydrateResultSet($stmt)
  {
    $result = parent::hydrateResultSet($stmt);

    $collection = array();
    foreach ($result as $item)
    {
      if (!isset($collection[$item->root_id]))
        $collection[$item->root_id] = new Doctrine_Collection($result->getTable());

      $collection[$item->root_id]->add($item);
    }

    $result = new Doctrine_Collection($result->getTable());
    foreach ($collection as $tree)
    {
      $tree = $tree->toHierarchy();
      $record = $tree->getFirst();

      $result->add($record, $record->root_id);
    }

    return $result;
  }
}

It should clarify the problem.

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

I think it was intended that you would only convert a single tree to a hierarchy. What would the structure of the returned data be like?

Comment by Kamil Rojewski [ 09/Jun/10 ]

A Doctrine_Collection with root nodes seems to work fine. It allows to traverse the tree for each root.





[DC-1011] wierd behaviour with setTableName - table name doesn't get set Created: 28/Jun/11  Updated: 28/Jun/11

Status: Reopened
Project: Doctrine 1
Component/s: Record
Affects Version/s: 1.2.3
Fix Version/s: None

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

Linux 2.6.35-28-generic #50-Ubuntu SMP Fri Mar 18 19:00:26 UTC 2011 i686 GNU/Linux
PHP 5.3.3-1ubuntu9.5


Attachments: File attribute_set.php    

 Description   

if i create class called Attribute_set that extends Doctrine_Record and setTableName in setUpTableDefinintion like in documentation setting up models section, the table name appears not to be set
getTableName returns "doctrine_record_abstract"

any joins or relations fail.

if i set it in the setUp function the setTableName appear working and returns "attribute_sets" table name. This only happens with this particular class, and relations have no effect.

I attached class example i'm expiriencing problems with, that should help reproducte this issue



 Comments   
Comment by Justinas [ 28/Jun/11 ]

fixed misstype and it appears that it was not the problem, i get the same:

Base table or view not found: 1146 Table 'db.doctrine_record_abstract' doesn't exist

maybe attribute_set is somekind reserved word in doctrine library ?

Comment by Justinas [ 28/Jun/11 ]

the problem appears to come from Doctrine Formatter





[DC-853] I am using symfony 1.4 Created: 01/Sep/10  Updated: 02/Sep/10

Status: Reopened
Project: Doctrine 1
Component/s: None
Affects Version/s: None
Fix Version/s: None

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

Symfony 1.4



 Description   

$q1 = Doctrine_Query::create()
->select('m.questionaire, COUNT(f.id) AS bref')
->from('machine m')
->leftJoin('m.relations r ON r.machine_id=m.id')
->leftJoin('r.ref f ON r.ref_id=f.id AND f.part_number LIKE "B%"')
->groupBy('m.questionaire')
;
$r1 = $q1->execute();
$q1q = $q1->getSqlQuery();

$q2 = Doctrine_Query::create()
->select('m.questionaire, COUNT(f.id) AS nonbref')
->from('machine m')
->leftJoin('m.relations r ON r.machine_id=m.id')
->leftJoin('r.ref f ON r.ref_id=f.id AND f.part_number NOT LIKE "B%"')
->groupBy('m.questionaire')
;
$r2 = $q2->execute();
$q2q = $q2->getSqlQuery();

$this->reports->setQuery(Doctrine_Query::create()
->select('')
->from('(SQL:'.$q1q.'} q1')
->leftJoin('(SQL:'.$q2q.') q2 ON q1.questionaire=q2questionaire')
);
echo $this->reports->getQuery()->getSqlQuery(); die;

This outputs Couldn't find class (SQL

How to use in such a case ?



 Comments   
Comment by Jonathan H. Wage [ 01/Sep/10 ]

What is the SQL: syntax you are using here? That is definitely not something that is "supported"

Comment by Petronel MALUTAN [ 02/Sep/10 ]

Dear Jonathan

The example I've tried is based on the :
http://www.symfony-project.org/cookbook/1_2/en/retrieving_data_with_doctrine
Please find Sub-Queries in this article and see what I mean.

My problem started from a SQL query created in phpmyadmin, I've tried to convert to doctrine. Following is the mysql query which nicely work:

select * from (select m1.questionaire, COUNT(f1.id) AS n1_refs
from machine AS m1
left join relation AS r1 on m1.id=r1.machine_id
left join ref AS f1 on f1.id=r1.ref_id and f1.part_number LIKE 'B%'
group by m1.questionaire) as a1

left join

(select m2.questionaire, COUNT(f2.id) AS n2_refs
from machine AS m2
left join relation AS r2 on m2.id=r2.machine_id
left join ref AS f2 on f2.id=r2.ref_id and f2.part_number not LIKE 'B%'
group by m2.questionaire) as a2
on a1.questionaire=a2.questionaire

and my trying was to create 2 easier DQL queries and than join them:

$this->q = Doctrine_Query::create()
->select('')
;

$this->q1 = $this->q->createSubquery()
->select('m.questionaire, m.aszero, COUNT(f.id) AS bref')
->from('machine m')
->leftJoin('m.relations r ON r.machine_id=m.id')
->leftJoin('r.ref f ON f.id=r.ref_id AND f.part_number LIKE "B%"')
->groupBy('m.questionaire')
;
//$r1 = $q1->fetchArray();
//$this->r1=$this->q1->execute(array(),Doctrine_Core::HYDRATE_RECORD);

$this->q2 = $this->q->createSubquery()
->select('m.questionaire, m.aszero, COUNT(f.id) AS nonbref')
->from('machine m')
->leftJoin('m.relations r ON r.machine_id=m.id')
->leftJoin('r.ref f ON f.id=r.ref_id AND f.part_number NOT LIKE "B%"')
->groupBy('m.questionaire')
;
//$this->r2=$this->q2->execute(array(),Doctrine_Core::HYDRATE_RECORD);

$this->q->from($this->q1->getDql() . ' q1)')
>leftJoin($this>q2->getDql() . ' q2 ON q1.questionaire=q2questionaire');

echo $this->q->getSqlQuery(); die;

This is outputting :

500 | Internal Server Error | Doctrine_Exception
Couldn't find class SELECT

so please tell me is it now more clear and make some sense ?
How to make it work ?

With kind regards

Petronel

Comment by Jonathan H. Wage [ 02/Sep/10 ]

Can you make a test case that I can run on my machine to see the problem?





[DC-676] Validation exception thrown only if internal nesting level == 1 Created: 10/May/10  Updated: 08/Jun/10

Status: Reopened
Project: Doctrine 1
Component/s: Validators
Affects Version/s: 1.2.2
Fix Version/s: None

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

PHP 5.3.1, Mac OS X 10.6


Attachments: File DC676TestCase.php    

 Description   

I wonder why Validation exception is thrown only if internal nesting level of transaction is 1 and the manual nesting level is not considered.

Line 262 - lib/Doctrine/Transaction.php:

if ($this->_internalNestingLevel == 1) {
    $tmp = $this->invalid;
    $this->invalid = array();
    throw new Doctrine_Validator_Exception($tmp);
}

I have a large database with a lot of tables and relationships. At some case the validation exception is not thrown if the validation fails.

If I add $this->_nestingLevel into this condition:

if ($this->_internalNestingLevel == 1 || $this->_nestingLevel == 1) {
    $tmp = $this->invalid;
    $this->invalid = array();
    throw new Doctrine_Validator_Exception($tmp);
}

Then the validation exception is thrown as excepted.

I don't know why you ignore here the nesting level.

It would be great if you could explain me more about these lines.

I ran the unit test with this modification and got the same result.



 Comments   
Comment by Fabian Spillner [ 10/May/10 ]

Additional information: On doctrine 1.1.x the exception is thrown without this modification.

Comment by Fabian Spillner [ 11/May/10 ]

It seems, the problem is the counter of internal nesting level. I output these member variables:

Doctrine 1.1.2

Doctrine_Transaction::commit invalid is not empty - internal nesting level: 1 nesting level: 2

ok 1 - Article cannot be created if empty: Doctrine_Validator_Exception: Validation failed in class Article

  1 field had validation error:

    * 1 validator failed on slug_title (notnull)

Doctrine 1.2.2

Doctrine_Transaction::commit invalid is not empty - internal nesting level: 0 nesting level: 1

not ok 1 - Empty Artcile could be created!
Comment by Fabian Spillner [ 11/May/10 ]

Yeah! I found the reason:

I attached a template (listener) into my model which validates the body and this method call:

// ...
if ($obj->getAuthor()->getAge())  # this creates new empty Author object
{
  // ...
}
// ...

On Doctrine 1.1, the method saveGraph() of class UnitOfWork executes first saveRelatedLocalKeys and then the hooks methods (preSave, etc.) and my code works!

On Doctrine 1.2, the method saveGraph() call *first* the hooks method and then the saveRelatedLocalKeys which save the empty author object and then destroy the internal nesting level
and the validation exception is not thrown.

Workaround for my listener:

// ...
if ($obj->relatedExists("Author") && $obj->getAuthor()->getAge())  # relatedExists() needed to avoid create empty Author object
{
  // ...
}
// ...

What do you think: It's a bug or a feature?

Comment by Fabian Spillner [ 11/May/10 ]

A test case attached to reproduce the bug.

The test passes if you remove the custom save() method of Ticket_DC676_Author class.

The problem: default "foobar" modifies the Author and is modified. Doctrine tries to save it,
but because there is more than two transactions, the validation exception is not thrown for Article object.

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

Something that really helps with determining what we should do is to find the exact changeset that causes the behavior. If we can look at what code was changed, we can then understand better what to do.

Comment by Fabian Spillner [ 20/May/10 ]

This changeset affects the problem:
http://trac.doctrine-project.org/changeset/6126/branches/1.2/lib/Doctrine/Connection/UnitOfWork.php

But I really don't like this logic how the validation exception is handled. I would seperate these things:

The method validate() should throw exception, and the method commit() should commit only if model is valid and nothing more.

1. go recursive into the model and call the validate() method

2. if not valid, exception is thrown

3. elseif ok, commit is called

PS. Sorry for late answer: I had a lot of work ...

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

Do you see something with that commit that could be changed that fixes your issue?

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

Whoops. This change is causing some problems. It causes lots of tests to fail in our test suite. Reverting for now. Can you try your change against our test suite and see if you can determine anything? Thanks, Jon





[DC-661] Subquery Doctrine Couldn't find class Created: 04/May/10  Updated: 08/Jun/10

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

Type: Bug Priority: Major
Reporter: Mauro E. Assignee: Jonathan H. Wage
Resolution: Unresolved Votes: 0
Labels: None


 Description   

I'm trying to create a query like this one :

SELECT nombre, (select count( * ) from alojamiento a left join localidad l on a.localidad_id=l.id where p.id=l.provincia_id and a.activo=true) as total from provincia p

$q = Doctrine_Query::create()
->select('p.nombre')
->addSelect('(select count( * ) from alojamiento a left join
localidad l on a.localidad_id=l.id where p.id=l.provincia_id and
a.activo=true)')
->from('provincia p');

but it fails : error 500, couldn't find class a.

And :
$q = Doctrine_Query::create() ->select('nombre') ->addSelect('(select count( * ) from alojamiento left join localidad on
alojamiento.localidad_id=localidad.id where
provincia.id=localidad.provincia_id and alojamiento.activo=true)') ->from('provincia');

leads to : SQLSTATE[42S22]: Column not found: 1054 Unknown column 'a.localidad_id' in 'on clause'.



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

You are using table names in your query and not model names.

For future reference, the Jira is for reporting bugs/issues. You can ask user questions on the Doctrine mailing lists: http://www.doctrine-project.org/community

Comment by Mauro E. [ 08/Jun/10 ]

My model names are: alojamiento, localidad and provincia I don't use table names.

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

Can you provide a test case because I am not able to reproduce any problems. All of our subquery tests are passing currently and I can't seem to find any problems like you describe.

Comment by Mauro E. [ 08/Jun/10 ]

Hope it is not my stupid fault, I apologize if it's my fault.

My schema.yml is:

Alojamiento:
actAs:
Timestampable:
Sluggable:
unique: true
fields: [nombre]
canUpdate: true
SoftDelete:
columns:
id:
type: integer(4)
primary: true
unique: true
notnull: true
autoincrement: true
localidad_id:
type: integer(4)
notnull: true
nombre:
type: string(255)
notnull: true
activo:
type: boolean
notnull: true
default: false
relations:
Localidad:
foreignType: one

Localidad:
actAs:
Sluggable:
unique: true
fields: [nombre]
canUpdate: true
columns:
id:
type: integer(4)
primary: true
unique: true
notnull: true
autoincrement: true
nombre:
type: string(250)
unique: true
notnull: true
provincia_id:
type: integer(4)
notnull: true
relations:
Provincia:
onDelete: CASCADE
local: provincia_id
foreign: id

Provincia:
actAs:
Sluggable:
unique: true
fields: [nombre]
canUpdate: true
columns:
id:
type: integer(4)
primary: true
unique: true
notnull: true
autoincrement: true
nombre:
type: string(250)
unique: true
notnull: true
relations:
Localidades:
type: many
class: Localidad
local: id
foreign: provincia_id
orderBy: nombre

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

You can read about how to create unit tests here http://www.doctrine-project.org/projects/orm/1.2/docs/manual/unit-testing/en#unit-testing





[DC-509] Oracle don't close cursor Created: 18/Feb/10  Updated: 28/Jul/11

Status: Reopened
Project: Doctrine 1
Component/s: Connection
Affects Version/s: 1.2.1
Fix Version/s: None

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

PHP 5.3.1



 Description   

When you doing a big loop of insertion you have a "too many open cursor".
The problem is in Doctrine_Connection class
At line 1005 you should replace :
$stmt = $this->prepare($query);
by
$stmt = $this->dbh->prepare($query);

and at line 1041 you should replace :
$stmt = $this->prepare($query);
by
$stmt = $this->dbh->prepare($query);

After this correction, you never have the problem "too many open cursor"

Thanks to Ota to point that on google groups.
http://groups.google.com/group/doctrine-user/browse_thread/thread/fe6cd03c8fb18b64/728ec1b4e42b1f0b?lnk=gst&q=doctrine_oracle_adapter#728ec1b4e42b1f0b



 Comments   
Comment by Jonathan H. Wage [ 02/Mar/10 ]

Hmm. This change is invalid because it then doesn't return the proper statement object. What is the real issue we get the too many cursors open error?

Comment by oxman [ 04/Mar/10 ]

Why ?
The both return Doctrine_Adapter_Statement_Interface

Comment by Jonathan H. Wage [ 04/Mar/10 ]

The change makes it so they return PDOStatement, and we need it to return the Doctrine statement wrapper, right?

Comment by oxman [ 04/Mar/10 ]

It seems not.

At line 1005 in Doctrine/Connection.php :
$stmt = $this->dbh->prepare($query);
var_dump(get_class($stmt));
die;

I see :
string(33) "Doctrine_Adapter_Statement_Oracle"

And with :
$stmt = $this->prepare($query);
var_dump(get_class($stmt));
die;

I see :
string(29) "Doctrine_Connection_Statement"

The both implements Doctrine_Adapter_Statement_Interface

Comment by vincent [ 29/Apr/10 ]

I have this problem in Symfony 1.4.3 when I use comand:
symfony doctrine:data-load

I have 3000 - 4000 lines in my fixtures and max open cursor at 300 on my database oracle.

This problem can an issue???

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

I see, well the change still is not right because it bypasses all the logic in Doctrine_Connection::prepare() which is for sure required and can't just be removed. We need to determine the real issue here in order to properly patch it.

Comment by Peter Wooster [ 29/Jun/10 ]

I'm encountering the same ORA-1000 problem running the symfony doctrine:build-schema command. We are still using PDO, but I have asked in the users group if that's the best choice. The problem is that the listTableColumns and listTableRelations methods both leave a cursor open. They both call Connection::fetchAssoc, so they should be reading all the records.

I narrowed it down a little:

This works properly when no parameters are provided:

$sql = "SELECT * FROM all_tab_columns tc WHERE tc.table_name = 'DEAL'";
$result = $connection->fetchAssoc($sql, array());

This doesn't release the cursor when a named parameter is provided

$sql = "SELECT * FROM all_tab_columns tc WHERE tc.table_name = :tn";
$result = $connection->fetchAssoc($sql, array(':tn' => 'DEAL')); // doesn't release cursor

This doesn't release the cursor when a positional parameter is provided

$sql = "SELECT * FROM all_tab_columns tc WHERE tc.table_name = ?";
$result = $connection->fetchAssoc($sql, array('DEAL')); // doesn't release cursor

This works properly when the PDO connection is used directly

$sql = "SELECT * FROM all_tab_columns tc WHERE tc.table_name = :tn";
$dbh = $connection->getDbh();
$stmt = $dbh->prepare($sql);
$stmt->execute(array(':tn' => 'DEAL'));
$result = $stmt->fetchAll();

Comment by Clément Herreman [ 28/Jul/11 ]

As I went through this bug, I looked for a fix. I found one here https://github.com/derflocki/doctrine1/commit/47b926a523f9f6e3b88042ef2939af0646285ea2

Basically it consist of freeing cursors that aren't used by a SELECT query, thus preventing Oracle from throwing an exception on batch insert/delete.





[DC-338] Add setting charset to sandbox Created: 08/Dec/09  Updated: 10/Dec/09

Status: Reopened
Project: Doctrine 1
Component/s: Sandbox
Affects Version/s: None
Fix Version/s: None

Type: Improvement Priority: Major
Reporter: Ladislav Prskavec Assignee: Jonathan H. Wage
Resolution: Unresolved Votes: 0
Labels: None
Environment:

mysql 5, ubuntu 9.04, php 5.2.10



 Description   

I try set charset adding to config.php in sandbox:
$manager = Doctrine_Manager::getInstance();
$manager->openConnection(DSN, 'doctrine');
$manager->setAttribute(Doctrine_Core::ATTR_MODEL_LOADING, Doctrine_Core::MODEL_LOADING_PEAR);
$manager->setCharset('utf8');

and this doesn't work.

Please update sandbox and give the how to right setCharset in this example. Almost used utf8.



 Comments   
Comment by Jonathan H. Wage [ 08/Dec/09 ]

This works properly. I test setCharset('utf8') and SET NAMES 'utf8' query is issued to the database properly. Please include reproducible code/information in your issue.

Comment by Ladislav Prskavec [ 09/Dec/09 ]

Try this example on my github http://github.com/abtris/doctrine-sandbox, screenshots included.

Comment by Ladislav Prskavec [ 10/Dec/09 ]

Try my sources, i now solution with add connection, but will be better support in manager for set encoding by options.





[DC-290] Relations need alias Created: 25/Nov/09  Updated: 09/Jun/10

Status: Reopened
Project: Doctrine 1
Component/s: Relations
Affects Version/s: 1.2.0-RC1
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Christian Jaentsch Assignee: Jonathan H. Wage
Resolution: Unresolved Votes: 0
Labels: None


 Description   

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);


 Comments   
Comment by Jonathan H. Wage [ 30/Nov/09 ]

If no alias is explicitly defined, the alias is automatically set to the model name. So in your code change you changed it from "ModelName" to "ModelName_rel". If you had the following:

SELECT * FROM User

The alias would be "User" and it is the same as if you did:

SELECT * FROM User User
Comment by Christian Jaentsch [ 01/Dec/09 ]

unfortunately this does not solve my problem... i narrowed it down to be a problem with addPendingJoinCondition in such a case.

here is a short scenario of what i want to do...

$modelName = 'Exhibition';
$alias = "exhibitionalias";
$subAlias = 'exhibitionsub';
		
$query = Doctrine_Query::create()
->from($modelName.' '.$alias);
		
$subQuery .= 'SELECT '.$subAlias.'.id FROM '.$modelName.' '.$subAlias.' WHERE '.$subAlias.'.id = '.$alias.'.id';
$query->addPendingJoinCondition($alias, $alias.'.id IN ('.$subQuery.')');
		
$query->execute();

this works so far as the alias is different from the model name... but if i change

$alias = "Exhibition";

i get the exception

exception 'Doctrine_Exception' with message 'Couldn't find class exhibitionsub' in /var/www/erh_eeecore/core/lib/Doctrine/Doctrine/Table.php:299
Stack trace:
#0 /var/www/erh_eeecore/core/lib/Doctrine/Doctrine/Table.php(249): Doctrine_Table->initDefinition()
#1 /var/www/erh_eeecore/core/lib/Doctrine/Doctrine/Connection.php(1126): Doctrine_Table->__construct('exhibitionsub', Object(Doctrine_Connection_Mysql), true)
#2 /var/www/erh_eeecore/core/lib/Doctrine/Doctrine/Query.php(1924): Doctrine_Connection->getTable('exhibitionsub')
#3 /var/www/erh_eeecore/core/lib/Doctrine/Doctrine/Query.php(1722): Doctrine_Query->loadRoot('exhibitionsub', 'exhibitionsub')
#4 /var/www/erh_eeecore/core/lib/Doctrine/Doctrine/Query/Where.php(76): Doctrine_Query->load('exhibitionsub', false)
#5 /var/www/erh_eeecore/core/lib/Doctrine/Doctrine/Query/Condition.php(92): Doctrine_Query_Where->load('exhibitionsub.i...')
#6 /var/www/erh_eeecore/core/lib/Doctrine/Doctrine/Query/Abstract.php(2077): Doctrine_Query_Condition->parse('exhibitionsub.i...')
#7 /var/www/erh_eeecore/core/lib/Doctrine/Doctrine/Query.php(1156): Doctrine_Query_Abstract->_processDqlQueryPart('where', Array)
#8 /var/www/erh_eeecore/core/lib/Doctrine/Doctrine/Query.php(1122): Doctrine_Query->buildSqlQuery(false)
#9 /var/www/erh_eeecore/core/lib/Doctrine/Doctrine/Query/Abstract.php(1137): Doctrine_Query->getSqlQuery(Array, false)
#10 /var/www/erh_eeecore/core/lib/Doctrine/Doctrine/Query/Abstract.php(1106): Doctrine_Query_Abstract->_getDqlCallbackComponents(Array)
#11 /var/www/erh_eeecore/core/lib/Doctrine/Doctrine/Query.php(1143): Doctrine_Query_Abstract->_preQuery()
#12 /var/www/erh_eeecore/core/lib/Doctrine/Doctrine/Query.php(1122): Doctrine_Query->buildSqlQuery(true)
#13 /var/www/erh_eeecore/core/lib/Doctrine/Doctrine/Query/JoinCondition.php(87): Doctrine_Query->getSqlQuery()
#14 /var/www/erh_eeecore/core/lib/Doctrine/Doctrine/Query/Condition.php(92): Doctrine_Query_JoinCondition->load('Exhibition.id I...')
#15 /var/www/erh_eeecore/core/lib/Doctrine/Doctrine/Query.php(1089): Doctrine_Query_Condition->parse('Exhibition.id I...')
#16 /var/www/erh_eeecore/core/lib/Doctrine/Doctrine/Query.php(1013): Doctrine_Query->_processPendingJoinConditions('Exhibition')
#17 /var/www/erh_eeecore/core/lib/Doctrine/Doctrine/Query.php(1228): Doctrine_Query->_buildSqlFromPart()
#18 /var/www/erh_eeecore/core/lib/Doctrine/Doctrine/Query.php(1122): Doctrine_Query->buildSqlQuery(false)
#19 /var/www/erh_eeecore/core/lib/Doctrine/Doctrine/Query/Abstract.php(1137): Doctrine_Query->getSqlQuery(Array, false)
#20 /var/www/erh_eeecore/core/lib/Doctrine/Doctrine/Query/Abstract.php(1106): Doctrine_Query_Abstract->_getDqlCallbackComponents(Array)
#21 /var/www/erh_eeecore/core/lib/Doctrine/Doctrine/Query/Abstract.php(1001): Doctrine_Query_Abstract->_preQuery(Array)
#22 /var/www/erh_eeecore/app/modules/default/controllers/ExhibitionsController.php(75): Doctrine_Query_Abstract->execute()
Comment by Jonathan H. Wage [ 07/Dec/09 ]

Hmm. Christian, your issue doesn't seem at all related to the original issue. It seems like an entirely different problem, no?

Comment by Christian Jaentsch [ 08/Dec/09 ]

Well, I don't know where the reason for the problem is exactly... I just solved my problem by adding an alias to the foreignKey and localKey relations. The actual problem seems to be located in how the query is processed if the alias is equal to the name of the "from"-model. this causes problems while adding pending join conditions.

Comment by Jonathan H. Wage [ 08/Dec/09 ]

Ok, well what you described is totally un-related to this issue. If you have found a bug you can open another ticket with detailed information and a failing test case. Thanks, Jon

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

Can you show a patch with your changes?

Comment by Christian Jaentsch [ 09/Jun/10 ]

My patch is as shown in the description of this issue:

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);




[DC-292] no migrations diff on template change Created: 25/Nov/09  Updated: 01/Mar/10

Status: Reopened
Project: Doctrine 1
Component/s: None
Affects Version/s: 1.2.0-RC1
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Christian Jaentsch Assignee: Jonathan H. Wage
Resolution: Unresolved Votes: 0
Labels: None


 Description   

the generate-migrations-diff does not create migrations if the template changes fields on the model.

For example:

File:
tableName: files
inheritance:
extends: Eeecore_Record_File
actAs:
Fileable:

The Fileable Template does the following... (the "fileFields" option stores a couple of fields to add to the model)

public function setTableDefinition()
{

foreach ($this->_options['fileFields'] as $key => $field)

{ $this->hasColumn($field['name'], $field['type'], $field['length'], $field['options']); }

}

everything works fine on generate-migrations-models (the initial migrations)... but if we change something on the "fileFields" the generate-migrations-diff call doesn't do anything. We have to rebuild all migrations or write a new migration by hand.



 Comments   
Comment by Jonathan H. Wage [ 07/Dec/09 ]

I added coverage for this issue and it is working as expected. I compare two schemas:

Article:
  columns:
    title: string(255)
    body: clob

and I compare it to this one:

Article:
  actAs: [Timestampable]
  columns:
    title: string(255)
    body: clob

And now I do this:

        $migration = new Doctrine_Migration(dirname(__FILE__) . '/DC292/migrations');
        $diff = new Doctrine_Migration_Diff(dirname(__FILE__) . '/DC292/from.yml', dirname(__FILE__) . '/DC292/to.yml', $migration);
        $changes = $diff->generateChanges();
        print_r($changes);

It has this in the array:

    [created_columns] => Array
        (
            [article] => Array
                (
                    [created_at] => Array
                        (
                            [notnull] => 1
                            [type] => timestamp
                            [length] => 25
                        )

                    [updated_at] => Array
                        (
                            [notnull] => 1
                            [type] => timestamp
                            [length] => 25
                        )

                )

        )

Comment by Christian Jaentsch [ 08/Dec/09 ]

The problem does not occur if you compare 2 schemas while one has a certain template and the other not.

In my case the problem occurs when the 2 schemas both already have the same template definition but in one case the template itself has changed (e.g. injects one more field into the database table of a certain model via setTableDefinition).

Comment by Jonathan H. Wage [ 08/Dec/09 ]

When I test that it works as well. Can you show some kind of reproducible test case?

Comment by Jonathan H. Wage [ 01/Mar/10 ]

Maybe you could add a test case for this?





[DC-278] Invalid qubquery generated if using oracle adapter instead of pdo_oci Created: 23/Nov/09  Updated: 09/Jun/10

Status: Reopened
Project: Doctrine 1
Component/s: None
Affects Version/s: None
Fix Version/s: 1.2.0

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

Linux, Oracle 10g



 Description   

Doctrine generates the following invalid subquery if oracle adapter is used (same query is generated corret if pdo_oci is used):

SELECT DISTINCT "s2"."code"
FROM "spend_category" "s2"
INNER JOIN "company_spend_category" "c4" ON ("s2"."code" = "c4"."spendcategory_code")
INNER JOIN "company" "c3" ON "c3"."code" = "c4"."company_code"
WHERE "s2"."code" = '19000000'
AND "c3"."is_deleted" IS NOT NULL
AND "s2"."is_deleted" IS NOT NULL
ORDER BY "c3"."name" desc

The problem is that the order by column is not part of the selected columns.

Sample dql statement to reproduce this error:

$this->pageAllPager = new Doctrine_Pager(
$q = Doctrine_Query::create()
->select('c.code, c.name, c.is_activated, c.is_deleted, sc.code')
->from('SpendCategory sc')
->innerJoin('sc.Companies c')
->where("sc.code = '$spendcategory'")
->addWhere('c.is_deleted IS NOT NULL')
->addWhere('sc.is_deleted IS NOT NULL')
->orderBy($orderBy)
->setHydrationMode(Doctrine::HYDRATE_ARRAY),
$page,
$resultsPerPage);
}



 Comments   
Comment by Jonathan H. Wage [ 23/Nov/09 ]

I believe this is fixed now in the latest Doctrine 1.2. Can you test and confirm?

Comment by Thomas Wahle [ 24/Nov/09 ]

It is not fixed with 1.2 RC1

Same sql wich work fine with pdo_oci generates an error using the oracle adapter

<p>
<b>Message:</b> ORA-01791: Kein mit SELECT ausgewählter Ausdruck : SELECT s.code AS s_code, c.code AS ccode, c.name AS cname, c.is_activated AS cis_activated, c.is_deleted AS c_is_deleted FROM spend_category s INNER JOIN company_spend_category c2 ON (s.code = c2.spendcategory_code) INNER JOIN company c ON c.code = c2.company_code WHERE s.code IN (SELECT a.code FROM ( SELECT DISTINCT s2.code FROM spend_category s2 INNER JOIN company_spend_category c4 ON (s2.code = c4.spendcategory_code) INNER JOIN company c3 ON c3.code = c4.company_code WHERE s2.code = '37000000' AND c3.is_deleted IS NOT NULL AND s2.is_deleted IS NOT NULL ORDER BY c3.name desc ) a WHERE ROWNUM <= 10) AND (s.code = '37000000' AND c.is_deleted IS NOT NULL AND s.is_deleted IS NOT NULL) ORDER BY c.name desc.

Failing Query: "SELECT s.code AS s_code, c.code AS ccode, c.name AS cname, c.is_activated AS cis_activated, c.is_deleted AS c_is_deleted FROM spend_category s INNER JOIN company_spend_category c2 ON (s.code = c2.spendcategory_code) INNER JOIN company c ON c.code = c2.company_code WHERE s.code IN (SELECT a.code FROM ( SELECT DISTINCT s2.code FROM spend_category s2 INNER JOIN company_spend_category c4 ON (s2.code = c4.spendcategory_code) INNER JOIN company c3 ON c3.code = c4.company_code WHERE s2.code = '37000000' AND c3.is_deleted IS NOT NULL AND s2.is_deleted IS NOT NULL ORDER BY c3.name desc ) a WHERE ROWNUM <= 10) AND (s.code = '37000000' AND c.is_deleted IS NOT NULL AND s.is_deleted IS NOT NULL) ORDER BY c.name desc"</p>

Comment by Jonathan H. Wage [ 24/Nov/09 ]

I can't reproduce the issue in a test case. Can you help me with that? When I test what you're describing I get the results that are expected.

Comment by Thomas Wahle [ 25/Nov/09 ]

Hi Jon,

if i undestand the last comment correctly this issue will be fixed in 1.2 RC2.

I have reviewed lots of existing test cases but did not find an example where pdo_oci or oracle adapter is used.

Can you please forward the test case you have created for DC-278 to me? I will use this as a template for any further issues and hopefuly reduce your efforts.

Kind regards
Tom

> Jonathan H. Wage updated DC-278:
> --------------------------------
>
> Fix Version/s: 1.2.0-RC2
> (was: 1.2.0-RC1)

Comment by Thomas Wahle [ 10/Dec/09 ]

I do believe that I have found the reason and it is still an issue with Doctrine 1.2.1

Doctrine generate the following subquery which is processed in Doctrine_Query:: getLimitSubquery().

SELECT b.id FROM
(
SELECT a.*, ROWNUM AS doctrine_rownum FROM
(
SELECT DISTINCT i.id, i.lft
FROM item i
INNER JOIN oum o ON i.oum_id = o.id AND (o.is_deleted IS NOT NULL)
INNER JOIN item_type i2 ON i.type_id = i2.id AND (i2.is_deleted IS NOT NULL)
INNER JOIN item_translation i3 ON i.id = i3.id
LEFT JOIN attachment a ON i.id = a.item_id AND (a.is_deleted = 0)
WHERE i.bundling_id = ?
ORDER BY i.lft
) a
) b
WHERE doctrine_rownum BETWEEN 3 AND 4

This function replaces the table alias names. It looks like that the first occurance of "a" is detected and the inner alias for table attachment "a" is replaced by "a2" - that's fine. But also the outer table alias "a" is replaced by "a2" The result will be

SELECT b.id FROM
(
SELECT a.*, ROWNUM AS doctrine_rownum FROM
(
[...]
LEFT JOIN attachment a2 ON i.id = a2.item_id AND (a2.is_deleted = 0)
WHERE i.bundling_id = ?
ORDER BY i.lft
) a2  !!!!!!
) b

„a" is selected but the alias has been changed to „a2" and this will cause an sql error.

It looks like that this bug will only raise if a table is used in the subquery which starts with an "a"

Comment by Thomas Wahle [ 11/Dec/09 ]

As a workaround i have change "a" to "x" in the Doctrine_Connection_Oracle::_createLimitSubquery()

As far as no table in our projekt starts with character "x" this works fine for me.

By the way: Shouldn't it be $this->quoteIdentifier('a') . '.' instead of just a. ??

original code:
$query = 'SELECT b.'.$column.' FROM ( '.
'SELECT a.*, ROWNUM AS doctrine_rownum FROM ( '
. $query . ' ) ' . $this->quoteIdentifier('a') . ' '.
' ) ' . $this->quoteIdentifier('b') . ' '.
'WHERE doctrine_rownum BETWEEN ' . $min . ' AND ' . $max;

modified code:
$query = 'SELECT b.'.$column.' FROM ( '.
'SELECT x.*, ROWNUM AS doctrine_rownum FROM ( '
. $query . ' ) ' . $this->quoteIdentifier('x') . ' '.
' ) ' . $this->quoteIdentifier('b') . ' '.
'WHERE doctrine_rownum BETWEEN ' . $min . ' AND ' . $max;

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

Can you provide the change that fixes the problem for you as a diff so we can test it?

Comment by Thomas Wahle [ 09/Jun/10 ]

Hi Jon,

long time with no hear from you. Hope you are well!

I am sorry, we did not fix this bug really. As writte above we have just implemented a work around by choosing a different character ("x" instead of "a") for the name of the generated sub query alias. This works fine with our data model (no table starts with "x") but may cause the same error with other data models.

At all: There was too much trouble in this project last year. Due to this we made the decision to go ahead with pdo_oci to finish the project in time.

Kind regards
Thomas

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

I'll leave this open if someone runs across the same problem, a test case showing the issue would help with pin pointing the problem area in the code.





[DC-254] getLimitSubquery Not Including All Order By Parts Created: 18/Nov/09  Updated: 27/Feb/11

Status: Reopened
Project: Doctrine 1
Component/s: Query
Affects Version/s: 1.2.0-BETA3
Fix Version/s: 1.2.0-RC1

Type: Bug Priority: Major
Reporter: Michael Card Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 1
Labels: None


 Description   

The getLimitSubquery function is only including the first orderby field and ignoring all remaining parts. The function also needs to be changed to include mssql and odbc.

The diff's below provide corrections.

Thanks,

Mike

diff -r Doctrine-1.1.2\lib\Doctrine\Query.php Doctrine-1.1.2-Fixes\lib\Doctrine\Query.php
1367c1367
<         if ($driverName == 'pgsql' || $driverName == 'oracle' || $driverName == 'oci') {
---
>         if ($driverName == 'pgsql' || $driverName == 'oracle' || $driverName == 'oci' || $driverName == 'mssql' || $driverName == 'odbc') {
1371,1387c1371,1392
<                 $part_original = trim($e[0]);
<                 $callback = create_function('$e', 'return trim($e, \'[]`"\');');
<                 $part = trim(implode('.', array_map($callback, explode('.', $part_original))));
< 
<                 if (strpos($part, '.') === false) {
<                     continue;
<                 }
< 
<                 // don't add functions
<                 if (strpos($part, '(') !== false) {
<                     continue;
<                 }
< 
<                 // don't add primarykey column (its already in the select clause)
<                 if ($part !== $primaryKey) {
<                     $subquery .= ', ' . $part_original;
<                 }
---
> 
>                 foreach ($e as $f) {
>                     if ($f == 0 || $f % 2 == 0) {
>                         $part_original = trim($f);
>                         $callback = create_function('$e', 'return trim($e, \'[]`"\');');
>                         $part = trim(implode('.', array_map($callback, explode('.', $part_original))));
> 
>                         if (strpos($part, '.') === false) {
>                             continue;
>                         }
> 
>                         // don't add functions
>                         if (strpos($part, '(') !== false) {
>                             continue;
>                         }
> 
>                         // don't add primarykey column (its already in the select clause)
>                         if ($part !== $primaryKey) {
>                             $subquery .= ', ' . $part_original;
>                         }
>                     }
>                 }



 Comments   
Comment by Michael Card [ 22/Dec/09 ]

Additonal fix for this issue, was missing dblib originally.

Mike


diff -r Doctrine-1.1.2\lib\Doctrine\Query.php Doctrine-1.1.2-Fixes\lib\Doctrine\Query.php
1367c1367
< if ($driverName == 'pgsql' || $driverName == 'oracle' || $driverName == 'oci' || $driverName == 'mssql' || $driverName == 'odbc') {
—
> if ($driverName == 'pgsql' || $driverName == 'oracle' || $driverName == 'oci' || $driverName == 'mssql' || $driverName == 'odbc' || $driverName == 'dblib') {

Comment by Andrej Pavlovic [ 27/Feb/11 ]

Come on guys, please include this fix into 1.2.4...





[DC-185] The pessimistic offline locking manager locks the entire table Created: 04/Nov/09  Updated: 13/Dec/12

Status: Reopened
Project: Doctrine 1
Component/s: None
Affects Version/s: 1.1.4, 1.1.5, 1.2.3
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Fabian Brussa Assignee: Jonathan H. Wage
Resolution: Unresolved Votes: 7
Labels: None
Environment:

Windows XP, WampServer Version 2.0


Attachments: File DC185TestCase.php     Text File row_based_locking.patch    

 Description   

Scenario:
$entity = Doctrine::getTable('Steps')->find($pID);
$lockingManager = new Doctrine_Locking_Manager_Pessimistic( Doctrine_Manager::connection() );
$lockingManager->releaseAgedLocks(300);
$gotLock = $lockingManager->getLock($entity, 'user1' );

Running this code locks the entire table "Steps", and not just the record.

in the table "doctrine_lock_tracking", in the fields: "object_type" and "object_key" are saved in this case: "Steps" and "IDStep".
I think that here must be saved "Steps" and "120" (the value of IDStep).



 Comments   
Comment by Fabian Brussa [ 18/Nov/09 ]

Is anybody looking into this issue ?

Comment by Jonathan H. Wage [ 18/Nov/09 ]

Can you provide a test case that shows the issue? It is hard to look into the issue without a test to run When I look at the code and our tests everything is passing and fine so I am not sure what your issue could be. Re-open if you have more information to provide.

Comment by Fabian Brussa [ 19/Nov/09 ]

ok, I attach the test case

Comment by Fabian Brussa [ 03/Dec/09 ]

Have you already been able to look at the testcase ??

Comment by Fabian Brussa [ 14/Jan/10 ]

Any news ??

Comment by Piotr Leszczyński [ 25/Jun/10 ]

This issue is still valid for Doctrine 1.2. Doctrine_Locking_Manager_Pessimistic is UNUSABLE without this bug fixed!

Comment by Markus Wößner [ 02/Jul/10 ]

Having a look at "Doctrine_Locking_Manager_Pessimistic::getLock()" it becomes clear what causes this misbehaviour:

    public function getLock(Doctrine_Record $record, $userIdent)
    {
        $objectType = $record->getTable()->getComponentName();
        $key        = $record->getTable()->getIdentifier();

        $gotLock = false;
        $time = time();

        if (is_array($key)) {
            // Composite key
            $key = implode('|', $key);
        }

        try {
            $dbh = $this->conn->getDbh();
            $this->conn->beginTransaction();

            $stmt = $dbh->prepare('INSERT INTO ' . $this->_lockTable
                                  . ' (object_type, object_key, user_ident, timestamp_obtained)'
                                  . ' VALUES (:object_type, :object_key, :user_ident, :ts_obtained)');

            $stmt->bindParam(':object_type', $objectType);
            $stmt->bindParam(':object_key', $key);
            $stmt->bindParam(':user_ident', $userIdent);
            $stmt->bindParam(':ts_obtained', $time);

There is NO hint about the Record's identifier VALUE but only about the identifier's NAME (mostly "id") which appears to be redundant information. Instead of ...

        $key = $record->getTable()->getIdentifier();

..there should be something like ..

        $key = $record->get($record->getTable()->getIdentifier());

In case of composite keys a string concatenation, prefixed by identifier's name might work but I would recommend using "md5()" on resulting value to limit its length since field "object_key" is limited to 250 chars.

Comment by Florian Zumkeller-Quast [ 02/Jul/10 ]

Based on the previous comment by Markus Wößner i created a patch for row based locking.

It concatenates the PK fields and their values to a string and calculates the sha-1 hash as a unique string representing that record. This string is then used as key so that we'll only lock the single Record and not the whole table.

I hope you'll give this patch a try - It solved this problem for me.

Comment by Markus Wößner [ 02/Jul/10 ]

I applied patch from Mr. Florian Zumkeller-Quast and provided testcase didn't fail anymore. I think this should do it. By the way I find it strange that this issue isn't already fixed. I guess locking is not very much used by Doctrine users.

Comment by Jérôme Weber [ 21/Nov/11 ]

I applied patch too and it works now. I guess too that nobody use Lockings but when you use it ... without the patch it fails.

Comment by Grégoire Paris [ 13/Dec/12 ]

Duplicate of http://www.doctrine-project.org/jira/browse/DC-984





[DC-81] Using WITH in combinations with LIMIT returns unexpected results Created: 06/Oct/09  Updated: 15/Jun/10

Status: Reopened
Project: Doctrine 1
Component/s: None
Affects Version/s: 1.2.0-ALPHA1
Fix Version/s: None

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

Mac OS X 10.6.1



 Description   

When I try to limit left joined records with an additional WITH clause, it seems to be bugged when also specifying a LIMIT.

My DQL:

FROM Transporter t LEFT JOIN t.Profile p LEFT JOIN t.Requests r WITH (r.distributor_id = ?) WHERE t.is_active = ? ORDER BY p.company_country, p.company_name LIMIT 20

This returns me 0 results, while removing the LIMIT (or the WITH) will return me 2 results.



 Comments   
Comment by Jonathan H. Wage [ 02/Nov/09 ]

Sorry this is just not enough information to produce the problem. I ran some basic tests looking for what you pointed out but I didn't see any issues. Plus our tests cover this functionality so I imagine that if it were broke we'd get some failures. However, I could be wrong so if you could re-open and provide a failing test case for us that would help with getting it fixed.

Comment by Gerry Vandermaesen [ 16/Apr/10 ]

I actually ran into this problem once again in another problem.

Schema:

Story:
columns:
id:
type: integer(4)
unsigned: true
primary: true
autoincrement: true

Picture:
columns:
id:
type: integer(4)
unsigned: true
primary: true
autoincrement: true
story_id:
type: integer(4)
unsigned: true
notnull: true
is_selected:
type: boolean
notnull: true
default: false
relations:
Story:
foreignAlias: Pictures
local: story_id
foreign: id
type: one
foreignType: many
onDelete: CASCADE

Query:

Doctrine_Query::create()
->from('Story s')
->leftJoin('s.Pictures p WITH p.is_selected = ?', true)
->where('s.id = ?', 5)
->limit(10);

Outputted SQL:

SELECT "s"."id" AS "s_id", "s"."first_name" AS "sfirst_name", "s"."last_name" AS "slast_name", "s"."country" AS "scountry", "s"."email" AS "semail", "s"."content" AS "scontent", "s"."title" AS "stitle", "s"."summary" AS "ssummary", "s"."is_published" AS "sis_published", "s"."is_rejected" AS "sis_rejected", "s"."is_promoted" AS "sis_promoted", "s"."published_at" AS "spublished_at", "s"."created_at" AS "screated_at", "s"."updated_at" AS "supdated_at", "p"."id" AS "pid", "p"."story_id" AS "pstory_id", "p"."filename_original" AS "pfilename_original", "p"."filename_large" AS "pfilename_large", "p"."filename_thumb" AS "pfilename_thumb", "p"."mime_type" AS "pmime_type", "p"."is_selected" AS "pis_selected", "p"."created_at" AS "pcreated_at", "p"."updated_at" AS "p_updated_at" FROM "story" "s" LEFT JOIN "picture" "p" ON "s"."id" = "p"."story_id" AND ("p"."is_selected" = '1') WHERE "s"."id" IN (SELECT DISTINCT "s2"."id" FROM "story" "s2" LEFT JOIN "picture" "p2" ON "s2"."id" = "p2"."story_id" AND ("p2"."is_selected" = '5') WHERE "s2"."id" = '1' ORDER BY "s2"."published_at" DESC LIMIT 10) AND ("s"."id" = '5') ORDER BY "s"."published_at" DESC

Notice the WHERE "s2"."id" = '1' !

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

Can you provide a test case we can run?

Comment by Gerry Vandermaesen [ 15/Jun/10 ]

I could not figure out what test to write but I did narrow the problem down.

It seems that the order of the generated SQL clauses does not match the order of the passed parameters, so in the final SQL query the values are mixed up. You can actually see that happening in the SQL query above. I also noticed there a special cases hardcoded in the code for MySQL and PgSQL as far as these "limit subqueries" go, so that might be another reason why you havent bumped into problem before. I was using an SQLite driver.

I suppose something goes wrong in the SQL generation, but I don't know the core good enough to find out where exactly it does go wrong, so I'm afraid I can't attach a test case, but it should be easy to reproduce now anyway.





[DC-67] Taggable not working Created: 28/Sep/09  Updated: 15/Apr/10

Status: Reopened
Project: Doctrine 1
Component/s: Extensions
Affects Version/s: 1.2.0-ALPHA1
Fix Version/s: None

Type: Bug Priority: Major
Reporter: admirau Assignee: Jonathan H. Wage
Resolution: Unresolved Votes: 4
Labels: None
Environment:

PHP 5.2.8, Doctrine 1.2.0 Alpha1, Vista, Sandbox 1.2.0 Alpha1



 Description   

After enabling Taggable extension, magic methods on the tables (e.g. getPopularTags)
cannot be reached:

Doctrine::getTable('Upload')->getPopularTags();

Fatal error: Uncaught exception 'Doctrine_Table_Exception' with message 'Unknown method Doctrine_Table::getPopularTags' in C:\Users\user\www\extens.dev\lib\Doctrine\Table.php:2608 Stack trace: #0 [internal function]: Doctrine_Table->__call('getPopularTags', Array) #1 C:\Users\user\www\extens.dev\index.php(7): Doctrine_Table->getPopularTags() #2

{main}

thrown in C:\Users\user\www\extens.dev\lib\Doctrine\Table.php on line 2608



 Comments   
Comment by admirau [ 08/Oct/09 ]

The same with Alpha2

Comment by admirau [ 10/Oct/09 ]

Running the tests results in one failure:

Doctrine Unit Tests
===================
Doctrine_Template_Taggable_TestCase.............................................failed

Unexpected Doctrine_Connection_Sqlite_Exception thrown in [Doctrine_Template_Taggable_TestCase] with message [SQLSTATE[HY000]: General error: 1 no such column: a__2] in C:\Users\user\www\extens.dev\lib\Doctrine\Connection.php on line 1067

Trace
-------------

#0 C:\Users\user\www\extens.dev\lib\Doctrine\Connection.php(1015): Doctrine_Connection->rethrowException(Object(PDOException), Object(Doctrine_Connection_Sqlite))
#1 C:\Users\user\www\extens.dev\lib\Doctrine\Query\Abstract.php(936): Doctrine_Connection->execute('SELECT t.id AS ...', Array)
#2 C:\Users\user\www\extens.dev\lib\Doctrine\Query\Abstract.php(990): Doctrine_Query_Abstract->_execute(Array)
#3 C:\Users\user\www\extens.dev\tools\sandbox\extensions\Taggable\lib\Doctrine\Template\TaggableTag.php(76): Doctrine_Query_Abstract->execute(Array, 2)
#4 [internal function]: Doctrine_Template_TaggableTag->getPopularTagsTableProxy(Array)
#5 C:\Users\user\www\extens.dev\lib\Doctrine\Record.php(2569): call_user_func_array(Array, Array)
#6 [internal function]: Doctrine_Record->__call('getPopularTagsT...', Array)
#7 [internal function]: TaggableTag->getPopularTagsTableProxy(Array)
#8 C:\Users\user\www\extens.dev\lib\Doctrine\Table.php(2605): call_user_func_array(Array, Array)
#9 [internal function]: Doctrine_Table->__call('getPopularTags', Array)
#10 C:\Users\user\www\extens.dev\tools\sandbox\extensions\Taggable\tests\Template\TaggableTestCase.php(216): Doctrine_Table->getPopularTags(Array)
#11 C:\Users\user\www\extens.dev\tests\DoctrineTest\UnitTestCase.php(144): Doctrine_Template_Taggable_TestCase->testGetPopularTags()
#12 C:\Users\user\www\extens.dev\tests\DoctrineTest\GroupTest.php(61): UnitTestCase->run()
#13 C:\Users\user\www\extens.dev\tests\DoctrineTest.php(180): GroupTest->run(Object(DoctrineTest_Reporter_Cli), '')
#14 C:\Users\user\www\extens.dev\tools\sandbox\extensions\Taggable\tests\run.php(29): DoctrineTest->run()
#15

{main}

Tested: 1 test cases.
Successes: 26 passes.
Failures: 1 fails.
Number of new Failures: 0
Number of fixed Failures: 0

Tests ran in 1 seconds and used 15279.15625 KB of memory

Comment by Jonathan H. Wage [ 03/Nov/09 ]

The tests all pass now.

http://www.doctrine-project.org/extension/Taggable/1_2-1_0/tests

Comment by admirau [ 04/Nov/09 ]

It still does not work on my environment (Windows or Ubuntu 9.10, PHP 5.3/5.2.6)
The test result is a bit different, but still fails:

Doctrine Unit Tests
===================
Doctrine_Template_Taggable_TestCase.............................................failed

Unexpected Doctrine_Record_UnknownPropertyException thrown in [Doctrine_Template_Taggable_TestCase] with message [Unknown record property / related component "total_num" on "TaggableTag"] in /home/user/doctrine12b/lib/Doctrine/Record/Filter/Standard.php on line 55

Trace
-------------

#0 /home/taat/doctrine12b/lib/Doctrine/Record.php(1359): Doctrine_Record_Filter_Standard->filterGet(Object(TaggableTag), 'total_num')
#1 /home/taat/doctrine12b/lib/Doctrine/Record.php(1318): Doctrine_Record->_get('total_num', true)
#2 /home/taat/doctrine12b/lib/Doctrine/Access.php(117): Doctrine_Record->get('total_num')
#3 /home/taat/doctrine12b/tools/sandbox/extensions/Taggable/tests/Template/TaggableTestCase.php(217): Doctrine_Access->offsetGet('total_num')
#4 /home/taat/doctrine12b/tests/DoctrineTest/UnitTestCase.php(144): Doctrine_Template_Taggable_TestCase->testGetPopularTags()
#5 /home/taat/doctrine12b/tests/DoctrineTest/GroupTest.php(61): UnitTestCase->run()
#6 /home/taat/doctrine12b/tests/DoctrineTest.php(180): GroupTest->run(Object(DoctrineTest_Reporter_Cli), '')
#7 /home/taat/doctrine12b/tools/sandbox/extensions/Taggable/tests/run.php(29): DoctrineTest->run()
#8

{main}

Tested: 1 test cases.
Successes: 26 passes.
Failures: 1 fails.
Number of new Failures: 0
Number of fixed Failures: 0

Comment by Jonathan H. Wage [ 04/Nov/09 ]

Did you update Doctrine? I fixed something in Doctrine 1.2 that fixes it.

Comment by admirau [ 04/Nov/09 ]

Yes, the results above ("total_num") I get after updating to 1.2.0-BETA1.
At 1.2.0-ALPHA2 it was "no such column: a__2" (see above comments).

Do the extensions work with 2.0, or they are 1.2 specific?

Comment by Jonathan H. Wage [ 04/Nov/09 ]

1.2 specific. I don't get any fails now, can you try and troubleshoot it and figure out why it fails for you? They don't fail for me anywhere.

Comment by admirau [ 04/Nov/09 ]

Doctrine_Template_Taggable_TestCase::testGetPopularTags()
line 214:
$test = Doctrine::getTable('TaggableTag')->getPopularTags(array('ArticleTaggableTest', 'ArticleTaggableTest2'));

// compare this with your results
echo serialize($test);
result:
C:19:"Doctrine_Collection":169:{a:7:{s:4:"data";a:0:{}s:6:"_table";s:11:"TaggableTag";s:9:"_snapshot";a:0:{}s:14:"referenceField";N;s:9:"keyColumn";s:4:"name";s:8:"_locator";N;s:10:"_resources";a:0:{}}}

When I try to var_dump($test),
I get circular dependency.
I tried to save results of var_dump($test) to disk, by I get 1,5GB file, and still growing...

Maybe this article helps somehow:
http://www.bigroom.co.uk/blog/php-nesting-level-too-deep-recursive-dependency

Can I see the fix you mentioned above?

I'm sorry, but I do not know what else I can do.
Any ideas?

Comment by Jonathan H. Wage [ 10/Nov/09 ]

I am not sure what to say since it is working for me on all the machines I test. I'll be happy to look if I can get something to fail so I can troubleshoot it.

Comment by admirau [ 11/Nov/09 ]

I've tested on PLD Linux too.
The same thing.

Could you provide sample server/php configuration on which the tests are passing?

Can you describe the fix you've made to correct the errors with ''Unknown method Doctrine_Table::getPopularTags'.
Maybe I can manage to investigate it, but I do not know where to start.

Comment by admirau [ 11/Nov/09 ]

Sorry for reopening this again.

After upgrading to BETA2 all Taggable tests are passing OK for me too.

However, in the code I still get 'Unknown method BlogPostTable::getPopularTags' in /tools\sandbox\lib\Doctrine\Table.php:2793'
Isn't it a regression of the bug you've already fixed before ALPHA2?

Here is the description of how I came to this exception.

At the very end of config.php:

$manager->registerExtension('Taggable');

In the index.php:

<?php

require_once('config.php');

$models = Doctrine_Core::loadModels('models');

$post = new BlogPost();
$post->title = 'This is a sample blog post';
$post->description = 'Cool!';
$post->setTags('2009, symfony, doctrine');
$post->save();

$table = Doctrine::getTable('BlogPost');
echo get_class($table); // echoes Doctrine_Collection
$table->getPopularTags(); // this throws the exception

Then in the console:
php doctrine.php create-db
php doctrine.php generate-models-yaml (BaseBlogPost, BlogPost, BlogPostTable were generated)
php doctrine.php generate-sql (see SQL below)
php doctrine.php create-tables
(all ok)

php index.php

result:

BlogPostTablePHP Fatal error: Uncaught exception 'Doctrine_Table_Exception' with message 'Unknown method BlogPostTable::getPopularTags' in C:\Users\user\www\extens.dev\tools\sandbox\lib\Doctrine\Table.php:2793
Stack trace:
#0 [internal function]: Doctrine_Table->__call('getPopularTags', Array)
#1 C:\Users\user\www\extens.dev\tools\sandbox\index.php(15): BlogPostTable->getPopularTags()
#2

{main}
thrown in C:\Users\user\www\extens.dev\tools\sandbox\lib\Doctrine\Table.php on line 2793
Fatal error: Uncaught exception 'Doctrine_Table_Exception' with message 'Unknown method BlogPostTable::getPopularTags' in C:\Users\user\www\extens.dev\tools\sandbox\lib\Doctrine\Table.php:2793
Stack trace:
#0 [internal function]: Doctrine_Table->__call('getPopularTags', Array)
#1 C:\Users\user\www\extens.dev\tools\sandbox\index.php(15): BlogPostTable->getPopularTags()
#2 {main}

thrown in C:\Users\user\www\extens.dev\tools\sandbox\lib\Doctrine\Table.php on line 2793

And here is the generated SQL:

CREATE TABLE taggable_tag (id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(255) UNIQUE);
CREATE TABLE blog_post_taggable_tag (id INTEGER, tag_id INTEGER, PRIMARY KEY(id, tag_id));
CREATE TABLE blog_post (id INTEGER PRIMARY KEY AUTOINCREMENT, title VARCHAR(255) NOT NULL, description VARCHAR(255) NOT NULL);

Comment by Jonathan H. Wage [ 12/Nov/09 ]

When I test your code, it is working for me. You sure you don't have some old versions of PHP or something?

Comment by admirau [ 12/Nov/09 ]

Everything is up to date.
Tested on different servers, with different operating systems, different versions PHP, different versions of Doctrine, by different people.

Common thing for all these machines is that PHP (>5.2.10 <= 5.3.1) was installed from precompiled builds, not from source.

The error I get now is the same error I got earlier (at the beginning of this topic), which disapperared after your fix.
Then appeared the second error, which was fixed in BETA2, but the previous error is back. (strange, but the tests are passing now, despite the errors in the regular code).

When I find some spare time I'll try to trace the changes you've made since the beginning of the topic.

Comment by admirau [ 18/Nov/09 ]

Maybe the models are generated not properly.
Here are my models (three) for the reproduction procedure (above):

class BlogPostTable extends Doctrine_Table {}

class BlogPost extends BaseBlogPost {}

abstract class BaseBlogPost extends Doctrine_Record
{
public function setTableDefinition()

{ $this->setTableName('blog_post'); $this->hasColumn('title', 'string', 255, array( 'type' => 'string', 'notnull' => true, 'length' => '255', )); $this->hasColumn('description', 'string', 255, array( 'type' => 'string', 'notnull' => true, 'length' => '255', )); }

public function setUp()

{ parent::setUp(); $taggable0 = new Doctrine_Template_Taggable(); $this->actAs($taggable0); }

}

Comment by Jonathan H. Wage [ 18/Nov/09 ]

I have everything generated the same as you.

Comment by Benjamin Arthur Lupton [ 21/Nov/09 ]

Also getting this. Using doctrine 1.2.0-beta3 and also tried with latest SVN. Taggable is latest available.

PHP 5.3.0 running on Zend Server version 5.0.0Beta2

Comment by admirau [ 24/Nov/09 ]

No changes after upgrade to 1.2.0-RC1.

Comment by admirau [ 24/Nov/09 ]

Maybe this helps somehow:

// Following line throws:
// Doctrine_Table_Exception: Unknown method Doctrine_Table::getPopularTags in /home/user/doctrine12rc1/lib/Doctrine/Table.php on line 2799
$tags = Doctrine::getTable('BlogPost')->getPopularTags();

// This line returns collection of tags, as expected
$tags = Doctrine::getTable('TaggableTag')->getPopularTags('BlogPost');

$rel = Doctrine::getTable('BlogPost')->getRelations(); // is int(2)
$rel = Doctrine::getTable('TagableTag')->getRelations(); // is int(2)

Comment by Brian Smith [ 16/Dec/09 ]

Symfony 1.3 SVN updated as of this post.

Ubuntu Karmic with PHP 5.3.1

I have installed Taggable in mysymfonyproject/lib/doctrine_extensions/Taggable

I have two example tables defined with Taggable:

Video:
actAs:

{ Taggable: ~, Timestampable: ~ }

columns:
id:

{ type: integer, primary: true, autoincrement: true }

content_id:

{ type: integer, notnull: true }

title:

{ type: string(255) }

description:

{ type: clob }

relations:
Content:

{ local: content_id, foreign: id }

Photo:
actAs:

{ Taggable: ~, Timestampable: ~ }

columns:
id:

{ type: integer, primary: true, autoincrement: true }

content_id:

{ type: integer, notnull: true }

title:

{ type: string(255) }

description:

{ type: clob }

uri:

{ type: string(255) }

relations:
Content:

{ local: content_id, foreign: id }

The following code functions and populates the database:
$video = new Video();
...
$video->setTags(implode(", ", $tags));
$video->save();

The following code errors:
$tags = Doctrine::getTable('Video')->getPopularTags();

500 | Internal Server Error | Doctrine_Table_Exception
Unknown method VideoTable::getPopularTags

The following code errors:
$tags = Doctrine::getTable('TaggableTag')->getPopularTags('Video');

500 | Internal Server Error | Doctrine_Exception
Couldn't find class TagableTag

Comment by Sorin Neacsu [ 20/Jan/10 ]

Dont know if its related to this issue or not, but for some reason Doctrine::getTable('TaggableTag') throws a "

{Doctrine_Exception}

Couldn't find class TaggableTag" exception

I understand that you would normally do this:
Doctrine::getTable('Video')->getTags()
but I just want plain access to the TaggableTag model and Doctrine doesnt seem to generate any model classess for TaggableTag

Comment by Jonathan H. Wage [ 01/Mar/10 ]

I have tested everything everyone has provided and it is all working for me. The only thing I can suggest now is that someone creates a Doctrine failing test case so that I can run it to see the failure.

Comment by Benjamin Arthur Lupton [ 01/Mar/10 ]

Will create a test case this weekend for this and a few other problems I've across (with fixes). Nothing more frustrating than not being able to reproduce a problem, so I feel your pain.

Comment by Ivar Nesje [ 02/Mar/10 ]

I think this bug i related to the bug in the documentation http://www.doctrine-project.org/jira/browse/DC-378 to get the popular tags you will first have to load the models you want the tags for.

// load models that you want the tags from
Doctrine::getTable('blog');
Doctrine::getTable('products');
// then you can get the tags for the models currently loaded
$tags = Doctrine::getTable('TaggableTag')->getPopularTags();
Comment by Brian Smith [ 07/Mar/10 ]

This works for me:

// load models that you want the tags from
Doctrine::getTable('blog');
Doctrine::getTable('products');
// then you can get the tags for the models currently loaded
$tags = Doctrine::getTable('TaggableTag')->getPopularTags();

Comment by Benjamin Arthur Lupton [ 01/Apr/10 ]

Okie... Creating test cases for this turned out to be more complicated than I can handle - myself not being that familiar with phpunit.
Although within the test it is written as:
$test = Doctrine::getTable('TaggableTag')->getPopularTags(array('ArticleTaggableTest', 'ArticleTaggableTest2'));
Rather than:
$test = Doctrine::getTable('ArticleTaggableTest')->getPopularTags();
which is what this problem is about.

Although I do believe I've nailed the source of the problem - as well as others I've experienced.

Having either of the above causes the system to fall down. I've applied a bunch of fixes/changes to taggable to ensure it works with my systems, such as http://sos.7dots.co.uk/ (still in staging) which uses all of the above mentioned problems, not sure how to do a diff but here is the file:
http://www.2shared.com/file/12385197/c5911e42/Taggable.html

Hope that helps some people. Any changes I've made code can come under the same licence as doctrine, so no worries there.

Comment by admirau [ 15/Apr/10 ]

With 1.2.2 there is some progress.
There was still 1 test failing (CASCADE),
but with the version:
http://www.2shared.com/file/12385197/c5911e42/Taggable.html
provided by Benjamin Arthur Lupton
all test are passing now.

But now when I run:
getTable('ArticleTaggableTest')->getPopularTags();
I get: ERROR: column "i2__1" does not exist

The failing query on PostgreSql is:

SELECT t.id AS t_id, t.name AS tname, COUNT(DISTINCT i.id) AS i_0,
(COUNT(DISTINCT i.id)) AS i__1
FROM taggable_tag t
LEFT JOIN cms_model_image_taggable_tag c ON (t.id = c.tag_id)
LEFT JOIN image i ON i.id = c.id
WHERE t.id IN
(SELECT doctrine_subquery_alias.id
FROM
(SELECT DISTINCT t2.id, (COUNT(DISTINCT i2.id)) AS i2__1
FROM taggable_tag t2
LEFT JOIN cms_model_image_taggable_tag c2 ON (t2.id = c2.tag_id)
LEFT JOIN image i2 ON i2.id = c2.id
GROUP BY t2.id HAVING i2__1 > 0
ORDER BY i2__1 DESC LIMIT 10) AS doctrine_subquery_alias)
GROUP BY t.id HAVING i__1 > 0
ORDER BY i__1 DESC

Seems that this just a PostgreSql issue.
On Mysql it runs just fine.

Comment by Ivar Nesje [ 15/Apr/10 ]

The last comment is concerning how the poular tags are found and rated (on PostgreSql). I think this is unrelated to the rest of this issue and should be moved to a separate issue. (also I only got junk ads when I tried to get the file you are linking to).

Comment by admirau [ 15/Apr/10 ]

I have created separated issue for this:
http://www.doctrine-project.org/jira/browse/DC-634

And yes, this link is quite odd...
There is a lot of crap,
however there is a small link, to download improved code:

Look for: "Save file to your PC: click here"





[DC-560] 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 Created: 10/Mar/10  Updated: 27/Jan/11

Status: Reopened
Project: Doctrine 1
Component/s: Relations
Affects Version/s: 1.2.0
Fix Version/s: None

Type: Bug Priority: Minor
Reporter: Mariano Ramon Assignee: Jonathan H. Wage
Resolution: Unresolved Votes: 1
Labels: None
Environment:

Windows XP, Apache 2.2, PHP 5, Doctrine 1.2 on Symfony 1.4



 Description   

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



 Comments   
Comment by Jonathan H. Wage [ 15/Mar/10 ]

I am unable to reproduce the issue. When I test everything works as expected. We really need some more information and a test case in order to be sure of any issue.

Comment by Ian Ricketson [ 30/Mar/10 ]

I was able to reproduce this issue in Doctrine 1.2. The issue is a two part scenerio, which makes it kind of hard to debug. But here is how to reproduce it.

1.) Lets assume you have a Product model with ID 14.
2.) Lets assume you have an "equal" M:M table that links to itself, (product_product_group). "product_id_1" and "product_id_2" are a compound primary key which link back to the Product model.
3.) Lets assume you that Product 14 is currently linked to products with IDs 2, 5, and 13.
4.) Assuming $object is an instance of Product id 14, call $object->unlink(array(2));

  1. SQL QUERY LOG for Step 4.)
    482 Query START TRANSACTION
    482 Query DELETE FROM product_product_group WHERE (product_id_1 = '14' AND product_id_2 IN ('2'))
    482 Query UPDATE product_product_group SET product_id_1 = '5' WHERE product_id_1 = '14' AND product_id_2 = '5'
    482 Query UPDATE product_product_group SET product_id_1 = '13' WHERE product_id_1 = '14' AND product_id_2 = '13'
    482 Query DELETE FROM product_product_group WHERE product_id_2 = '2' AND product_id_1 = '14'
    482 Query COMMIT

The result is as follows:
a.) Doctrine properly unlinks ID 2
b.) Doctrine updates the M:M table and sets the remaining two IDs (5 and 13) and links them to themselves (it shouldn't run these updates). Now, all we have in our database are two links (5-5 and 13-13).

5.) Now, Product ID 14 has no links anymore in the M:M table. So we call $object->link(array(2, 5, 13));

  1. SQL QUERY LOG for Step 5.)
    512 Query START TRANSACTION
    512 Query SELECT ... FROM to_product_id t WHERE (t.id IN ('13', '2', '5'))
    512 Query INSERT INTO to_product_id_to_product_id_group (product_id_2, product_id_1) VALUES ('2', '14')
    512 Query INSERT INTO to_product_id_to_product_id_group (product_id_2, product_id_1) VALUES ('5', '14')
    512 Query INSERT INTO to_product_id_to_product_id_group (product_id_2, product_id_1) VALUES ('13', '14')
    512 Query COMMIT

This works as expected.

6.) Now, in our database, Product ID 14 properly links to Ids 2, 5, and 13. BUT IDs 5 and 13 also have a link to themselves as a byproduct of the UPDATE queries called in step 4.).
7.) Now, we call $object->unlink(array(2)) again.

  1. SQL QUERY LOG for Step 7.)
    432 Query START TRANSACTION
    432 Query DELETE FROM product_product_group WHERE (product_id_1 = '14' AND product_id_2 IN ('2'))
    432 Query UPDATE product_product_group SET product_id_1 = '5' WHERE product_id_1 = '2' AND product_id_2 = '5'
    432 Query UPDATE product_product_group SET product_id_1 = '5' WHERE product_id_1 = '14' AND product_id_2 = '5'
    432 Query ROLLBACK

The following error occurs:

  1. Mar 30 20:40:41 symfony [err] exception 'Doctrine_Connection_Mysql_Exception' with message 'SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '5-5' for key 1' in /path/to/symfony/1.4/lib/plugins/sfDoctrinePlugin/lib/vendor/doctrine/Doctrine/Connection.php:1082

So basically, it repeated the same queries from step 4.), however this time around, when attempting to set ID 5 to itself, it fails because there is already an entry this pair in the database. Since we have a compound primary key, this non-unique combination is not allowed. I don't know why its trying to run these UPDATE queries, but its definitely something to do with thre relationship being "equal".

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

It is hard to decipher the problem. It is best and most clear to provide a test case.

Comment by Ian Ricketson [ 27/Jan/11 ]

I know this is old, and is likely to never be resolved, but the problem does still exist.

Basically, "unlinking" an equal relation only unlinks one side of the relationship:

DELETE FROM user_user_group WHERE (user_id_1 = '20' AND user_id_2 IN ('9'))

However, if the relationship was created from the other side (i.e. user_id_1 = 9 and user_id_2 = 20), then it doesn't properly get rid of the relationship. Again, this only applies when a relationship is marked as "equal".

Here is another post about the same issue, with their work-around:
http://stackoverflow.com/questions/1385281/how-do-i-remove-a-self-referencing-nn-relation-in-doctrine





[DC-457] Generating YAML schema from DB breaks on certain table names Created: 26/Jan/10  Updated: 17/Mar/10

Status: Reopened
Project: Doctrine 1
Component/s: Import/Export
Affects Version/s: 1.2.1
Fix Version/s: None

Type: Improvement Priority: Minor
Reporter: Ari Pringle Assignee: Jonathan H. Wage
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Linux, Symfony 1.4.1, Doctrine 1.2.1



 Description   

When attempting to reverse engineer databases using the generate-yaml-db task (via Symfony's doctrine:build-schema task), I've run into some issues due to table names not being compatible with PHP classnames. It creates the PHP classes in /tmp, setting the class name to the same as the table name, and then attempts to build the YAML files off of those.

I attempted to do this a few months ago and ran into issues because some of the table names began with numbers ("123MyTable" is not a valid PHP class name). My most recent attempt failed because of a table named "List", which is a reserved PHP keyword.

While I'm not sure if this is the ideal solution, I think allowing a way to pre-define a prefix for the generated PHP class names would be great.



 Comments   
Comment by Stefan [ 22/Feb/10 ]

I think there seems to be a general problem in usage of table names, when using the generating from database to yml.

I think there something changed with the naming stragegies in the last versions (I do not know in wich one), but it works with doctrine 1.0.10 (used in symfony 1.2.8).

theoretical stragegies:
1. Only the first letter is a capital (e.g. ProjectCategory --> Projectcategory)
2. When a _ is inside the name, the _ is removed an a capital will used (e.g. Project_Values --> ProjectValues)

But doctrine does not make use of this anymore. When I generate with doctrine 1.2.1 (used in symfony 1.3.2) my table ProjectCategory will be named as ProjectCategory in schema. And when the schema is wrong, all generated models are incompatible to older versions.

Comment by Jonathan H. Wage [ 01/Mar/10 ]

Hmm. I don't quite understand. I tested the things you described and it all works as I expected it to. You'll have to provide some more information.

Comment by Ari Pringle [ 17/Mar/10 ]

Sorry for the delay in response.

Here's how to reproduce my original issue (I should also mention that I'm running PHP 5.2.4):

mysql> create table List (id int not null auto_increment,primary key(id));
bash$ ./symfony doctrine:build-schema
>> doctrine  generating yaml schema from database

Parse error: syntax error, unexpected T_LIST, expecting T_STRING in /tmp/tmp_doctrine_models/List.php on line 17

bash$ sed -n '17p' /tmp/tmp_doctrine_models/List.php 
class List extends Doctrine_Record

The same thing happens if the table name begins with a number (ie 123MyTable).

Again, I'm not sure if this is the ideal solution, but being able to define a prefix to the generated class names would take care of this issue (so the generated class name above could be MyPrefix_List instead of List)





Generated at Mon Apr 21 00:37:16 UTC 2014 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.