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

I don't want you to report something that isn't a bug.
But I read about problems with doctrine2 and mssql uniqueid's.
So First I asked a question at stackoverflow. No one could help me, and the only one who gave me a comment thought the same then me, that it looks like a bug.

If it isn't a bug I'm very sorry for this issue report.

Issue as reported in
http://stackoverflow.com/questions/15368082/refresh-uniqueidentifier-id-from-mssql-of-inserted-entity-in-doctrine2

When using the CASE WHEN expression in DQL a Lexer error is thrown if the THEN condition uses a FunctionsReturningStrings function. For example, the following query is valid in SQL:

SELECT t.*, CASE WHEN LENGTH(t.myfield) <> 0 THEN CONCAT(t.myfield, t.myfield2) ELSE t.myfield2 END as mycasefield FROM mytable AS t

However, if the CONCAT function is used in DQL the exception is raised.

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

Hi,

I have stumbled on a problem were querying the same data with different methods (findBy and DQL) retrieves different results.

I have extended the Doctrine\DBAL\Types\DateTimeType with my own type
BVD\PetroleumBundle\DoctrineExtensions\DBAL\Types\UTCDateTimeType,

which I attach.
I expect, that whenever we deal with an entity that has a property of this type, both convertToDatabaseValue() (whenever we access the DB, conversion from DateTime to a string) and convertToPHPValue() (whenever the result from DB query is returned back, conversion from string to DateTime) have to be executed.

It is important, as the single purpose of convertToDatabaseValue() is to perform conversion of the incoming DateTime to the UTC timezone prior to conversion to string,
and then whenever we convert the DB value to DateTime to set it's timezone not to default value (server local timezone), but to UTC (as the values are stored in UTC).

Example:
Query:
$entity = $em->getRepository('BVDPetroleumBundle:FuelCardTransaction')->findOneBy(array('id' => $id, 'processed_datetime' => new \DateTime('2011-03-10 23:58:37')));

as you see, DateTime object is created without DateTimeZone set, which makes it employ the server's default timezone (say EST).

Entity has this property registered as:
/**

• @ORM\Column(type="utcdatetime")
  */
  public $processed_datetime;

And this datatype is registered with Doctrine through Symfony2 configuration:
doctrine:
dbal:
types:
utcdatetime: BVD\PetroleumBundle\DoctrineExtensions\DBAL\Types\UTCDateTimeType

Whenever I query the DB, prior to SQL generation, DateTime is getting converted to UTC by UTCDateTimeType#convertToDatabaseValue(), and becomes:
'2011-03-10 23:58:37' (EST) -> '2011-03-11 04:58:37' (UTC).
Then, whenever the object is retrieved back, I expect that UTCDateTimeType#convertToPHPValue() is used to set the correct timezone information
on the created DateTime object: '2011-03-11 04:58:37' (UTC).
This is the correct behaviour that is expected, and is correctly achieved by using findBy methods to retrieve data:

$entity = $em->getRepository('BVDPetroleumBundle:FuelCardTransaction')->findOneBy(array('id' => $id, 'processed_datetime' => new \DateTime('2011-03-10 23:58:37')));

But, when the DQL is used to issue the same query:

$queryBuilder = $em->createQueryBuilder()>select('a')>from('BVDPetroleumBundle:FuelCardTransaction','a')
->where('a.id = :transaction_id')
->andWhere("a.processed_datetime = :datetime")
->setParameter('transaction_id', $id)
->setParameter("datetime", new \DateTime('2011-03-10 23:58:37'));
$entity = $queryBuilder->getQuery()->getOneOrNullResult();

Doctrine\DBAL\Types\DateTimeType#convertToDatabaseValue() is getting executed for 'processed_datetime', instead of
BVD\PetroleumBundle\DoctrineExtensions\DBAL\Types\UTCDateTimeType,

and the conversion doesn't happen, so the query doesn't return the result, that really exists in DB.

I attach two methods traces, so it's easier to identify the problem: whenever the findBy is used, and whenever the DQL is used.
I have managed to trace it to the way how both methods retrieve their $types arrays.

The reason it succeeds when used with findBy methods:
Doctrine\ORM\Persisters\BasicEntityPersister#load() is used to retrieve the data.
The $types property that holds the type information ('utcdatetime') is formed by calling
BasicEntityPersister#expandParameters($criteria), and in the process of analyzing incoming parameters it queries the entity metadata (@ORM\Column(type="utcdatetime")),
stored in BasicEntityPersister#$_class property. (method BasicEntityPersister#getType())
Then it's able to match type 'utcdatetime' to class BVD\PetroleumBundle\DoctrineExtensions\DBAL\Types\UTCDateTimeType

The reason it fails with DQL:
It seems that with DQL, it doesn't query the entity metadata (@ORM\Column(type="utcdatetime")) to derive property type. This mechanism leads the type to be recognized as simply 'datetime', and the standard handler Doctrine\DBAL\Types\DateTimeType is used instead:

The $types (which has 'datetime' instead of 'utcdatetime') array is getting formed in
Doctrine\ORM\Query#_doExecute():
list($sqlParams, $types) = $this->processParameterMappings($paramMappings);

in Doctrine\ORM\Query#processParameterMappings($paramMappings)
Doctrine\ORM\Query#processParameterValue($parameter->getValue()) is called to convert parameter from Object to string.

in Doctrine\ORM\AbstractQuery#processParameterValue($value) for object of class DateTime I would expect this to be executed:
case is_object($value) && $this->_em->getMetadataFactory()->hasMetadataFor(ClassUtils::getClass($value)):
return $this->convertObjectParameterToScalarValue($value);

but it's not, and the DateTime is returned out of it, and in Doctrine\ORM\Query\processParameterMappings $type is getting set to $parameter->getType() ('datetime')

Please confirm/contradict the issue. Right now for workaround, whenever I use DQL, have to explicitly set the timezone of DateTime prior to issuing a query.

From Russia with love,
Slavik

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()).

DQL Expression:

SELECT (CASE WHEN t.id = 1 THEN -1 ELSE t.id END) FROM Table t

Gives an error:
[Syntax Error] line 0, col 32: Error: Unexpected '-'

It doesn't seem to like the negative number.

The sample query (I want to bring a specific item to the top of the list).
mysql> select * from Language order by name='English' desc, name asc limit 5;
------------+

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

------------+
5 rows in set (0.00 sec)

In theory, the code below should generate the same query.
$repository->createQueryBuilder('p')
->addOrderBy("p.name='english'", 'desc')
->addOrderBy('p.name', 'asc');

In practice, an exception is thrown.
Doctrine\ORM\Query\QueryException: [Syntax Error] line 0, col 67: Error: Expected end of string, got '=' (uncaught exception) at /vendor/doctrine/orm/lib/Doctrine/ORM/Query/QueryException.php line 44

Attached, SQL dump for the table & data

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.

I have a problem when I get database records through a query DQL
and then they are changed by another application
If I repeat the query, Doctrine always return the first value, not the current value of the base

<?php

// bootstrap

$cache = new \Doctrine\Common\Cache\ArrayCache();
$config = new Doctrine\ORM\Configuration();
$config->setQueryCacheImpl($cache);
$conn = array(
				'dbname' => $database_name,
				'user' => $cnx_user,
				'password' => $cnx_pass,
				'host' => $cnx_host,
				'driver' => $cnx_type,
				'charset' => 'utf8',
				'driverOptions' => array( 1002 => "SET NAMES 'utf8'" )
				);

			$em = Doctrine\ORM\EntityManager::create($conn, $config);

while(true){
   $dql = "SELECT s from Register s WHERE s.id = 1";
   $query = $em->createQuery($dql);
// the next line is optional, produces same result
   $query->useResultCache(false);
   $res = $query->getResult();
   $orm = reset($res);
   	
   echo " regiter id :".$orm->getId()."  field "$orm->getText()."\n";

}

I run this code in a terminal, and then edit the registry (field text), but the terminal still shows the same result

First off, here's a pastie with all the code and mappings involved, and a stacktrace: http://pastie.org/5372087

Not sure if this a bug or not, but I think that according to the docs this should work - in a vendor bundle I have an entity defining a unidirectional many-to-many relation to an interface: the entity is Group and defines a many-to-many relation to a UserInterface, which is resolved correctly in the app configuration (as the many-to-one relations using it in the other entities of this vendor bundle work fine).

I'm going by these docs:

http://symfony.com/doc/master/cookbook/doctrine/resolve_target_entity.html

However, when adding a user to a group, I'm getting a weird error:

An exception occurred while executing 'INSERT INTO acme_group_user (group_id, user_id) VALUES (?, ?)' with params

:

SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens

For some reason it's adding 4 parameters to the statement instead of 2.

Oddly enough, the query generation works perfectly fine when I replace the interface in the mapping with the actual final entity. But that wasn't the point of the exercise... I need to use the interface there.

Also, in other entities in that GroupBundle which are referencing the UserInterface using a different relation type, i.e. many-to-one, the relations work perfectly fine.

The schema validates as well, using doctrine:schema:validate in the console.

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 using Doctrine with Symfony 2.1. Everything was working OK in Symfony 2.0 so I think I was using Doctrine 2.2. Now I'm using Doctrine 2.3-dev (packagist) and when I run the following query in a EntityRepository:

$em->createQuery('SELECT u, tc FROM XBundle:X tc JOIN tc.usuario u JOIN tc.tax t WHERE tc.timestamp >= :date_from AND tc.timestamp <= :date_to AND t.type = :x GROUP BY tc.usuario');

Nothing happens, getResult() just returns array(0){}

I've done \Doctrine\Common\Util\Debug::dump($query); and this is the result:

object(stdClass)#487 (18) {
  ["__CLASS__"]=>
  string(18) "Doctrine\ORM\Query"
  ["_state"]=>
  int(1)
  ["_dql"]=>
  string(180) "SELECT u, tc FROM XBundle:X tc JOIN tc.usuario u JOIN tc.tax t WHERE tc.timestamp >= :date_from AND tc.timestamp Marcos:project marcos$ 

As you can see something happens because the dql query got cropped and seems like Doctrine is not working properly...

If I change 'WHERE tc.timestamp >= :date_from AND tc.timestamp <= :date_to' for 'WHERE tc.timestamp BETWEEN :date_from AND :date_to', then the query is run without any problem and the debug dump function shows a complete debug of $query.

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 = ?)

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.

I have the following object hierarchy:

Clazz A with Joined Table Inheritance
Clazz B extends A
Clazz C extends A with an association (n:1) to D
Clazz D

I am doing a DQL query 'SELECT u FROM A u'

If I just have As and Bs in the DB everything is fine. But I there is a C in there I get the following error:

Notice: Trying to get property of non-object in X:\Zend_Workspace\goalio_application\vendor\doctrine\orm\lib\Doctrine\ORM\Internal\Hydration\ObjectHydrator.php on line 479

Fatal error: Call to a member function fetch() on a non-object in X:\Zend_Workspace\goalio_application\vendor\doctrine\orm\lib\Doctrine\ORM\Internal\Hydration\ObjectHydrator.php on line 148

When I debug the _rsm of the Hydrator is null when trying to hydrate the row for C.

Any ideas?

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