DQL Example about count related entity is wrong

It will help us use the namespace and code autocomplete in some IDE.

To find out if a model is persist, is missing in the documentation of doctrine 2.

To become the state of an model you must call the entitymanager->getUnitOfWork()->getEntityState(model)

If you need to override the ID Generator, e.g. during a migration, you can do that in your migration script as follows:

Make sure that both calls equal to the same generator type. You can now modify the @Id fields in your entities. Additionally, make sure that you set the IdGenerator after you created the database using e.g. SchemaTool->create().

http://www.doctrine-project.org/docs/dbal/2.0/en/reference/types.html links to http://www.doctrine-project.org/docs/dbal/2.0/en/known-vendor-issues instead of http://www.doctrine-project.org/docs/dbal/2.0/en/reference/known-vendor-issues.html

Also noticed that the documentation still says "Doctrine DBAL v2.0.0-BETA4 documentation"

i'd like to see more example in documentation with this subselects
[23:08] <beberlei> can you open a tciket on jira? then i dont forget to do that when i have time

When you are looking for a config example for the bidirectional mapping of an one-to-many association you will just find an example with XML, but not with YAML or PHP. It would be nice if somebody could add an example or a link to the bidirectional one-to-one association, because it should be the same, right?

Here the link to the example: http://www.doctrine-project.org/docs/orm/2.0/en/reference/association-mapping.html#one-to-many-bidirectional

Hi, my battle is described here: http://groups.google.com/group/doctrine-user/browse_thread/thread/db9c77b6bc000f13

When I follow bugtracker tutorial I think that there is an error when working with Annotations, see these examples:

Start a new bugtracker project as described in tutorial from scratch, create folders and files as tutorial expose, then do following changes:

1) put Product, Bug, User class files at root level, same level as bootstraps files and create_xxxxx files
2) Create 'entities' folder, but leave it empty.
3) Create 'yaml' and 'xml' at root level too and add related files.
4) Open bootstrap and edit paths to Product, Bug and User class files to read from root files as in 1) so they can be read from scripts.

1) YAML
a) When using YAML as mapping driver you need a path to Setup::createYAMLMetadataConfiguration( ) method, I use this
Setup::createYAMLMetadataConfiguration(array(_DIR_."/yaml"), $isDevMode);
where "yaml" directory is same level as bootstraps and create_xxxxx files are. When executing script to create a product
Doctrine work as expected, creating a row inside table correctly.
b) If you comment line where class Product is included, the object can't be found at runtime and will throw an exception as expected.
c) With uncommented require Product line, change yaml name folder to anything, and Doctrine throw an exception (MappingException) as expected

so Setup method path argument is considered correctly, Doctrine engine must know where yaml files for classes are.

2) XML
a) When using XML as mapping driver you need a path to Setup::createXMLMetadataConfiguration( ) method, I use this
Setup::createXMLMetadataConfiguration(array(_DIR_."/xml"), $isDevMode);
where "xml" directory is same level as bootstraps and create_xxxxx files are. When executing script to create a product
Doctrine work as expected, creating a row inside table correctly.
b) If you comment line where class Product is included, the object can't be found at runtime and will throw an exception as expected.
c) With uncommented require Product line, change xml name folder to anything, and Doctrine throw an exception (MappingException) as expected

so Setup method path argument is considered correctly again, Doctrine engine must know where xml files for classes are.

3) Annotations
a) When using Annotations as mapping driver you need a path to Setup::createAnnotationsMetadataConfiguration( ) method, I use this
Setup::createAnnotationsMetadataConfiguration(array(_DIR_."/entities"), $isDevMode);
where "entities" directory is same level as bootstraps and create_xxxxx files are (but remember THEY ARE EMPTY). When executing script to create a product
Doctrine WORK AS EXPECTED, creating a row inside table correctly and still I don't know how if THERE IS NO Annotations files.
b) If you comment line where class Product is included, the object can't be found at runtime and will throw an exception as expected.
c) With uncommented require Product line, change name folder to anything, and Doctrine WILL NOT throw an exception, continue with execution.
d) Copy /Product.php to /entities/Product.php, then comment docblocks from /Product class (using // or delete them). When running script Doctrine throw a MappingException with message: "Class Product is not a valid entity or mapped super class", when as follow concepts from 1) (yaml) and 2) (xml) it should search docblocks from /entities/Product.php file (path argument from Setup), right?

so Setup method path argument IS NOT CONSIDERED, Doctrine engine use already defined classes to get Annotations docblocks using php reflexion classes, methods and functions.

How to deal with this? I mean...

a) Erase path argument from Setup::createAnnotationMetadataConfiguration methos (and similar functions for Annotations) because is not needed, classes and annotations must be defined before.
b) Add support to find docblocks from path argument when no valid dockblock is found from class definition, so entities classes can live without docblocks because they are found inside Setup path function argument, as YAML and XML do.

I know that is easy to follow tutorial guidelines to develop applications in Annotations point of view, load them before Doctrine script start (with require/include or autoloaders, etc) and will work, but I think that is wrong how tutorial and functional logic are given, so a) and b) are my proposed solutions. I think b) should be right, get dockblocks from a class already defined and if are not defined it follow XML and YAML logic: read metadata from other files.

Attachment: My bugtracker Netbeans project.

Sorry by my english

I am in the process of switching over from Doctrine 2.0.7 to Doctrine 2.1 and one of the major missing components in my entities was the new use of using the mapping entity.

Example:

 
<?php
namespace My\Project\Entities;
use Doctrine\ORM\Mapping\Entity as ORM;
/**
 * @ORM\Entity(...)
 */
class Something
{
// Doctrine annotations here
}

The nessecity for this to be included in the entities is (as far as I can tell) nowhere to be found in the docs, so I am a little curious as to how people are supposed to know. I have also had a look here: http://www.doctrine-project.org/blog/doctrine-2-1-beta-release and can see no references too it.

Am I missing something; or is it really just missing from the docs?

Arbitrary joins need to be documented in EBNF

Visiting the following page:

http://www.doctrine-project.org/api/orm/2.2/class-Doctrine.ORM.QueryBuilder.html

always redirects back to http://www.doctrine-project.org/api/orm/2.2/

Available documentation is a bit thin regarding the "WITH" clause on JOIN expressions. Only a single example is provided in

http://docs.doctrine-project.org/en/2.1/reference/dql-doctrine-query-language.html#dql-select-examples

WITH seems to allow to only "partially" load a collection, so the collection in memory does not fully represent the associations available in the database.

The resulting collection is marked as "initialized" and it seems there is no way to tell later on whether/how (with which expression) the collection has been initialized.

When using the collection filtering API, the "initialized" flag on the collection will lead to in-memory processing. If a collection has been loaded WITH a restricting clause and another filter is applied later, results may not be what one might expect.

I assume this is by design (no idea how the collection could be "partially" loaded and behave correctly under all conditions), so filing it as a documentation issue.

see pull request 24 on github.com

On paragraph 4.8.1.1 SequenceGenerator, the correct example should be:

<?php
class User {
/**

• @Id
• @GeneratedValue(strategy="SEQUENCE")
• @SequenceGenerator(sequenceName="tablename_seq", initialValue=1, allocationSize=100)
  */
  protected $id = null;
  }

In chapter 19 of the reference guide some coding examples seem to be inaccurate or incorrect. Especially when it comes to the bidirectional many-to-many associations, this might be confusing.

Example:
The code fragment on http://www.doctrine-project.org/docs/orm/2.0/en/reference/annotations-reference.html#annref-manytomany has the following issues:

• it does not include class declarations although the collections associated are both mentioned. It should be clear to which target entity they belong and therefore their classes should be declared.

• from the context it seems that the associated classes should probably be User and Group, and the owning side is User. So the association should probably be inversed by 'users', although the example mentions 'features'.

• the mapping for the inverse side maps a collection called $features, although this should probably be $users. Also the class declaration for the Group class is missing.

Some other code fragments in chapter 19 have similar issues. I think they could easily be replaced by the examples from the earlier chapters, like for the bidirectional man-to-many association the example from chapter 5:

http://www.doctrine-project.org/docs/orm/2.0/en/reference/association-mapping.html#many-to-many-bidirectional

The documentation recommends using private variables in entities. This can be problematic on entities with relations when using caching drivers as the proxy objects cannot access private variables and so the caching driver can throw notices like

...apc_store(): "_id" returned as member variable from __sleep() but
does not exist in ...

Making member variables protected resolves this issue when caching is enabled.

This information would be helpful on the documentation so others can be made aware of this issue. We spent a few days trying to debug the issue before understanding exactly what was going on.

Following the changes made for DDC-1472 and DDC-1416, the EBNF for InExpression should have been updated. It now takes an ArithmeticExpression instead of a SingleValuePathExpression.

Openning an Oracle Database with Doctrine and Symfony doesn't support by default utf8 charset.

To support it, it is necessary to specify charset=AL32UTF8 in the dsn of the database in config/databases.yml. Analogous to the example shown at
http://groups.google.com/group/doctrine-user/browse_thread/thread/d0d22145d8bdc83
however we have been able to use it fully only with the oracle driver (instead of oci), for example:

oracle:dbname=//192.168.2.9:1521/nomina_dev;charset=AL32UTF8

Documentation by Alexia Velásquez (alexia.velasquez@hotmail.es, Vladimir Tàmara (vtamara@pasosdeJesus.org) and Fernando Guerrero

I am missing documentation about how to handle indexes in YAML and XML definition files. I had to search in the code to learn how to do that.
Please add some documentation about it.

This issue is related to #DDC-160 where the reporter asked for documentation about indexes in annotation mapping.

EDIT:
Maybe an example how I have done it with YAML would be helpful for others:

User:
  type: entity
  fields:
    id:
      id: true
      type: integer
      generator:
        strategy: IDENTITY
    email:
      type: string
      length: 150
      unique: true
    active:
      type: boolean
  indexes:
    indexActiveField: { name: idx_user_active, columns: [ active ] }

indexActiveField is the name of the index used by doctrine and idx_user_active is the name of the index in the database. The rest should be clear.

According to the docs on this page:
http://docs.doctrine-project.org/en/latest/reference/dql-doctrine-query-language.html#using-index-by

The following "multi-dimensional index" should be perfectly possible, with a default hydration mode:
SELECT b as business, p as product FROM Businesses b INDEX BY b.id JOIN Products p WITH b.id = p.businessid INDEX BY p.id

However, b.id is completely ignored (it is a numeric primary key).

I tried to go further, giving 2 products a matching barcode and indexing by barcode and then a (unique, numeric) productid. Only the barcode worked as a key and only one of the products with a matching barcode was selected. I used this query to test:
SELECT p FROM Products p INDEX BY p.barcode JOIN p.businessid b INDEX BY p.id

I also flagged the docs, because I don't think a userid should/could be starting from 0.

When you have a queryBuilder and you want to break it down using getDQLParts, You can't restore it by looping over the parts and adding them.

This is what I am doing:

$parts = $qb->getDQLParts();

// save the parts and use them in a different environment.

$newQb = $em->createQueryBuilder();
foreach ($parts as $name => $part) {
  $newQb->add($name, $part);
}

Please make it more clear that prePersist and postPersist events are called only when creating new entity (that is, prior and after a database insert).

IRC log:
helmer
Hi. I have a question regarding (pre|post)persist events. Why are the events ignored for second persist (should fire prePersist) + flush (should fire postPersist) in the following pastie: http://pastebin.com/V8CrPWkM Is it a bug or am I missing sth?
Stof
helmer: there is no second persist. persist() means saying Doctrine to manage the entity. Once it is managed, you are not persisting it anymore but updating it
helmer
Stof: so basically one could define these two events for themselves as (pre|post)Insert?
beberlei
yes
helmer
thanks beberlei&stof! though a suggestion to docteam .. perhaps make it more clear to people like me, current doc can be kind of misleading ie: "There are two ways for the prePersist event to be triggered. One is obviously when you call EntityManager#persist()" http://www.doctrine-project.org/docs/orm/2.0/en/reference/events.html#prepersist

Pastie code (probably expired by now):
$entity = new Entity();

$entity->setSomething('xxx');
$this->em->persist($entity);
$this->em->flush();

$entity->setSomething('yyy');
$this->em->persist($entity);
$this->em->flush();

Since DDC-514 there is a default in place for this, thus the description is wrong in stating this is required.

http://www.doctrine-project.org/docs/orm/2.0/en/reference/events.html

The above URL has an example of how to register lifecycle callbacks from YAML, but actually it does not work. The correct way of doing it is mentioned on the page: http://www.doctrine-project.org/docs/orm/2.0/en/reference/yaml-mapping.html

I read mini tutorial http://www.doctrine-project.org/docs/orm/2.0/en/reference/introduction.html#mini-tutorial and try run command "doctrine orm:schema-tool --drop"

TIP When you create new model classes or alter existing ones you can recreate the database schema with the command doctrine orm:schema-tool --drop followed by doctrine orm:schema-tool --create.

It's not worked for me:

[InvalidArgumentException]
Command "orm:schema-tool" is not defined.

But command "doctrine orm:schema-tool:drop --force" and "doctrine orm:schema-tool:create" is worked.
It's bug in documentation?

The Doctrine 2 reference documentation defines the comparison operators syntactically, as:

ComparisonOperator  ::= "=" | "<" | "<=" | "<>" | ">" | ">=" | "!="

Now, without experimentation, it is pure guesswork to tell whether e. g. null = null, or e. g. null <> null, or null = 0, or null <> 0 are considered true statements in DQL.

In SQL semantics, all four statements would be false (or more precisely, null). In PHP semantics, both null == null and null == 0 would be true, while null != null and null != 0 would be false.

It would be helpful to have the semantics of the comparison operators defined. While comparisons with non-null values behave in a common-sense way, it is hard to guess how queries involving comparison operators on fields allowing null values or null query arguments will filter the results, without knowing the exact semantics of the comparison operators with regard to null values.

It would be great if this could be clarified in the docs.

Thanks.

The Documentation for @JoinColumn annotation states:
"This annotation is not required. If its not specified the attributes name and referencedColumnName are inferred from the table and primary key names."

However, this seems not to be correct. If you have non-standard name for the @Id columns for a @OneToMany/@ManyToMany the name and referencedColumnName are not correctly inferred.

https://gist.github.com/e61bf8f4462870ffd4f3

The code example here:
http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/tools.html#configuration-non-pear

In the second "use" statement it references a "EntityManagerHelper" from the "Doctrine\DBAL\Tools\Console\Helper\" package. However, it does not exist there. It does exist in the "Doctrine\ORM\Tools\Console\Helper\" package though, and replacing it seems to work.

8. Working with Objects:
"... if the relationships from X to these other entities are mapped with cascade=PERSIST or cascade=ALL (see "Transitive Persistence")."
"... with cascade=REMOVE or cascade=ALL (see "Transitive Persistence")."
and so on.
Maybe it's reasonable to make "Transitive Persistence" as links to http://www.doctrine-project.org/docs/orm/2.1/en/reference/working-with-associations.html#transitive-persistence-cascade-operations

I am refering to the examples on

http://www.doctrine-project.org/docs/orm/2.0/en/reference/batch-processing.html

Let's say you want to process a bulk of 25 objects and have a batchsize of 20.
With the code provided the last 5 would not be saved as far as I understand unless you do another flush after the for-loop.

This is probably very clear to any experienced Doctrine developer but maybe it is also confusing for beginners like me (some internet sources say, that flush is executed automatically at the end of the request, but obviously it is not). Maybe this could be mentioned somewhere?

Hello,

Reference Guide topic 4.8 Identifers/Primary Keys(http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/basic-mapping.html#identifiers-primary-keys) states that: "Every entity class needs an identifier/primary key."

However, example in topic 7.3. Class Table Inheritance (http://docs.doctrine-project.org/projects/doctrine-orm/en/2.1/reference/inheritance-mapping.html#class-table-inheritance) doesn't contain any definitions for Ids.

Consider following code:

/**
 * @Entity
 * @InheritanceType("JOINED")
 * @DiscriminatorColumn(name="discr", type="string")
 * @DiscriminatorMap({"person" = "Person", "employee" = "Employee"})
 */
class Person
{
    /** @Id @Column(type="integer") */
    private $id;

    public function getId() {
        return $this->id;
    }
}

/** @Entity */
class Employee extends Person
{
    /** @Id @Column(type="integer") */
    private $id;

    // Overrides parent to retrieve private
    public function getId() {
        return $this->id;
    }
}

// create instances and $em->persist(...)

// $person instanceof Person
$person->getId(); // Returns id.

// $employee instanceof Employee
$employee->getId(); // Returns null. Private $id in subclass isn't assigned.

Please clarify correct use of identifiers in CTI subclass entities. Should subclasses contain any definitions of identifiers?

Thank you!

Ludek

The documentation at http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/faq.html#how-do-i-set-the-charset-and-collation-for-mysql-tables clearly states that the collation should be set at database level and then inherited for all tables created.

Digging through the code and reading this issue http://www.doctrine-project.org/jira/browse/DDC-2139 it's clear that this is no longer the case.

As far as I know, docs don't explain that it is up to the developer to keep the object graph consistent instead of relying on Doctrine ORM for everything.

For example, for many to many, examples like following may be used:
https://gist.github.com/3121916

The XML Mapping documentation indicates that "orphan-removal" is an attribute on One-to-One or Many-to-One elements, but in DoctrineORM version 2.0 that does not work. It seems I have to make "orphan-removal" a child attribute of those elements to turn it on.

Just to be clear:

Documentation says to do this, but it does not work:
<one-to-many field="..." orphan-removal="true"> ... </one-to-many>

This does work:
<one-to-many field="..."> <orphan-removal>true</orphan-removal> </one-to-many>

See: http://www.doctrine-project.org/documentation/manual/2_0/en/annotations-reference#ann_mappedsuperclass

@MappedSuperclass

An mapped superclass is an abstract or concrete class that provides persistent entity state and mapping information for its subclasses, but which is not itself an entity. This annotation is specified on the Class docblock and has no additional attributes.

This doesn't adequately communicate how to use it. It took me several minutes of failing before I downloaded the PDF and did a search for @MappedSuperclass to find an example of how it's used.

Specifically the following were unclear:

• Is this defined on the superclass or on the children classes?
• If it's defined on the child classes, does it take parameters? The name of the super class?
• It was not at all apparent to me that it was mutually exclusive with the @Entity tag

We should keep a list of DQ reserved keywords, so users can check out what they can use or not.

http://www.doctrine-project.org/projects/orm/2.0/docs/cookbook/getting-started-xml-edition/en#getting-started-xml-edition

Paragraph 1:
The benefit of Doctrine for the programmer is the possibility can to focus soley solely on the business and worry about persistence only as a secondary task. This doesn't mean persistence is not important to Doctrine 2, however it is our belief that there are considerable benefits for object-oriented programming, if persistence and entities are kept perfectly seperated separated.

Paragraph 2:
Entities are leightweight lightweight PHP Objects that don't need to extend any abstract base class or interface. An entity class must not be final or contain final methods. Additionally it must not implement __clone nor __wakeup or it should do so safely.

What would be the best way to note other errors?

Throughout the documentation found in the following link (http://www.doctrine-project.org/docs/orm/2.0/en/reference/xml-mapping.html) when mentioning eager or lazy loading the documentation says FETCH instead of EAGER.

E.G. content says "fetch - Either LAZY or FETCH" which should read "fetch - Either LAZY or EAGER"

This occurs in a few different areas on this page.

In the note in
http://www.doctrine-project.org/docs/orm/2.0/en/reference/working-with-objects.html#merging-entities
It appears to state that private variables are not serialized for child objects

If this is the only reason entities can't have private properties, then this restriction is no longer valid, or possibly be reconsidered.

<?php

class A {
    private $a = null;

    public function setValue($value) {
        $this->a = $value;
    }
}

class B extends A {}

$b = new B();
$b->setValue("B");
var_dump($b);

$c = unserialize(serialize($b));
var_dump($c);

The output suggests private variables are serialized, and are restored fine

I was reading http://www.doctrine-project.org/docs/orm/2.1/en/reference/tools.html
In 24.2.2. Configuration (Non-PEAR)

The example seems to be wrong.

use Doctrine\DBAL\Tools\Console\Helper\EntityManagerHelper;
should be
use Doctrine\ORM\Tools\Console\Helper\EntityManagerHelper;

That's all.

The following DQL :

SELECT s,ba,c,mno,ss,sws,ccs,cns,cws FROM WLCoreBundle:SIM s INNER JOIN s.billingAccount ba LEFT JOIN s.connection c INNER JOIN s.status ss LEFT JOIN s.workflowStatus sws INNER JOIN c.customerStatus ccs INNER JOIN c.networkStatus cns LEFT JOIN c
.workflowStatus cws INNER JOIN s.mno mno ORDER BY c.msisdn ASC

Produces the following SQL :

SELECT * FROM (SELECT c0_.id AS id0, c0_.iccid AS iccid1, c0_.created AS created2, c0_.updated AS updated3, c0_.spreference AS spreference4, c1_.id ASid5, c1_.account_number AS account_number6, c1_.name AS name7, c1_.address1 AS address18, c1_.address2 AS address29, c1_.address3 AS address310, c1_.address4 AS address411, c1_.address5 AS address512, c1_.address6 AS address613, c1_.email_address AS email_address14, c1_.spreference AS spreference15, c2_.id AS id16, c2_.msisdn AS msisdn17, c2_.local AS local18, c2_.imsi AS imsi19, c2_.data AS data20, c2_.fax AS fax21, c2_.api AS api22, c2_.activation_date AS activation_date23, c2_.contract_end_date AS contract_end_date24, c2_.created AS created25, c2_.updated AS updated26, c2_.spreference AS spreference27, c3_.id AS id28, c3_.ident AS ident29, c3_.label AS label30, c3_.description AS description31, c4_.id AS id32, c4_.ident AS ident33, c4_.label AS label34, c4_.description AS description35, c4_.customer_label AS customer_label36, c4_.customer_description AS customer_description37, c5_.id AS id38, c5_.ident AS ident39, c5_.label AS label40, c5_.description AS description41, c6_.id AS id42, c6_.ident AS ident43, c6_.label AS label44, c6_.description AS description45, c7_.id AS id46, c7_.ident AS ident47, c7_.label AS label48, c7_.description AS description49, c7_.customer_label AS customer_label50, c7_.customer_description AS customer_description51, c8_.id AS id52, c8_.name AS name53, c8_.email_address AS email_address54, c8_.is_active AS is_active55, c8_.spreference AS spreference56, c0_.billing_account AS billing_account57, c0_.customerHierarchy AS customerHierarchy58, c0_.mno AS mno59, c0_.status AS status60, c0_.workflow_status AS workflow_status61, c1_.customer_hierarchy AS customer_hierarchy62, c1_.country AS country63, c1_.tax_rate AS tax_rate64, c1_.currency AS currency65, c1_.status AS status66, c1_.priority AS priority67, c2_.sim AS sim68, c2_.customer_status AS customer_status69, c2_.network_status AS network_status70, c2_.workflow_status AS workflow_status71, ROW_NUMBER() OVER (ORDER BY msisdn17 ASC) AS doctrine_rownum FROM core_sim c0_ WITH (NOLOCK) INNER JOIN core_billing_account c1_ ON c0_.billing_account = c1_.id LEFT JOIN core_connection c2_ ON c0_.id = c2_.sim INNER JOIN core_sim_status c3_ ON c0_.status = c3_.id LEFT JOIN core_sim_workflow_status c4_ ON c0_.workflow_status = c4_.id INNER JOIN core_connection_customer_status c5_ ON c2_.customer_status = c5_.id INNER JOIN core_connection_network_status c6_ ON c2_.network_status = c6_.id LEFT JOIN core_connection_workflow_status c7_ ON c2_.workflow_status = c7_.id INNER JOIN core_mno c8_ ON c0_.mno = c8_.id) AS doctrine_tbl WHERE doctrine_rownum BETWEEN 1 AND 10

Which returns an error :

SQLSTATE[42S22]: [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'msisdn17'.

Same query works fine in Doctrine 2.3

Using the DQL "partial" keyword is not enough to get a partial entity as a result.
The DQL hint HINT_FORCE_PARTIAL_LOAD must be used as well.

$q = $em->createQuery('SELECT partial r.{id,comment} FROM Entities\Rating r WHERE r.id=3');
$r = $q->getResult() /* HYDRATE_OBJECT is the default hydration mode */

Here, $r contains the full Entity, a SELECT * has been sent

$q = $em->createQuery('SELECT partial r.{id,comment} FROM Entities\Rating r WHERE r.id=3');
$q->setHint(Doctrine\ORM\Query::HINT_FORCE_PARTIAL_LOAD, 1);

$r = $q->getResult() /* HYDRATE_OBJECT is the default hydration mode */

Here, $r contains only the selected fields, hence a true partial Entity

Hi.
First sorry for my bad english.
In
SELECT u.id FROM CmsUser u WHERE :groupId MEMBER OF u.groups
DQL we can't use Array of groupId like

Similar to this issue I think, but triggered when performing a query on entities modelled with Class Table Inheritance, e.g.

$qb = $repository->createQueryBuilder('entity');
$metadata = $em->getClassMetadata($class);
$qb->where('entity INSTANCE OF :type')->setParameter('type', $metadata);
$qb->getQuery()->execute();

Seems that there isn't a corresponding entry in the parameter mapping array for this clause, which triggers the exception at line 254 of Doctrine\ORM\Query:

if (count($paramMappings) != count($this->parameters))

Update and delete executors for Class Table Inheritance (JOINED) are extremely slow on MySQL platform. It is most probably due to use of subselect on the temporary table.
The slowdown is really significant as the table size increases. As an example, lets have a root entity with one subclass:

/**
 * @Entity
 * @InheritanceType("JOINED")
 * @DiscriminatorColumn(name="discr", type="string")
 * @DiscriminatorMap({"root" = "Root", "a" = "SubA"})
 */
class Root
{
	/**
	 * @Column(type="integer")
	 * @Id
	 * @GeneratedValue
	 */
	private $id;

	/**
	 * @Column(type="integer")
	 */
	private $xyz;
}

/**
 * @Entity
 */
class SubA extends Root
{
	/**
	 * @Column(type="integer")
	 */
	private $foo;
}

Now lets perform a simple DQL UPDATE:

UPDATE Entities\Root r SET r.xyz = 123 WHERE r.id > ?

(note: always the upper half of entries)
Which creates following SQLs:

CREATE TEMPORARY TABLE Root_id_tmp (id INT NOT NULL)
INSERT INTO Root_id_tmp (id) SELECT t0.id FROM Root t0 LEFT JOIN SubA s0_ ON t0.id = s0_.id WHERE t0.id > 25000
UPDATE Root SET xyz = 123 WHERE (id) IN (SELECT id FROM Root_id_tmp)
DROP TEMPORARY TABLE Root_id_tmp

The time spent on this on MySQL 5.5.17 and PostgreSQL 9.1 is:

As you can see, MySQL is drastically slower on even relatively small tables. This currently makes Doctrine unusable for this type of inheritance on MySQL. The solution probably would be to avoid subselect in WHERE clause in Doctrine\ORM\Query\Exec\MultiTableUpdateExecutor and Doctrine\ORM\Query\Exec\MultiTableDeleteExecutor.

Feel free to try/modify the test script yourself, it's here.

When running following DQL in newly cleared EntityManager, with the provided entities (see attached archive or gist at https://gist.github.com/2473775 ), results in different fetched association:

DQL without join:
SELECT a FROM Entity\A a WHERE a.id = :id

SQL without join:
SELECT a0_.a_id AS a_id0, a0_.id AS id1 FROM a a0_ WHERE a0_.a_id = ?

Result without join:
$query->getOneOrNullResult()>getB()>getName(); // 'correct'

DQL with fetch join:
SELECT a, b FROM Entity\A a LEFT JOIN a.b b WHERE a.id = :id

SQL with fetch join:
SELECT a0_.a_id AS a_id0, b1_.id AS id1, b1_.name AS name2, a0_.id AS id3 FROM a a0_ LEFT JOIN b b1_ ON a0_.id = b1_.id WHERE a0_.a_id = ?

Result with fetch join:
$query->getOneOrNullResult()>getB()>getName(); // 'wrong' (different result)

The problem seems to be strictly related with how the `@JoinColumn` is configured.

the DQL Parser don't parse properly functions when a ScalarExpression is needed like of all case functions.

In fact first function token is interpreted as a T_IDENTIFIER and enter on line 1663 of Doctrine\ORM\Query\Parser class. in search of math operator, when not found this case considere that the token is a row element with no considération of the functions procession treated after.

fix of this bug consist to enclose the line 1672 by a if (!$this->_isFunction()).

Having the following part in select DQL throws an exception.

SUM(CASE
            WHEN c.startDate <= :start THEN c.endDate - :start
            WHEN c.endDate >= :end THEN :end - c.startDate
            ELSE 0
            END) 

exception:

[Syntax Error] line 0, col 124: Error: Expected Doctrine\ORM\Query\Lexer::T_ELSE, got '-' 

It seems that it's failing inside the second THEN

This one also seems to fail:

SUM(CASE
            WHEN c.startDate <= :start THEN (c.endDate - :start)
            WHEN c.endDate >= :end THEN (:end - c.startDate)
            ELSE 0
            END) 

exception:

[Syntax Error] line 0, col 60: Error: Unexpected '(' 

Another one:

SUM(CASE
                WHEN c.startDate <= :start THEN c.endDate - :start
                WHEN c.endDate >= :end THEN :end - c.startDate
                ELSE 0
                END) = :result FROM ...

exception:

[Syntax Error] line 0, col 60: Error: Expected Doctrine\ORM\Query\Lexer::T_FROM, got '=' 

i am not able to extract only month from the date field using doctrine2 using 'MONTH' function

DQL should deal correctly with composite primary keys:

SELECT u FROM User u WHERE u.CompositeAssocEntity = ?1

Should be converted to:

SELECT ... FROM users u WHERE (u.cae_id1, u.cae_id2) = (?, ?) // or something similar

It also supports IN expressions:

SELECT u FROM User u WHERE u.CompositeAssocEntity IN (?1, ?2)

Should be converted to:

SELECT ... FROM users u WHERE (u.cae_id1, u.cae_id2) IN ((?, ?), (?, ?)) // or something similar

MySQL, SQLite and PgSQL works smoothly.
Need to check out MSSQL, Oracle and DB2.

Requesting implementation of mixed inheritance mapping (class table inheritance and single table inheritance).

This would be especially handy when the difference between certain classes is only "implementational" (i.e. a subclass only functions differently/implements abstract methods and does not specify any additional fields). Using class table inheritance would result in tables only containing an id column.

This is basically a duplicate of another issue I stumbled across lately but cannot find here again. It added a __clone() function to the ORM QueryBuilder to allow this use case:
Create a base query and derive two different queries from it.

I adopted the code for the DBAL QueryBuilder which is suffering the same issue (e.g. expressions were not cloned but shared between instances). The code is tested at least for my limited use case.

/**

• Deep clone of all expression objects in the SQL parts.
  *
• @return void
  */
  public function __clone()
  {
  foreach ($this->sqlParts as $part => $elements) {
  if (is_array($this->sqlParts[$part])) {
  foreach ($this->sqlParts[$part] as $idx => $element)
  Unknown macro: { if (is_object($element)) { $this->sqlParts[$part][$idx] = clone $element; } }

  } else if (is_object($elements))

  { $this->sqlParts[$part] = clone $elements; }

  }

$params = array();

foreach ($this->params as $param)

$this->params = $params;
}

The LIKE and IS NULL operators are not supported in HAVING clause.

Work:
SELECT _a.id, count(_photos) as uuuu FROM Acme\CoreBundle\Entity\Member _a LEFT JOIN _a.photos _photos GROUP BY _a HAVING uuuu in (3,6)
SELECT _a.id, count(_photos) as uuuu FROM Acme\CoreBundle\Entity\Member _a LEFT JOIN _a.photos _photos GROUP BY _a HAVING uuuu = 3
SELECT _a.id, count(_photos) as uuuu FROM Acme\CoreBundle\Entity\Member _a LEFT JOIN _a.photos _photos GROUP BY _a HAVING uuuu >= 3
...

Don't work:
SELECT _a.id, count(_photos) as uuuu FROM Acme\CoreBundle\Entity\Member _a LEFT JOIN _a.photos _photos GROUP BY _a HAVING uuuu LIKE 3
SELECT _a.id, count(_photos) as uuuu FROM Acme\CoreBundle\Entity\Member _a LEFT JOIN _a.photos _photos GROUP BY _a HAVING uuuu IS NULL
SELECT _a.id, count(_photos) as uuuu FROM Acme\CoreBundle\Entity\Member _a LEFT JOIN _a.photos _photos GROUP BY _a HAVING uuuu IS NOT NULL

I tried to create inheritance entities with save policy table per class.
Simple fileds was created normally, but a field with ManyToOne type was lost.

I had found a solution.

In Doctrine\ORM\Tools\SchemaTool
...

private function _gatherRelationsSql($class, $table, $schema)
    {
        foreach ($class->associationMappings as $fieldName => $mapping) {

           // if (isset($mapping['inherited'])) { // - old version

	/**
             * SSW
             * It's the solution
             */
	if (isset($mapping['inherited']) && !$class->isInheritanceTypeNone() && !$class->isInheritanceTypeTablePerClass() ) {
                continue;
            }            

            $foreignClass = $this->_em->getClassMetadata($mapping['targetEntity']);
...

But it was enough. In DQL query a simple query was made wrong.

I had found a solution again.
In Doctrine\ORM\Query\SqlWalker
...

public function walkSelectExpression($selectExpression)
...

                // original => if (isset($mapping['inherited'])){
                // It's the solution
                if (isset($mapping['inherited']) && !$class->isInheritanceTypeNone() && !$class->isInheritanceTypeTablePerClass()) {
                    $tableName = $this->_em->getClassMetadata($mapping['inherited'])->table['name'];
                } else {
                    $tableName = $class->table['name'];
                }
...

This problems are topical for inheritance type: INHERITANCE_TYPE_NONE and INHERITANCE_TYPE_TABLE_PER_CLASS.

I don't know, may be my solutions are wrong. But some programmers want to correctly work with INHERITANCE_TYPE_TABLE_PER_CLASS.

Sorry for my english.

I'm having big troubles while developing a quietly advanced DQL query for a tiny DMS: The schema: DmsObject is a superclass for which two subclasses exist (document and folder) UserRights and GroupRight (which are associative entities in the db, pointing respectively to user and group tables). User and Group represent (obvious) the dms "actors".

SELECT o, ur, gr 
from module\EDMS\business\DmsObject o 
join o.userRights ur 
join o.groupRights gr
WHERE o.ownerUser=ur.user
AND o.ownerGroup=gr.group

The WHERE condition is WRONG! Doctrine switches the two tables. I've already checked the mapping (it's ok!) and checked also where the fk's point in the database (ok!).

...
LEFT JOIN dms_folder d1_ 
    ON d0_.id = d1_.id 
LEFT JOIN dms_document d2_ 
    ON d0_.id = d2_.id 
INNER JOIN dms_user_object_rights d3_ 
    ON d0_.id = d3_.document_id 
INNER JOIN dms_group_object_rights d4_ 
    ON d0_.id = d4_.document_id 
WHERE d0_.sys_group_owner = d3_.user_id 
    AND d0_.sys_user_owner = d4_.group_id
...

This seems to be a bug in the DQL translator.

This request is in followup to my doctrine-user message "Doctrine 2.0: Nested joins'.
I am a bit surprised by the responses in that defining precedences in joins by placing parenthesis around join expressions is not well-known. Although not in the original SQL92 specification it is a major and important feature offered by all the RDBMS's that Doctrine 2 supports, and oftenly performs better than using subselects or alike. Doctrine 1 did not support it, but imho Doctrine 2 should support it to be a mature allround ORM.

As a short example the following is a SQL statement with a nested join, where the nesting is absolutely necessary to return only a's together with either both b's and c's or no b's and c's at all:

SELECT *
FROM a A
LEFT JOIN (
b B
INNER JOIN c C ON C.b_id = B.id
) ON B.a_id = A.id

In order for Doctrine 2 to support this the BNF should be something like:
Join ::= ["LEFT" ["OUTER"] | "INNER"] "JOIN" ( "(" JoinAssociationPathExpression ["AS"] AliasIdentificationVariable Join ")" | JoinAssociationPathExpression ["AS"] AliasIdentificationVariable ) [("ON" | "WITH") ConditionalExpression]
instead of the current:
Join ::= ["LEFT" ["OUTER"] | "INNER"] "JOIN" JoinAssociationPathExpression ["AS"] AliasIdentificationVariable [("ON" | "WITH") ConditionalExpression]

This would allow DQL like:

SELECT A, B, C
FROM a A
LEFT JOIN (
A.b B
INNER JOIN B.c C
) WITH B.something = 'value' AND C.something = 'othervalue'

What further needs to be done is that the DQL parser loosly couples the ConditionalExpression to any of the previously parsed JoinAssociationPathExpression's instead of tieing it explicitely to the JoinAssociationPathExpression that preceedes it according to the old BNF notation. The new BNF should however not require any changes to the hydrator. Therefore I have the feeling that improving the DQL parser for nested joins does not require extensive work, while the benefit of running these kind of queries is considerable.

As an extra substantiation here are links to (BNF) FROM clause documentations of the RDBMS's that Doctrine 2 supports, they all show support for nested joins:
MySQL: http://dev.mysql.com/doc/refman/5.0/en/join.html
PostgreSQL: http://www.postgresql.org/docs/8.4/interactive/sql-select.html#SQL-FROM and http://www.postgresql.org/docs/8.1/interactive/explicit-joins.html
MSSQL: http://msdn.microsoft.com/en-us/library/ms177634.aspx
Oracle: http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/statements_10002.htm#CHDDCHGF
SQLite: http://www.sqlite.org/syntaxdiagrams.html#single-source

I surely hope you will consider implementing this improvement because it would save me and others from the hassle of writing raw SQL queries or executing multiple (thus slow) queries in DQL for doing the same. Thanks anyway for the great product so far!

Custom tree walkers have freedom in modifying the AST but when you try to add a new query component (i.e. new join in walkSelectStatement() ) to the AST then the SqlWalker throws an exception because it does not has the new component in its _queryComponents array. I see two possible ways to resolve this:
1. Modify the Parser class in order to allow tree walkers to modify queryComponents and pass changed queryComponents to the SqlWalker
2. Improve SqlWalker so it can extract and prepare needed information about queryComponent based on AST when it does not have them.

Hi
In DQL when i use NULL IN "CASE WHEN" like this
"AVG(CASE WHEN g.speed > 0 THEN g.speed ELSE NULL END)"
Throw this expestion
Unexpected 'NULL'

Given a simple Joined Table Inheritance like following:

/**
 * @Entity @Table(name="foo") @InheritanceType("JOINED")
 * @DiscriminatorColumn(name="discr", type="string")
 * @DiscriminatorMap({"foo" = "DDC2452Foo", "bar" = "DDC2452Bar"})
 */
class DDC2452Foo
{
    /** @Id @Column(type="integer") @GeneratedValue */
    public $id;
}

/** @Entity @Table(name="bar") */
class DDC2452Bar extends DDC2452Foo
{
}

Following DQL

SELECT foo1 FROM DDC2452Foo foo1 JOIN DDC2452Foo foo2 WITH 1=1

Will produce broken SQL:

SELECT
    f0_.id AS id0, f0_.discr AS discr1 
FROM 
    foo f0_ 
LEFT JOIN bar b1_ 
    ON f0_.id = b1_.id 
LEFT JOIN foo f2_ 
LEFT JOIN bar b3_ 
    ON f2_.id = b3_.id 
    ON (1 = 1)

(please note the duplicate `ON` in the SQL)

That is caused because of the SQL walker producing the JTI filter with already the `ON` clause in it.

That happens because the JTI join conditions are added in https://github.com/doctrine/doctrine2/blob/2.4.0-BETA2/lib/Doctrine/ORM/Query/SqlWalker.php#L823-L825 (`walkRangeVariableDeclaration`), while the additional defined `WITH` conditions are considered in `walkJoinAssociationDeclaration` later on.

Added a test case and fix at https://github.com/doctrine/doctrine2/pull/668

Currently, using MEMBER OF for a ManyToMany collection does a join on the table of the related entity, whereas all it needs is in the join table.

Using the following DQL:

SELECT p FROM Player p
WHERE NOT :team MEMBER OF p.targetedBy

Here is the current generated SQL:

WHERE NOT EXISTS (SELECT 1 FROM player_team p1_ INNER JOIN Team t2_ ON p1_.team_id = t2_.id WHERE p1_.player_id = p0_.id AND t2_.id = ?)

whereas it could drop the join:

WHERE NOT EXISTS (SELECT 1 FROM player_team p1_ WHERE p1_.player_id = p0_.id AND p1_.team_id = ?)

Hi all,

in a big project if you have queries spread out in different
repositories,
when you have to modify a cache lifetime, you have to search the query and
modify the code, than test it.
Is not so easy also to answer to 'how much is the cache for the query XYZ?'

the idea:
Each group of repository (bundle) should have in a single point maybe into its config file a place where you could set the lifetime of the various queries.

see the code for a better explanation
https://gist.github.com/3075742

the pro: a better handling of the cache mechanism
cons: ?

Do you think is a good approach?
Have you ever had a similar problem?

Thanks

liuggio

When using a LIKE condition in a WHERE clause, I would like to be able to use the foreign key of a single value association. For example:

Imagine Product and Department are both entities. Department has the field Name with a unique index. Product is has a ManyToOne association called Department with the Department entity, referencing the field Name.

I would like to write:
SELECT FROM Product p WHERE p.Department LIKE '% Tools'

However, at present I need to write:
SELECT FROM Product p
JOIN p.Department d
WHERE d.name LIKE '% Tools'

The issue is one of performance. On large record sets the first query runs several magnatudes more quickly than the second, particularly when four or five joins are involved.

Looking at the DQL grammar from the online docs, the relveant lines are:

LikeExpression ::= StringExpression ["NOT"] "LIKE" string ["ESCAPE" char]
StringExpression ::= StringPrimary | "(" Subselect ")"
StringPrimary ::= StateFieldPathExpression | string | InputParameter | FunctionsReturningStrings | AggregateExpression

The problem is the StateFieldPathExpression in StringPrimary. When used with a LikeExpression a SingleValuedAssociationField should be allowed also.

When executing partial DQL queries it may be important to keep given order of columns e.g. for "pairs" hydrator when first column of a pair is used as a key and second - as value. For example query like this:

select partial u.{id,name} from my:User u

will expect "id" to be first in resulted set and "name" to be second and not vice versa.

However Doctrine parses this part of statement via iterating over fields mapping from entity's class metadata (as can be seen in Doctrine\ORM\Query\SqlWalker::walkSelectExpression()):

foreach ($class->fieldMappings as $fieldName => $mapping) {
  if ($partialFieldSet && ! in_array($fieldName, $partialFieldSet)) {
    continue;
  }
  ...

and hence given columns order preserving is not guaranteed.

PDO supports grouping by a field when doing a fetchAll() by using PDO::FETCH_GROUP. This can in many places be useful. http://blog.stealth35.com/2011/08/17/pdo-fetch-group.html desribes the behavior quite well.

Hi! Let me start by saying you guys did a great job with Doctrine 1 and that I can't wait to start using Doctrine2

I will explain this feature request with an example. I have a User entity wich relates one to many to a Picture entity. Picture has a " is main picture" boolean field. Not all users have a main picture. I would like to be able to select all Users, each with their main picutre, if that exists, or some Null value, if it does not exists, in one query, using join. I would also like for the result collection to contain Picture entities on the first level, with the User beinng accessible as an aggregate of Picture.

The way I can think doing this is by using a RIGHT or LEFT join (not INNER) as to also select Users that don't have a main picture. I can do this by selecting

SELECT Picture p, p.User u FROM p RIGHT JOIN u WITH p.main=1

but right joins afik are not available atm in either version of Doctrine, or by selecting

SELECT User u, u.Picture p FROM u LEFT JOIN p WITH p.main=1

and somehow instructing the hydrator to consider Picture as the root object for the generated object tree and User as a "child" of Picture.

For users without a picture, the Picture object would somehow indicate it is NULL, while still holding a refference to the User.

Makes sense? If there is an alternate way to achieve this, please enlighten me, tough I think it would still add felxibility if we could hint the hydrator for the root object in a tree.

Setting such a query hint to TRUE should result in all entities being retrieved by that query to be read-only for the purposes of change-tracking. Note that the entities themselves need not necessarily be read-only in general.

This feature is a flush performance tweak that can be used to query for objects but not let the returned objects run through change-tracking on flush. Any other managed objects are tracked as usual so you can do a read-only query for 100 entities and persist a new entity in the same unit of work with optimal flushing performance.

Currently ID hash is generated using implode function with space character. I would like to point out that problems may raise if the ID column values are allowed to contain the space themselves - different objects could return equal ID hash values.

The trivial one-line solution would be to serialize the array instead. Cheaper solution from performance perspective would be escaping the space character for ID values.