[DDC-3281] Error: Expected Doctrine\ORM\Query\Lexer::T_CLOSE_PARENTHESIS, got 'S' Created: 28/Aug/14  Updated: 29/Aug/14  Resolved: 28/Aug/14

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: None
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: David Soussan Assignee: Marco Pivetta
Resolution: Invalid Votes: 0
Labels: dql, orm
Environment:

Windows 7, WAMPP, PHP 5.4, Symfony 2.3.3, SonataAdminBundle


Attachments: File composer.lock    

 Description   

Twig_Error_Runtime: An exception has been thrown during the rendering of a template ("[Syntax Error] line 0, col 545: Error: Expected Doctrine\ORM\Query\Lexer::T_CLOSE_PARENTHESIS, got 'S'") in SonataAdminBundle:CRUD:base_list.html.twig at line 33.

QueryException: SELECT o FROM TLF\PortalBundle\Entity\Company o WHERE o.name IN ('EWEN MACRAE (WEST END GARAGE) LIMITED','F.A.M. ENGINEERING LTD','FIFE ACCIDENT REPAIR CENTRE LIMITED','FORREST RESCUE','FOURWINDS GARAGE','FRED HENDERSON LIMITED','FURNESS CARS & COMMERCIALS LTD','FYLINGDALES SERVICE STATION','G BANNERMAN (TAIN) LIMITED','GALLOWS WOOD SERVICE STATION LIMITED','GLENDINNING BROS.','GLENGYLE GARAGE LTD','GRAHAMS VEHICLE REPAIRS','GREENPARK GARAGE LIMITED','GRIFFIN`S RESCUE LIMITED','GWALIA RECOVERY LIMITED','H K MOTORS (WALES) LIMITED','HARDY\'S RECOVERY LIMITED','HIGHFIELD GARAGE & RECOVERY LIMITED','HINTON RESCUE','HORNE PARK GARAGE LIMITED','Independent Inspections','J & J CAMPSIE LIMITED','J D MACADAM & SON (RESCUE) LIMITED','J.H HENDERSON & SONS LIMITED') ORDER BY o.name ASC

This error is generated by the SonataAdminBundle when fetching entities for display in the admin list. The query is generated by the bundle and one can see the escape character in the query: ,'HARDY\'S RECOVERY LIMITED', but the Lexer is not escaping the apostrophe but rather sees it as the string terminator. Hence the error.

My composer.lock is attached.



 Comments   
Comment by Christophe Coevoet [ 28/Aug/14 ]

Quote escaping in DQL strings is done by doubling the quote, not by prepending a backslash. So you are not escaping it

Comment by David Soussan [ 29/Aug/14 ]

Thanks Chris. Since this DQL is produced by SonataAdminBundle, not me, then this is a bug in that bundle and I will raise it with them.





[DDC-3215] wrong quotation Created: 16/Jul/14  Updated: 17/Jul/14

Status: Open
Project: Doctrine 2 - ORM
Component/s: DQL, ORM
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: revrev Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: dql, orm


 Description   

when doctrine build query´s, for example when you doing

$entitity->getTest()->clear();

following queries are generated (test_id is integer in mysql):

DELETE FROM test WHERE test_id = '6'

Is this right?
For me the right query would be:

DELETE FROM test WHERE test_id = 6

as in http://dev.mysql.com/doc/refman/5.5/en/type-conversion.html
6 will be converted to float, this can be an issue, or?

Comparisons that use floating-point numbers (or values that are converted to floating-point numbers) are approximate because such numbers are inexact. This might lead to results that appear inconsistent:

mysql> SELECT '18015376320243458' = 18015376320243458;
        -> 1
mysql> SELECT '18015376320243459' = 18015376320243459;
        -> 0

this also happens in dql sometimes, why doctrine does this not automatic right due to description in the entities?

     /**
     * @ORM\Id @ORM\Column(type="integer")
     * @ORM\GeneratedValue
     */


 Comments   
Comment by Marco Pivetta [ 16/Jul/14 ]

Could you please convert this to a failing test case? Doctrine doesn't quote integers as strings by default.

Comment by revrev [ 16/Jul/14 ]

i try to describe what i have done

i have an Entity with:

    /**
     * @ORM\ManyToMany(targetEntity="Messen", inversedBy="vertrag_messen")
     * @ORM\JoinTable(name="vertrag_messen")
     **/
    private $vertrag_messen;

    public function __construct()
    {
        $this->vertrag_messen = new \Doctrine\Common\Collections\ArrayCollection();
    }

    public function getMessen()
    {
        return $this->vertrag_messen;
    }

when i now clear the Data

$entitity->getMessen()->clear();

following query is created
DELETE FROM vertrag_messen where messe_id = '6'

Should here not set the value 6 as integer (param_int) (DELETE FROM test vertrag_messen where messe_id = 6) that mysql doesn´t have to cast the value? (http://dev.mysql.com/doc/refman/5.5/en/type-conversion.html) or is this not an problem?

Comment by Marco Pivetta [ 16/Jul/14 ]

is messe_id in your entity an integer or a string at the moment in time when that query is being executed?

Comment by revrev [ 16/Jul/14 ]

the value comes automatic
$entitity = $em->getRepository('Base\Entities\Vertrag')>find(intval($data["id"]));

i don´t set messe_id here

Comment by Marco Pivetta [ 16/Jul/14 ]

Can you var_dump the Base\Entities\Messe instance?

Comment by revrev [ 16/Jul/14 ]

object(stdClass)#1014 (64) {
["__CLASS__"]=>
string(24) "Base\Entities\Vertrag"
["id"]=>
int(6)
[„vertrag_messen"]=>
array(1)

Unknown macro: { [0]=> string(20) "BaseEntitiesMessen" }


["erstellungsdatum"]=>
object(stdClass)#1210 (3)

Unknown macro: { ["__CLASS__"]=> string(8) "DateTime" ["date"]=> string(25) "2013-09-28T00}

["zeitraumvon"]=>
NULL
["zeitraumbis"]=>
NULL
["jahr"]=>
int(2014)
["created"]=>
object(stdClass)#1178 (3)

Unknown macro: { ["__CLASS__"]=> string(8) "DateTime" ["date"]=> string(25) "2013-09-28T19}

["updated"]=>
object(stdClass)#1177 (3)

Unknown macro: { ["__CLASS__"]=> string(8) "DateTime" ["date"]=> string(25) "2014-07-16T17}

["uuid"]=>
string(36) "52470c58-4288-45eb-b75f-0c41c0a81437"
}

Comment by Marco Pivetta [ 17/Jul/14 ]

yeah, integer identifier there.
Could you verify if the problem also comes up with current master? I think this issue is related with another one that was fixed some months ago in 2.5.x-dev





[DDC-3196] Enabling LIMIT resp. setMaxResults on subquery Created: 19/May/14  Updated: 26/Jun/14  Resolved: 26/Jun/14

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: None
Affects Version/s: 2.4.2
Fix Version/s: None
Security Level: All

Type: Improvement Priority: Major
Reporter: Webdevilopers Assignee: Marco Pivetta
Resolution: Won't Fix Votes: 1
Labels: dql, limit, setMaxResults, subquery
Environment:

PHP, Zend Framework2, DoctrineModule, DoctrineORMModule



 Description   

The following subquery using LIMIT 1 is legal SQL:

(SELECT state FROM contract_states 
WHERE contract_states.contract_id = contracts.id
ORDER BY date DESC, created_at DESC, id DESC LIMIT 1) = ?

I tried to get the same result in DQL using the queryBuilder:

	$qb2 = $this->_em->createQueryBuilder();
    	$qb2->select(array('s2.state'))
    		->from('Application\Entity\ContractState', 's2')
    		->where('s2.contract = c.id')
    		->orderBy('s2.date', 'DESC')
    		->addOrderBy('s2.createdAt', 'DESC')
    		->addOrderBy('s2.id', 'DESC')
    		->setMaxResults(1);
$stateDql = $qb2->getDQL();

or directely via DQL:

    	$stateDql = 'SELECT s2.state FROM Application\Entity\ContractState s2 WHERE s2.contract = c.id
    			ORDER BY s2.date DESC, s2.createdAt DESC, s2.id DESC LIMIT 1';

The DQL was inserted in my query:

	    $qb->select(
	    	array(
	    		'DISTINCT(c.id) AS contract_id',
	    		$qb->expr()->max('s.createdAt') . ' AS state_updated',
	    		's.createdAt',
	    		's.state'
			))
	    	->from('Application\Entity\Contract', 'c')
	    	->join('c.states', 's')
->andWhere('s.state = (' . $stateDql . ')');

The first DQL string did not include a LIMIT part. The second one ended with the following error:
Error: Expected Doctrine\ORM\Query\Lexer::T_CLOSE_PARENTHESIS, got 'LIMIT'

Is there a way to achieve it?

I also tried a workaround by using MAX inside my orderBy which could help:

->addOrderBy($qb->expr()->max('s2.createdAt'), 'DESC')

But this throws an error too:
Error: Expected Doctrine\ORM\Query\Lexer::T_CLOSE_PARENTHESIS, got '('

I hope this is the right place to post the issue. I havn't found a similar topic browsing 'subquery' or 'LIMIT' as keyword.



 Comments   
Comment by Christophe Coevoet [ 19/May/14 ]

There is not LIMIT in DQL. the mx result is not part of the DQL string.

However, is limiting a subquery valid in SQL generally or only on some platforms ?

I know that MySQL supports them only in some subqueries but not all places where subqueries can be used: http://dev.mysql.com/doc/refman/5.6/en/subquery-restrictions.html

Comment by Webdevilopers [ 19/May/14 ]

Thanks for the quick reply.

I think it is not valid on correlated subquery and using them inside the select statement.

The subquery mentioned above is used inside the where statement and will return a legal result in the current MySQL version.

Maybe there is another workaround? As I described setting a max expression in the order clause did not work out either.

Comment by Christophe Coevoet [ 19/May/14 ]

OK, but what about other platforms ? If only MySQL support this, we cannot integrate the feature in DQL

Comment by Webdevilopers [ 20/May/14 ]

I can't speak for other platforms but I will do some research.
Maybe some experts will comment too.
Thanks so far.

Comment by Webdevilopers [ 21/May/14 ]

While I am doing some research can somebody tell if it s possible to male DQL accept the LIMIT by creating a custom extension for the Lexer? E.g.:
https://github.com/beberlei/DoctrineExtensions/blob/master/lib/DoctrineExtensions/Query/Mysql/GroupConcat.php

Comment by Marco Pivetta [ 26/Jun/14 ]

The parser is not really designed for extensions.

Additionally, as Christophe Coevoet said, it's not really possible to support LIMIT in all platforms.





[DDC-3159] CONCAT expression for PostGreSql Created: 10/Jun/14  Updated: 10/Jun/14

Status: Open
Project: Doctrine 2 - ORM
Component/s: DQL
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: Improvement Priority: Minor
Reporter: Maxime Colin Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: concat, dql, postgresql
Environment:

PostGreSQL



 Description   

For PostGreSQL, the CONCAT DQL function is translated in concatenation with || operator (which is the default behavior in AbstractPlatform class).

Is there a particular reason to not use the CONCAT PostGreSQL function instead like in MySqlPlatform ?

I ask this cause the concatenation with || operator return null if one of the part is null, whereas CONCAT function will simply ignore null values.






[DDC-3121] I resolved issue on date format with some exotics MSSQL server Created: 12/May/14  Updated: 12/May/14  Resolved: 12/May/14

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: DQL, ORM
Affects Version/s: 2.x
Fix Version/s: None
Security Level: All

Type: Improvement Priority: Major
Reporter: Gasc Assignee: Marco Pivetta
Resolution: Invalid Votes: 0
Labels: dql, orm


 Description   

A good way to resolve date issue on mssql is to use "SET DATEFORMAT" before each query. So I have done a quick and simple fix in our doctrine-dbal>Connection.php to get rid of the issue:

    private function patch_date( &$query ) {
        $driver = print_r($this->getDriver(), true);
        //SI EN MSSQL
        if ( stripos($driver, 'Sqlsrv') > 0 ) {
            $query = 'SET DATEFORMAT ymd;'.$query;
        }
    }

    public function prepare( $statement ) {
        $this->connect();
        $this->patch_date($statement);
        return new Statement($statement, $this);
    }

    public function executeUpdate( $query, array $params = array(), array $types = array() ) {
        $this->connect();
        $this->patch_date($query);

Simple and no need to manage it anymore.
As I work on several MSSQL date formated server: it's very usefull.



 Comments   
Comment by Marco Pivetta [ 12/May/14 ]

This should be implemented as a listener like https://github.com/doctrine/dbal/blob/9d6300fd862478f58a526fbcad0a8f630daa60aa/lib/Doctrine/DBAL/Event/Listeners/OracleSessionInit.php

Comment by Steve Müller [ 12/May/14 ]

What exact date issue are you refering to here?

Comment by Gasc [ 12/May/14 ]

Thank you for the listener.
We have usually no issue on MySQL and MSSQL 2008R2, but date setting were different on one 2008R2 of our customer server.
Here is the error message we got:

SQLSTATE[22007]: [Microsoft][SQL Server Native Client 10.0][SQL Server]La conversion d'un type de données nvarchar en type de données datetime a créé une valeur hors limites.

Just to know: We use Symfony for the project (with doctrine "2.1.0"), and without time to rewrite services, so this tiny little patch saved us full of time.

Comment by Marco Pivetta [ 12/May/14 ]

Yep, this is a non-issue for us, as it is a custom environment, as it seems.

That should be solved with a post-connect listener as I've linked above.

Closing as invalid.





[DDC-3108] Criteria cannot reference a joined tables' fields when used with an ORM QueryBuilder Created: 30/Apr/14  Updated: 30/Apr/14

Status: Open
Project: Doctrine 2 - ORM
Component/s: DQL, ORM
Affects Version/s: 2.4.2
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Chris Rog Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: criteria, dql, orm, querybuilder


 Description   

This regression was introduced in 2.4.2 with the addition of the "rootAlias" stuff. Basically, the hard-coded addition of the rootAlias + "." prevents any Criteria object from referencing any field that isn't on the first table selected.

Example:
// Assume $repo is a valid EntityRepository and $value is some scalar value.
$qb = $repo->createQueryBuilder('T1')->join('T1.field', 'T2');

$criteria = new Comparison('T2.field2', Comparison::EQ, $value);

$qb->addCriteria($criteria);
$dql = $qb->getDQL();

$dql is now (roughly) equal to:
SELECT T1 FROM <entityclass> T1 JOIN T1.field T2 WHERE T1.T2.field2 = <value>

Evaluating this causes QueryExceptions to be thrown; usually something along the lines of "Expected Doctrine\ORM\Query\Lexer::<token>, got '.'"

There's a similar issue involving ordering by a related field for the same reason.






[DDC-3031] ORM does not understand constants in SELECT clause Created: 15/Mar/14  Updated: 15/Mar/14  Resolved: 15/Mar/14

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: ORM
Affects Version/s: 2.4
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Roman Assignee: Benjamin Eberlei
Resolution: Invalid Votes: 0
Labels: dql, orm


 Description   

It seems that doctrine do not understand constants in SELECT clause.
I try to execute such a query:

SELECT e.id, 'abc' as flag FROM SomeEntity

And I get this error:

[Semantical Error] line 0, col 40 near 'parent, p.weight,': Error: Invalid PathExpression. Must be a StateFieldPathExpression.

There was an issue like this a long time ago: DDC-1077. It's written that it was Resolved, but it does not work.



 Comments   
Comment by Roman [ 15/Mar/14 ]

Sorry, that was my mistake. It works fine!





[DDC-3026] Provide DQL TYPE() function to access discriminator column value Created: 12/Mar/14  Updated: 12/Mar/14  Resolved: 12/Mar/14

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: DQL
Affects Version/s: 2.4.2
Fix Version/s: None
Security Level: All

Type: New Feature Priority: Minor
Reporter: Nils Adermann Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: dql


 Description   

It is not currently possible to figure out which type a value is associated with if you do not wish to perform object hydrations. There should be a TYPE() function in DQL which returns the value of the discriminator column or the associated class name, e.g

SELECT u.id, TYPE(u) FROM Some\Base\Class u

which would return

[[1, 'foo'], [2, 'bar']]

if there were two classes foo and bar which inherit from the base class.



 Comments   
Comment by Marco Pivetta [ 12/Mar/14 ]

I'd add that these should be 2 functions:

  • TYPE(e) gives you the class name for a given fetched result
  • DISCRIMINATOR(e) gives you the discriminator value for a given fetched result
Comment by Benjamin Eberlei [ 12/Mar/14 ]

Not an issue or something to add as a new feature. There is no way to get the discriminator value.





[DDC-3018] DQL “NEW” Operator and Literal type "String" Created: 09/Mar/14  Updated: 23/Mar/14  Resolved: 23/Mar/14

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: DQL
Affects Version/s: 2.4, Git Master, 2.5
Fix Version/s: 2.5, 2.4.3
Security Level: All

Type: Bug Priority: Major
Reporter: harleaux Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: dql, orm


 Description   

Hello all,

When i use the DQL operator "new" to build data transfert object with string literal expression as field in object constructor, the call to Query::getResult thrown an exception.

Condition :
The string literal expression must be the first parameter of the constructor.

Following DQL :

$query = $em->createQuery('SELECT NEW CustomerDTO('some scalar string', c.name, c.email) FROM Customer c');

$users = $query->getResult();

Thrown exception :

ContextErrorException: Notice: Undefined variable: fieldType in doctrine/orm/lib/Doctrine/ORM/Query/SqlWalker.php line 1527

That happens because in SqlWalker::walkNewObject on the AST\Literal switch case. There is no case for AST\Literal::STRING, so $fieldType isn't defined.

I have also noted if the scalar string isn't the first parameter, $fieldType take the type of previous foreach element.



 Comments   
Comment by Benjamin Eberlei [ 23/Mar/14 ]

Fixed and merged into 2.4 release branch





[DDC-2923] Query expressions ALL/ANY/SOME don't seem to work Created: 17/Jan/14  Updated: 09/Feb/14  Resolved: 09/Feb/14

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: DQL, ORM
Affects Version/s: 2.3.4
Fix Version/s: None
Security Level: All

Type: Bug Priority: Critical
Reporter: Kees Schepers Assignee: Benjamin Eberlei
Resolution: Invalid Votes: 0
Labels: dql, orm, querybuilder
Environment:

Doctrine 2.3 from a Symfony 2.4 application running on a Ubuntu 12.04 machine onder Apache / PHP.



 Description   

It seems that the query expressions ALL() / ANY() / SOME() don't work (anymore). See my testcase and the error on my stackoverflow question:

http://stackoverflow.com/questions/21184374/doctrine-any-all-some-doesnt-work

The exception is thrown in the Query/Parser.php (line: 3040) since the functions are not mapped in the parser class as string functions.

What goes wrong?



 Comments   
Comment by Marco Pivetta [ 20/Jan/14 ]

Tests seem to cover this use case at https://github.com/doctrine/doctrine2/blob/a83f5df47c463cd64a2525274c97812e04a8eca5/tests/Doctrine/Tests/ORM/Query/LanguageRecognitionTest.php#L418-L426 - try using plain DQL instead of the query builder first - may be a problem with the QB

Comment by Steve Müller [ 20/Jan/14 ]

Kees Schepers You use the expression wrong. ALL/ANY/SOME expression needs another expression to compare against. Something like:

SELECT * FROM foo WHERE ALL(SELECT bar.id FROM bar WHERE bar.id > 100)

is not valid SQL.

What you want is something like:

SELECT * FROM foo WHERE foo.id > ALL(SELECT bar.id FROM bar WHERE bar.id > 100)

I hope you get what I mean, I don't know exactly what you supposed your query to do but I hope you get the point. This is not a Doctrine bug.

See here for an example with the query builder: https://github.com/doctrine/doctrine2/blob/a83f5df47c463cd64a2525274c97812e04a8eca5/tests/Doctrine/Tests/ORM/QueryBuilderTest.php#L716-L729

Comment by Benjamin Eberlei [ 09/Feb/14 ]

Not an issue, the `andWhere($expr->all())` usage is wrong.





[DDC-2870] Doctrine error when using SUM(a.id=1) as `ìdentifier`: Expected Doctrine\ORM\Query\Lexer::T_CLOSE_PARENTHESIS, got '=' Created: 22/Dec/13  Updated: 06/Jan/14

Status: Awaiting Feedback
Project: Doctrine 2 - ORM
Component/s: DQL
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Maxim Geerinck Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: dql
Environment:

Symfony2 bundle



 Description   

Doctrine error when using SUM(a.id=1) as `ìdentifier`: Expected Doctrine\ORM\Query\Lexer::T_CLOSE_PARENTHESIS, got '='

I am trying to execute a query in doctrine that contains something like this

SUM(a.id = 1) as `1`
for some reasons it always gives me the following error:

[Syntax Error] line 0, col 15: Error: Expected Doctrine\ORM\Query\Lexer::T_CLOSE_PARENTHESIS, got '='
This is the code i am using

$result = $em->getRepository('MyBundle:PlayerAction')
->createQueryBuilder('pa')
->select(array(
'SUM(a.id=1) as `1`,
SUM(a.id=2) as `2`,
SUM(a.id=3) as `3`,
p.playerName,
pa.timestamp'
))
->innerJoin('pa.action', 'a')
->innerJoin('pa.player', 'p')
->where('pa.timestamp > ?1')
->groupBy('p')
->setParameter(1, time() - $time)
->orderBy('p.playerName', 'ASC');






[DDC-2836] DQL errors when attempting to use GROUP BY MAX(field) Created: 26/Mar/13  Updated: 03/Dec/13  Resolved: 03/Dec/13

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: None
Affects Version/s: 2.3
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Jon Langevin Assignee: Benjamin Eberlei
Resolution: Invalid Votes: 0
Labels: dql, groupby, max


 Description   

Attempting to run DQL similar to:
SELECT a FROM ClassName a GROUP BY MAX(a.depth)

Throws error:
[Semantical Error] line 0, col 250 near 'MAX(ao.depth)': Error: Cannot group by undefined identification or result variable.

Per docs, MAX is allowed within GROUP BY: http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/dql-doctrine-query-language.html#aggregate-functions

If this error is due to some omission on my part, then perhaps the docs should be extended to show a valid GROUP BY MAX() usage, or the error message expanded for a better hint.



 Comments   
Comment by Marco Pivetta [ 03/Dec/13 ]

Grouping by MAX() is not supported, as MAX() is already an aggregation

Comment by Jon Langevin [ 03/Dec/13 ]

Shame, because that is useful functionality in SQL. So I assume this means the documentation will be correct then?

Comment by Marco Pivetta [ 03/Dec/13 ]

Jon Langevin did you check if the EBNF allows that?

Comment by Jon Langevin [ 03/Dec/13 ]

Per the doc link in my initial report, the docs say that the aggregation functions are allowed in both SELECT and GROUP BY clauses.

The following aggregate functions are allowed in SELECT and GROUP BY clauses: AVG, COUNT, MIN, MAX, SUM

Searching EBNF in same page, one of items supported for GROUP BY is:

/* ResultVariable identifier usage of mapped field aliases (the "total" of "COUNT AS total") */
ResultVariable = identifier

So I assume that means we'd have to move AVG, COUNT, MIN, MAX, SUM into the SELECT statement, using SELECT <function> AS <var>, and then GROUP BY <var> ?

Comment by Marco Pivetta [ 03/Dec/13 ]

Jon Langevin I think the syntax allows that, but you should give it a try. I don't see how that is supposed to work given how GROUP BY works in various RDBMS implementations





[DDC-2795] the queryBuider Expr\Join class has a ON type but unsupported by the parser Created: 14/Nov/13  Updated: 14/Nov/13

Status: Open
Project: Doctrine 2 - ORM
Component/s: Documentation, DQL
Affects Version/s: 2.4.1
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Christophe Coevoet Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: documentation, dql, querybuilder,


 Description   

The Doctrine\ORM\Query\Expr\Join class has 2 cosntants for the condition types: WITH and ON.

None of them are documented. The only place where WITH appear is the EBNF, which is outdated in the doc as it does not show arbitrary joins (added in 2.3) but only association joins.

and when looking at the EBNF in the code, I find 2 different ones (none of them matching the one given in the doc):

  • in Doctrine\ORM\query\Parser::Join:
Join ::= ["LEFT" ["OUTER"] | "INNER"] "JOIN"
         (JoinAssociationDeclaration | RangeVariableDeclaration)
         ["WITH" ConditionalExpression]

This is matching the implementation and ON is not supported.

  • in Doctrine\ORM\Query\AST\Join:
Join ::= ["LEFT" ["OUTER"] | "INNER"] "JOIN" JoinAssociationPathExpression
         ["AS"] AliasIdentificationVariable [("ON" | "WITH") ConditionalExpression]

This one is missing 2 features also missing in the doc (INDEX BY for associations, and arbitrary joins) and adds the support of ON which is not implemented.

What is the reason to have this ON constant in the query builder ? It is confusing to get a DQL parse exception when using it if it is there.

On a side note, what is the canonical source for the EBNF ? There is 2 different locations in the code (the phpdoc of parser methods and the phpdoc of AST nodes created by the parser), plus the doc. Shouldn't we try to limit the duplication and have a way to check the consistency of the doc ?






[DDC-2634] Adding a WITH clause on leftJoin places the JOIN ... AND in the wrong place combined with inheritance mapping Created: 27/Aug/13  Updated: 25/Jan/14  Resolved: 25/Jan/14

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: DQL
Affects Version/s: 2.3.4
Fix Version/s: 2.4.1
Security Level: All

Type: Bug Priority: Major
Reporter: Bram Van der Sype Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: dql, inheritance, leftjoin, orm
Environment:

MySQL 5.6



 Description   

I implement following querybuilder:
$qb = $this->createQueryBuilder('it')
->select('it, i')
->leftJoin('it.items', 'i', 'WITH', 'i.archived = false')
->orderBy('it.position', 'ASC')
;

Note: the result is the same with
return $this->getEntityManager()
->createQuery('
SELECT it, i FROM AcmeDemoBundle:ItemType it
LEFT JOIN it.items i WITH i.archived = 0
ORDER BY it.position ASC')
->getResult();

The Item entity has inheritance mapping to (at the moment) one subclass, ObjectItem. The archived property is on the Item class (not ObjectItem).

The generated query is
SELECT
...
FROM
item_type i0_
LEFT JOIN item i1_ ON i0_.id = i1_.item_type_id
LEFT JOIN object_item o2_ ON i1_.id = o2_.id
AND (i1_.archived = 0)
ORDER BY
i0_.position ASC

This causes issues and unexpected in MySQL and a wrong set of results. The generated query should be
SELECT
...
FROM
item_type i0_
LEFT JOIN item i1_ ON i0_.id = i1_.item_type_id
AND (i1_.archived = 0)
LEFT JOIN object_item o2_ ON i1_.id = o2_.id
ORDER BY
i0_.position ASC



 Comments   
Comment by Benjamin Eberlei [ 08/Sep/13 ]

Can you check this again with 2.4? I think we have fixed this bug in 2.4

Comment by Bram Van der Sype [ 25/Jan/14 ]

Saw you (Benjamin) walking around phpbnl14 and remembered this. Just reproduced it in 2.4 and works now. So fixed, can be closed.

Comment by Bram Van der Sype [ 25/Jan/14 ]

reproduced in 2.4, works now.





[DDC-2575] Hydration bug Created: 27/Jul/13  Updated: 18/Apr/14  Resolved: 18/Apr/14

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: ORM
Affects Version/s: 2.3.4
Fix Version/s: 2.5
Security Level: All

Type: Bug Priority: Major
Reporter: Nicolas Bottarini Assignee: Guilherme Blanco
Resolution: Fixed Votes: 1
Labels: dql, orm


 Description   

I have the following class mappings:

class A
{
    /**
     * @Id
     * @Column(type="integer")
     * @GeneratedValue(strategy="AUTO")
     */
    protected $id;
    
    /**
     * @Column(type="string", length=100, nullable=FALSE)
     */    
    protected $sampleField;
    
    /**
     * @OneToOne(targetEntity="B", mappedBy="aRelation")
     **/     
    protected $bRelation;
}
class B
{
    /**
     * @Id
     * @OneToOne(targetEntity="A", inversedBy="bRelation")
     * @JoinColumn(name="a_id", referencedColumnName="id", nullable=FALSE, onDelete="CASCADE")
     */
    protected $aRelation;

    /**
     * @ManyToOne(targetEntity="C")
     * @JoinColumn(name="c_id", referencedColumnName="id", nullable=FALSE, onDelete="CASCADE")
     */
    protected $cRelation;

}
class C
{
    /**
     * @Id
     * @Column(type="integer")
     * @GeneratedValue(strategy="AUTO")
     */
    protected $id;
    
    /**
     * @Column(type="string", length=100, nullable=FALSE)
     */    
    protected $sampleField;
}

Then I make the following query:

$qb = $em->createQueryBuilder();
$qb = $qb->select('a, b, c')
      ->from('A','a')
      ->leftJoin('a.bRelation', 'b')
      ->leftJoin('b.cRelation', 'c');
      
$result = $qb->getQuery()->getResult();

The result contains a collection of instances of class A with the a.bRelation field populated and the a.bRelation.cRelation field populated in all rows except the last one.

The problem is an hydration problem. The Parser constructs the select statement with the fields of class A, then the fields of class C and last the fields of class B. The hydrator don't work correctly because when it's hydrating class C it doesn't find the class B (because it appears last in the select statement).
I think the problem is because class B only contains associations. If i put an extra field (an string for example) in class B it works as expected.



 Comments   
Comment by Popy [ 28/Oct/13 ]

I have the same kind of bug : i have a OneToMany relations which stays to null if I request both entities in one query, and miss an entity if I preload the related entities in a second query. It seems to occur on the last entity of the list.

If I remember well the hydrator code, there's (in the hydratation loop) something like "If we find a new root entity (or maybe on each row, i'm not sure), link the entities we didn't link". Maybe this thing is not done AFTER the loop for remaining entities.

I'll try to dig again into the hydrator code tomorrow to check this hypothesis.

Comment by Popy [ 29/Oct/13 ]

This bug seems more severe :

I made a test on a query with 3 entities (root, root->a, root->b, a and b relations are OneToMany, so no connections on this side), and there's the process I witness :

  • First result row
  • The hydrator finds the linked entities before the root... and just does nothing (line 359)
  • The hydrator finds the root entity, and hydrate it
  • Other result rows
  • The hydrator finds the linked entities before the root... and associate them with the previously found root entity, which is the root entity fetched on the first row
  • The hydrator finds the root entity, so trash the previous, and hydrate (without related entities, as they were linked to previous root entity)

To finish, the last row has no related entities, as its related entities were given to the previous row.

Comment by Popy [ 29/Oct/13 ]

Bug confirmed in a small Symfony app and Doctrine 2.3. I managed to reproduce the bug with 3 entities :

  • A (id autoincrement)
  • B (id autoincrement)
  • Root (composite id a,b which are ManyToOne relations to A and B entities)

Can provide the app to ease things.

Comment by Popy [ 29/Oct/13 ]

Possible workaround : declaring integer fields as ID (with the same field name as relation fields) makes the thing working again (at the price of thoose two useless properties and a prePersist method to fill them with related entity ids)

Comment by Benjamin Eberlei [ 14/Dec/13 ]

First step here: Try to reproduce this issue with the given entities above in a Testcase

Comment by Karol Horowski [ 14/Dec/13 ]

I created test for this issue, but I can't reproduce id. My pull request is here https://github.com/doctrine/doctrine2/pull/878

Comment by Popy [ 15/Dec/13 ]

You should maybe call $this->_em->clear() at the end of your setUp method.

I still have a Symfony Bundle reproducing the bug, how can i hand it to you ?

Comment by Benjamin Eberlei [ 15/Dec/13 ]

Popy you can create a branch of that symfony standard edition, and push it to a fork of symfony-standard on your Github account. Then you can comment a link to your branch on Github.

Comment by Karol Horowski [ 17/Dec/13 ]

After Popy's suggestion I added $this->_em->clear() and now I have failing test. My fault with this quick "everything is ok".

I tried to search what's happening in ObjectHydrator but something strange is going on in hydrateRowData method.

Comment by Popy [ 17/Dec/13 ]

Be carefull, headache come fast while reading this method

As far as I know, the problem could be solved if the hydrator started by hydrating the root entity first. Maybe.

Comment by Karol Horowski [ 19/Dec/13 ]

In select statement fields are in this order Root, B, A. Relations in my test are Root 1:1 A *:1 B.
Hydrator first gets Root data and next B. But here (line 407 in ObjectHydrator) it doesn't find A parent in resultPointers.

For now I don't have any idea how to add new logic for this.

Comment by Doctrine Bot [ 18/Apr/14 ]

A related Github Pull-Request [GH-878] was closed:
https://github.com/doctrine/doctrine2/pull/878

Comment by Guilherme Blanco [ 18/Apr/14 ]

As of https://github.com/doctrine/doctrine2/commit/38b683838648b549aad0e38ce88c70b6393755b3 this issue is now fixed.





[DDC-2574] Add posibility to fetch subclass associations in a polymorphic query Created: 27/Jul/13  Updated: 27/Jul/13  Resolved: 27/Jul/13

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: DQL, ORM
Affects Version/s: 2.3.4
Fix Version/s: None
Security Level: All

Type: Improvement Priority: Major
Reporter: Nicolas Bottarini Assignee: Marco Pivetta
Resolution: Won't Fix Votes: 0
Labels: dql, orm, proxy


 Description   

Suppose you have a parent class A and a subclass B that has an association with class C.
If you want to make a polymorphic query on all instances of class A the relation on class C in the subclass B always returns a proxy.
There's not posibility to use a left join with class C to fetch that association. This causes a N+1 SELECT performance problem.



 Comments   
Comment by Marco Pivetta [ 27/Jul/13 ]

This is a known/wanted limitation. Fetch-joining on associations of subclasses is not supported anyway, since from a DQL perspective, these associations don't exist at all. It's a very old won't fix: see DDC-16

Comment by Marco Pivetta [ 27/Jul/13 ]

Related to DDC-16

Comment by Nicolas Bottarini [ 27/Jul/13 ]

Hi Marco, thanks for the fast response!
Suppose you have a base Notification class and then a subclass for each type of concrete notification (UserFollowNotification, ProductAddedNotification, etc etc). You can't show the user's notifications with each notification detail without incurring in a n+1 performance problem. How do you deal with this kind of situations in doctrine?. In Nhibernate, for example, you can choose that an association must always be fetched at mapping level or at query level (with DataLoading options).
How do you handle this situations in doctrine?

Thank you very much.

Comment by Marco Pivetta [ 27/Jul/13 ]

Nicolas Bottarini assuming that you want to work only on a particular subclass, you may want to refresh a set of objects by building a specific DQL query.

First, retrieve all your records that contain the said collection. Then create following query:

SELECT f, b FROM Foo f LEFT JOIN f.bar b WHERE f.id IN (:foos)

You can then run this query against the objects you fetched:

$refreshedFoos = $query->setParameter('foos', $fetchedObjects)->setHint(\Doctrine\ORM\Query::HINT_REFRESH, true)->getResult();

This should reduce the overhead greatly.

Comment by Nicolas Bottarini [ 27/Jul/13 ]

Thank you very much Marco!





[DDC-2539] ResultVariable cannot be used in Like expressions Created: 02/Jul/13  Updated: 26/Aug/13  Resolved: 26/Aug/13

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: ORM
Affects Version/s: 2.3.4
Fix Version/s: 2.4
Security Level: All

Type: Bug Priority: Major
Reporter: Serge Liszewski Assignee: Marco Pivetta
Resolution: Duplicate Votes: 0
Labels: dql, orm, querybuilder,


 Description   

It is not possible to use ResultVariable in a like expression. For exemple :

$qb ->select('c', 'SQRT(c.id) AS test')
->from('SomeClass', 'c')
->having('test LIKE \'%4%\'');

return [Syntax Error] line 0, col 91: Error: Expected Literal, got 'LIKE'

$qb ->select('c')
->from('SomeClass', 'c')
->having('c.id LIKE \'%4%\'');

works, and :

$qb ->select('c', 'SQRT(c.id) AS test')
->from('SomeClass', 'c')
->having('test = 4');

works

Changing Doctrine\ORM\Query\Parser LikeExpression function fix the problem (but has maby other consequences) :

by replacing : "$stringExpr = $this->StringExpression();"
by :
$peek = $this->_lexer->glimpse();
if($peek['value'] == 'LIKE')

{ // Simple Expression $stringExpr = $this->ArithmeticExpression(); }

else

{ $stringExpr = $this->StringExpression(); }

 Comments   
Comment by Serge Liszewski [ 26/Aug/13 ]

After working on this issue, i really think it's a bug. I just add more details on the description

Comment by Marco Pivetta [ 26/Aug/13 ]

Duplicate of DDC-2615 (solved in master)





[DDC-2532] Bring back LIMIT in DQL Created: 27/Jun/13  Updated: 27/Jun/13  Resolved: 27/Jun/13

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: DQL
Affects Version/s: 2.3.4
Fix Version/s: None
Security Level: All

Type: Improvement Priority: Major
Reporter: Stancsik Miklós Assignee: Benjamin Eberlei
Resolution: Invalid Votes: 0
Labels: dql


 Description   

Hi!

I want to use limit in dql. Consider the following query:

SELECT p FROM Products p
INNER JOIN p.prices price
WITH price =
(SELECT prc FROM Prices prc
WHERE prc.from < :time
AND prc.product = p
ORDER BY prc.from desc)
WHERE p.id IN(:product_ids)

I want it to use like this:
SELECT p FROM Products p
INNER JOIN p.prices price
WITH price =
(SELECT prc FROM Prices prc
WHERE prc.from < :time
AND prc.product = p
ORDER BY prc.from desc
LIMIT 1)
WHERE p.id IN(:product_ids)

I think it doesn't need further explanation.



 Comments   
Comment by Benjamin Eberlei [ 27/Jun/13 ]

This impossible to support across all vendors, and therefore not an optoin for us.





[DDC-2506] WITH Conditionals on Class Table Inheritance LEFT JOINs are inserted incorrectly Created: 14/Jun/13  Updated: 17/Feb/14  Resolved: 20/Aug/13

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: DQL
Affects Version/s: Git Master
Fix Version/s: 2.4, 2.3.5
Security Level: All

Type: Bug Priority: Major
Reporter: Matt Janssen Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 2
Labels: dql, inheritance, joins, sql-walker


 Description   

The following JOIN

JOIN c.ctiRelationship cti WITH cti.id IN (42)

generates unexpected SQL

LEFT JOIN class_base p1_ ON u1_.cti_id = p1_.id 
LEFT JOIN class_child1 p2_ ON p1_.id = p2_.id
LEFT JOIN class_child2 p3_ ON p1_.id = p3_.id AND (p1_.id IN (42)) 

when it SHOULD be generating

LEFT JOIN class_base p1_ ON u1_.cti_id = p1_.id AND (p1_.id IN (42)) 
LEFT JOIN class_child1 p2_ ON p1_.id = p2_.id
LEFT JOIN class_child2 p3_ ON p1_.id = p3_.id


 Comments   
Comment by Matt Janssen [ 14/Jun/13 ]

https://github.com/doctrine/doctrine2/pull/708

Comment by gseric [ 01/Jul/13 ]

Thanks Matt, this bug prevented me to upgrade to 2.3. BTW it was originally reported in DDC-2131 (I put a comment there to redirect users here).

Comment by Gordon Forsythe [ 03/Jul/13 ]

I've tested this PR and it does work.

Comment by Doctrine Bot [ 13/Aug/13 ]

A related Github Pull-Request [GH-708] was closed:
https://github.com/doctrine/doctrine2/pull/708

Comment by Doctrine Bot [ 17/Feb/14 ]

A related Github Pull-Request [GH-886] was closed:
https://github.com/doctrine/doctrine2/pull/886





[DDC-2495] Partial objects not working with STI Created: 10/Jun/13  Updated: 11/Jun/13

Status: Open
Project: Doctrine 2 - ORM
Component/s: ORM
Affects Version/s: 2.3.4
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Radoslaw Ejsmont Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: STI, dql, orm, partial
Environment:

Symfony2 project, Doctrine ORM with MySQL database backend


Attachments: File CrossVial.php     File Entity.php     File InjectionVial.php     File Stock.php     File StockVial.php     File Vial.php    

 Description   

When I try to create a query retrieving partial objects of a root class in single table inheritance hierarchy, the resulting SQL includes all fields from the whole class hierarchy.

DQL:
SELECT partial v.

{id, setupDate, flipDate}

FROM VIB\FliesBundle\Entity\Vial v WHERE v.id IN (1,2,3,4,5,6,7,8,9,10)

SQL:
SELECT v0_.setupDate AS setupDate0, v0_.flipDate AS flipDate1, v0_.id AS id2, v0_.type AS type3, v0_.parent_id AS parent_id4, v0_.position_id AS position_id5, v0_.prevPosition_id AS prevPosition_id6, v0_.incubator_id AS incubator_id7, v0_.stock_id AS stock_id8, v0_.male_id AS male_id9, v0_.virgin_id AS virgin_id10, v0_.targetStock_id AS targetStock_id11, v0_.targetStockVial_id AS targetStockVial_id12 FROM Vial v0_ WHERE (v0_.id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)) AND v0_.type IN ('vial', 'stock', 'cross', 'injection')



 Comments   
Comment by Fabio B. Silva [ 10/Jun/13 ]

Could you please provide your entities ?

Thanks

Comment by Radoslaw Ejsmont [ 11/Jun/13 ]

This is the whole class hierarchy.

Comment by Radoslaw Ejsmont [ 11/Jun/13 ]

I have actually noticed, that the "partial" keyword is ignored even for entities that are not using any inheritance schema. So it seems that this keyword is generally ignored.

Right now the following query:

SELECT e, partial p.

{id}

, o, s FROM VIB\FliesBundle\Entity\StockVial e LEFT JOIN e.parent p LEFT JOIN e.position o LEFT JOIN e.stock s WHERE e.setupDate > :date AND e.trashed = false ORDER BY e.setupDate DESC ORDER BY e.id DESC

would result in the following SQL:

SELECT v0_.setupDate AS setupDate0, v0_.flipDate AS flipDate1, v0_.notes AS notes2, v0_.size AS size3, v0_.labelPrinted AS labelPrinted4, v0_.trashed AS trashed5, v0_.temperature AS temperature6, v0_.id AS id7, v1_.id AS id8, r2_.rackRow AS rackRow9, r2_.rackColumn AS rackColumn10, r2_.id AS id11, s3_.name AS name12, s3_.genotype AS genotype13, s3_.notes AS notes14, s3_.vendor AS vendor15, s3_.infoURL AS infoURL16, s3_.verified AS verified17, s3_.id AS id18, v0_.type AS type19, v0_.parent_id AS parent_id20, v0_.position_id AS position_id21, v0_.prevPosition_id AS prevPosition_id22, v0_.incubator_id AS incubator_id23, v0_.stock_id AS stock_id24, v1_.type AS type25, v1_.parent_id AS parent_id26, v1_.position_id AS position_id27, v1_.prevPosition_id AS prevPosition_id28, v1_.incubator_id AS incubator_id29, v1_.stock_id AS stock_id30, v1_.male_id AS male_id31, v1_.virgin_id AS virgin_id32, v1_.targetStock_id AS targetStock_id33, v1_.targetStockVial_id AS targetStockVial_id34, r2_.rack_id AS rack_id35, s3_.sourceCross_id AS sourceCross_id36 FROM Vial v0_ LEFT JOIN Vial v1_ ON v0_.parent_id = v1_.id AND v1_.type IN ('vial', 'stock', 'cross', 'injection') LEFT JOIN RackPosition r2_ ON v0_.position_id = r2_.id LEFT JOIN Stock s3_ ON v0_.stock_id = s3_.id WHERE (v0_.setupDate > '2013-04-11' AND v0_.trashed = 0) AND v0_.type IN ('stock') ORDER BY v0_.setupDate DESC, v0_.id DESC

Please note that ALL properties of parent have been included in the generated SQL.

You can find the whole project (Symfony2) on github: https://github.com/rejsmont/LabDB

Best,

R.

Comment by Radoslaw Ejsmont [ 11/Jun/13 ]

I have noticed that using the setHint(Doctrine\ORM\Query::HINT_FORCE_PARTIAL_LOAD, 1) forces partial load, however then even the entities I want loaded entirely (with proxied references) are partially loaded (i.e. all the references are forced to null, unless explicitly loaded via join).





[DDC-2470] Sql Server error in createQuery using ORDER BY and setMaxResults Created: 24/May/13  Updated: 07/Jun/13  Resolved: 07/Jun/13

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: ORM
Affects Version/s: 2.4
Fix Version/s: 2.4
Security Level: All

Type: Bug Priority: Blocker
Reporter: Jonnatan Oyarzún Assignee: Fabio B. Silva
Resolution: Fixed Votes: 0
Labels: dql, sqlserver
Environment:

Windows 7, Apache 2 (xampp 1.8.1), PHP 5.4.7, Symfony 2.2.1


Attachments: PNG File BD.png    

 Description   

Important: This issue only affect to 2.4.* versions

When executing

$query = $em->createQuery('
SELECT m.nombre
     , m.fechainicio
     , m.fechafin 
  FROM Bundle:Medicion m
  JOIN m.estudio e
  JOIN e.cliente c
  JOIN c.usuarios u
 WHERE u.id = :id
 ORDER BY m.fechainicio DESC
')
->setMaxResults(12);

Get the following error:

An exception occurred while executing '
SELECT * 
  FROM (
           SELECT m0_.NOMBRE AS NOMBRE0
                , m0_.FECHAINICIO AS FECHAINICIO1
                , m0_.FECHAFIN AS FECHAFIN2
                , ROW_NUMBER() OVER (ORDER BY FECHAINICIO1 DESC) AS doctrine_rownum 
             FROM MEDICION m0_ WITH (NOLOCK) 
            INNER JOIN ESTUDIO e1_ 
               ON m0_.ESTUDIO_ID = e1_.ID 
            INNER JOIN CLIENTE c2_ 
               ON e1_.CLIENTE_ID = c2_.ID 
            INNER JOIN USUARIO u3_ 
               ON c2_.ID = u3_.CLIENTE_ID WHERE u3_.ID = ?
       ) AS doctrine_tbl 
 WHERE doctrine_rownum BETWEEN 1 AND 12
' with params [2]:
SQLSTATE[42S22]: [Microsoft][SQL Server Native Client 11.0][SQL Server]El nombre de columna 'FECHAINICIO1' no es válido.

Attached the BD model

Added extra info!

Engine version: Sql server 2008 R2

When executing this SQL (returned by doctrine error) on Management Studio

SELECT * FROM (SELECT m0_.NOMBRE AS NOMBRE0, m0_.FECHAINICIO AS FECHAINICIO1, m0_.FECHAFIN AS FECHAFIN2, ROW_NUMBER() OVER (ORDER BY FECHAINICIO1 DESC) AS doctrine_rownum FROM MEDICION m0_ WITH (NOLOCK) INNER JOIN ESTUDIO e1_ ON m0_.ESTUDIO_ID = e1_.ID INNER JOIN CLIENTE c2_ ON e1_.CLIENTE_ID = c2_.ID INNER JOIN USUARIO u3_ ON c2_.ID = u3_.CLIENTE_ID WHERE u3_.ID = 12) AS doctrine_tbl WHERE doctrine_rownum BETWEEN 1 AND 12

Get the following error:

El nombre de columna 'FECHAINICIO1' no es válido. ('FECHAINICIO1' is invalid)

But if change "FECHAINICIO1"

... OVER (ORDER BY FECHAINICIO1 DESC) AS doctrine_rownum FROM...

by this

... OVER (ORDER BY m0_.FECHAINICIO DESC) AS doctrine_rownum FROM...

Don't get error

regards
Jonnatan Oyarzún



 Comments   
Comment by Guilherme Blanco [ 05/Jun/13 ]
  • Which version of SQL Server are you working on?
  • Also, could you please verify 2 queries for us?
    This one should not work:
SELECT m0_.NOMBRE AS NOMBRE0
     , m0_.FECHAINICIO AS FECHAINICIO1
     , m0_.FECHAFIN AS FECHAFIN2 
  FROM MEDICION m0_ 
 ORDER BY FECHAINICIO1 DESC

This one should work:

SELECT m0_.NOMBRE AS NOMBRE0
     , m0_.FECHAINICIO AS FECHAINICIO1
     , m0_.FECHAFIN AS FECHAFIN2 
  FROM MEDICION m0_ 
 ORDER BY m0_.FECHAINICIO DESC

As soon as you get this it may define the approach on how we're gonna fix the issue. =)

Cheers,

Guilherme Blanco

Comment by Jonnatan Oyarzún [ 05/Jun/13 ]

Add extra info

Cheers,

Jonnatan Oyarzún

Comment by Fabio B. Silva [ 05/Jun/13 ]

Hi Jonnatan

There is a possible fix in this branch : DDC-2470,
but i'm not able to test it right now..

Could you please test it in your environment ?

Thanks..

Comment by Jonnatan Oyarzún [ 05/Jun/13 ]

Hi Fabio

Thank you very much for posting this fix.
From your DDC-2470, I downloaded and pasted files in vendor\doctrine\dbal.
Fix is working for me!.

The question is, when this fix could be merged to dbal/master branch?

Cheers,
Jonnatan Oyarzún

Comment by Fabio B. Silva [ 05/Jun/13 ]

Thanks Jonnatan,

I've created a pull request : https://github.com/doctrine/dbal/pull/332

Comment by Fabio B. Silva [ 07/Jun/13 ]

Fixed : https://github.com/doctrine/doctrine2/commit/753d63c2d48facdecba5d84f6ed2450024de2867





[DDC-2452] Additional `WITH` condition in joins between JTI roots cause invalid SQL to be produced Created: 16/May/13  Updated: 27/Mar/14

Status: Open
Project: Doctrine 2 - ORM
Component/s: DQL, ORM
Affects Version/s: Git Master
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Marco Pivetta Assignee: Marco Pivetta
Resolution: Unresolved Votes: 0
Labels: dql, sql-walker
Environment:

irrelevant



 Description   

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






[DDC-2347] Refresh Uniqueidentifier ID from mssql of inserted Entity in doctrine2.3 Created: 13/Mar/13  Updated: 13/Mar/13

Status: Open
Project: Doctrine 2 - ORM
Component/s: ORM
Affects Version/s: 2.3.2
Fix Version/s: None

Type: Bug Priority: Minor
Reporter: Lucas Senn Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: dql
Environment:

Windows Server 2008 R2, Apache 2.2, Doctrine 2.3, PHP 5.4



 Description   

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






[DDC-2268] Lexer error using string functions inside CASE WHEN Created: 02/Feb/13  Updated: 02/Feb/13  Resolved: 02/Feb/13

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: DQL
Affects Version/s: 2.3.2
Fix Version/s: 2.4
Security Level: All

Type: Bug Priority: Major
Reporter: Stefano Assignee: Fabio B. Silva
Resolution: Fixed Votes: 0
Labels: dql
Environment:

Windows 8 64bit, IIS Express 8, PHP 5.4.9



 Description   

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.



 Comments   
Comment by Fabio B. Silva [ 02/Feb/13 ]

https://github.com/doctrine/doctrine2/commit/1627fc95965a3e2e3894fcf7e524eb0eaa9d0ddd





[DDC-2254] Exporting and restoring a query. Created: 23/Jan/13  Updated: 04/May/13

Status: Open
Project: Doctrine 2 - ORM
Component/s: Documentation, DQL, ORM
Affects Version/s: Git Master, 2.3.2
Fix Version/s: None
Security Level: All

Type: Improvement Priority: Major
Reporter: Dries De Peuter Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: dql, rebuild, restore, save
Environment:

OSX



 Description   

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


 Comments   
Comment by Dries De Peuter [ 23/Jan/13 ]

I wrote a test showing the issue.

https://github.com/NoUseFreak/doctrine2/commit/8574b79fd3d245532bbe7e310c5cbe083892057a

Comment by Benjamin Eberlei [ 04/May/13 ]

This is not a bug, because restoring queries is not yet a feature of the QueryBuilder. Marking as possible improvement for future.





[DDC-2240] Inconsistent querying for parameter type (from ClassMetadata) between using Find/FindBy and DoctrineQL Created: 11/Jan/13  Updated: 08/Feb/13  Resolved: 13/Jan/13

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: DQL
Affects Version/s: 2.3.1
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Slavik Derevyanko Assignee: Benjamin Eberlei
Resolution: Duplicate Votes: 0
Labels: ClassMetadata, dql

Attachments: PNG File DoctrineQL methods trace.png     PNG File findBy methods trace.png     File UTCDateTimeType.php    
Issue Links:
Duplicate
is duplicated by DDC-2224 convertToDatabaseValueSQL() is not ho... Resolved

 Description   

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



 Comments   
Comment by Slavik Derevyanko [ 11/Jan/13 ]

I realized, that with DQL,
the default type 'datetime' of Doctrine\ORM\Query\Parameter for DateTime objects
is getting set
by Doctrine\ORM\Query\ParameterTypeInferer#inferType(),

and that it's possible to set the type as a third parameter:

$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'), 'utcdatetime');

It seems, this is worth noting in the documentation.

Comment by Benjamin Eberlei [ 12/Jan/13 ]

Verified, but I don't know how to fix it without breaking BC.

As a workaround you can convert the value yourself in your code, not the nicest solution, but when wrapped in a function call of your own, it shouldn't be to invasive.

Guilherme Blanco any idea what to do?

Comment by Guilherme Blanco [ 12/Jan/13 ]

There's a way currently to fix this issue.
Of course that we lack of some direct support, but you can take advantage of a second method to fix your problem.

Currently, setParameter only accepts key, value, type as arguments, creating its own Query\Parameter.
But if you look at setParameters receiving an ArrayCollection, it doesn't create the Parameter. This is where you can take advantage.

Ideally, any Type could convert back and forth from DB to PHP value. During a query, the algorithm should apply also. But if we do this change, we will introduce a BC break. To solve the issue, you'll have to create your own Parameter.

From the Doctrine perspective, we only need to support $key to be a class too. If it's a class, replace the value in the collection of parameters. This is the required change in our codebase.
But until this gets done, setParameters is already compatible with the solution.

All you have to do is create a class that extends Query\Parameter, then apply your required changes when doing getValue or during object construction. Then use the method I mentioned to inject an ArrayCollection of Parameters and everything will work. =)

Comment by Benjamin Eberlei [ 13/Jan/13 ]

Same as DDC-2224

Comment by Benjamin Eberlei [ 08/Feb/13 ]

A related Github Pull-Request [GH-574] was opened
https://github.com/doctrine/doctrine2/pull/574

Comment by Slavik Derevyanko [ 08/Feb/13 ]

Great, thanks!





[DDC-2223] unable to use scalar function when a scalar expression is expected Created: 04/Jan/13  Updated: 04/Jan/13

Status: Open
Project: Doctrine 2 - ORM
Component/s: DQL
Affects Version/s: Git Master
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Alexis Lameire Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: dql
Environment:

(not affected by this bug)



 Description   

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






[DDC-2205] Negative Values in Case Then expressions Created: 18/Dec/12  Updated: 21/Dec/12  Resolved: 21/Dec/12

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: DQL
Affects Version/s: 2.3.1
Fix Version/s: 2.4
Security Level: All

Type: Bug Priority: Critical
Reporter: Ilya Biryukov Assignee: Fabio B. Silva
Resolution: Fixed Votes: 0
Labels: dql


 Description   

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.



 Comments   
Comment by Fabio B. Silva [ 21/Dec/12 ]

Fixed : https://github.com/doctrine/doctrine2/commit/8b5e4a9a52670992b85e7223d255b98cf77a35a3





[DDC-2204] Order by With Equals is not supported Created: 17/Dec/12  Updated: 22/Dec/12  Resolved: 22/Dec/12

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: DQL
Affects Version/s: 2.3
Fix Version/s: None

Type: Bug Priority: Critical
Reporter: Ilya Biryukov Assignee: Benjamin Eberlei
Resolution: Invalid Votes: 0
Labels: dql
Environment:

SQL construct tested on postgres 9.0, mysql 5.5, and sqlite 3.


Attachments: File Language.sql    

 Description   

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

id name

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

82 English
73 Albanian
74 Arabic
75 Armenian
76 Bengali

------------+
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



 Comments   
Comment by Benjamin Eberlei [ 22/Dec/12 ]

Its supported by including the condition in the SELECT clause, aliasing it, then using it. You might need to use "AS HIDDEN name" to prevent it from appearing in the result





[DDC-2185] Better explain DQL "WITH" and implications for the collection filtering API Created: 04/Dec/12  Updated: 17/Dec/12

Status: Open
Project: Doctrine 2 - ORM
Component/s: Documentation, DQL
Affects Version/s: 2.2
Fix Version/s: None
Security Level: All

Type: Documentation Priority: Major
Reporter: Matthias Pigulla Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: collection, documentation, dql, filtering


 Description   

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.



 Comments   
Comment by Matthias Pigulla [ 17/Dec/12 ]

An additional observation:

If you eager-load a collection using WITH, for the resulting entities that collection is marked as initialized as described above.

Should you happen to come across the same entity during hydration in another (later) context where you explicitly eager load the same association without the WITH restriction (or with another one), the collection on that (existing) entity won't be re-initialized and still contains the associated objects found during the first query.





[DDC-2155] problem with DQL and cache Created: 18/Nov/12  Updated: 25/Nov/12  Resolved: 25/Nov/12

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: DQL, ORM
Affects Version/s: 2.3
Fix Version/s: None
Security Level: All

Type: Bug Priority: Critical
Reporter: gabriel sancho Assignee: Benjamin Eberlei
Resolution: Invalid Votes: 0
Labels: cache, dql
Environment:

linux , php 5.4.8, mysql 5.5.28



 Description   

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



 Comments   
Comment by Benjamin Eberlei [ 25/Nov/12 ]

Doctrine uses an IdentityMap pattern which leads to this issue.

You need to call "EntityManager#clear()" to clean the in memory cache of Doctrine and fetch records from the database again. or call "EntityManager#refresh($entity)"





[DDC-2148] Many-to-many not working with interface Created: 16/Nov/12  Updated: 22/Nov/12  Resolved: 22/Nov/12

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: DQL
Affects Version/s: 2.3
Fix Version/s: 2.2

Type: Bug Priority: Major
Reporter: Moritz Kraft Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: dql
Environment:

Linux, PHP 5.3.10, Symfony 2.2



 Description   

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

{"1":2,"2":1,"3":2,"4":1}

:

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.



 Comments   
Comment by Marco Pivetta [ 16/Nov/12 ]

"many-to-many TO a mapped superclass"? I don't think relations TO mapped superclasses are supported in any way... A mapped superclass should never appear in a `targetEntity` mapping.

Could you please re-formulate the description of the issue? There is no mapped superclass in your examples

Comment by Moritz Kraft [ 16/Nov/12 ]

You are of course right. Edited the issue description/title.

It is a blocker for us, btw., not minor - being able to use a many-to-many relation there is rather central to the code of our app. I'm not seeing a workaround, easy or otherwise.

Comment by Marco Pivetta [ 16/Nov/12 ]

Moritz Kraft yes, but it is not a blocker for the next release Will restore prio.

Comment by Moritz Kraft [ 16/Nov/12 ]

Ah right, yeah, that makes sense. Thank you!

Comment by Moritz Kraft [ 22/Nov/12 ]

Fixed in 1b5f051 - thanks Benjamin!

Comment by Moritz Kraft [ 22/Nov/12 ]

Fixed in 2.3 branch, backported to 2.2 as well





[DDC-2119] Problem with inheritance type: INHERITANCE_TYPE_NONE and INHERITANCE_TYPE_TABLE_PER_CLASS Created: 03/Nov/12  Updated: 08/Apr/13

Status: Open
Project: Doctrine 2 - ORM
Component/s: DQL, Tools
Affects Version/s: 2.1
Fix Version/s: None

Type: Bug Priority: Major
Reporter: SergSW Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: dql, schematool

Attachments: File dump.sql     File SSWTestBundle.rar    

 Description   

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.



 Comments   
Comment by Fabio B. Silva [ 05/Nov/12 ]

Hi SergSW

Could you try to write a failing test case ?

Thanks

Comment by SergSW [ 06/Nov/12 ]

SSW/TestBundle with the problem

Comment by SergSW [ 07/Nov/12 ]

I install the Symfony v2.0.18. and made small TestBundle.
I made schema database, by CLI "console doctrine:schema:update --force"
Result: Database schema updated successfully!
But I saw that I lost a field 'user_id' in a table 'AttachTree' (see Attach)

Comment by SergSW [ 07/Nov/12 ]

MySQL dump

Comment by Benjamin Eberlei [ 12/Nov/12 ]

Adjusted example formatting, don't apologize for your English, thanks for the report!

Comment by Benjamin Eberlei [ 24/Dec/12 ]

What version of 2.1 are you using? We don't actually support 2.1 anymore. Inheritance has always worked as used in hundrets of unit-tests, this changes look quite major a bug to have been missed before. I can't really explain whats happening here.

Comment by Marco Pivetta [ 23/Jan/13 ]

SergSW news?





[DDC-2105] Error when rendering DQL query Created: 26/Oct/12  Updated: 23/Dec/12  Resolved: 23/Dec/12

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: DQL
Affects Version/s: 2.3
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Marcos García Assignee: Benjamin Eberlei
Resolution: Invalid Votes: 0
Labels: dql
Environment:

Mac OS X Mountain Lion
PHP 5.3.14 (cli)



 Description   

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.



 Comments   
Comment by Fabio B. Silva [ 29/Oct/12 ]

Hi Marcos,

Could you try to add a failing test case ?

Thanks ...

Comment by Marcos García [ 23/Dec/12 ]

Hi Fabio,

I've updated Doctrine and now it seems that everything works OK...

Thank you for your time... (and sorry) :S





[DDC-2076] Optimization for MEMBER OF Created: 14/Oct/12  Updated: 14/Oct/12

Status: Open
Project: Doctrine 2 - ORM
Component/s: DQL
Affects Version/s: Git Master
Fix Version/s: None
Security Level: All

Type: Improvement Priority: Minor
Reporter: Christophe Coevoet Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: dql


 Description   

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





[DDC-2052] Custom tree walkers are not allowed to add new components to the query Created: 02/Oct/12  Updated: 08/Feb/14  Resolved: 08/Feb/14

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: DQL
Affects Version/s: 2.3
Fix Version/s: 2.5

Type: Improvement Priority: Major
Reporter: Łukasz Cybula Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 2
Labels: dql


 Description   

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.



 Comments   
Comment by Benjamin Eberlei [ 06/Oct/12 ]

Ok this is much more complicated to allow then i thought. The problem is that the QueryComponents are passed by value, as an array, not by reference. That prevents changing them because this change wouldn't be visible in the output walker.

I can add a method to allow this in the OutputWalker for now, but generally this requires a bigger refactoring on the Query Components.

Comment by Benjamin Eberlei [ 06/Oct/12 ]

Added setQueryComponent() in SQL Walker to allow modification in output walker.

Comment by Łukasz Cybula [ 08/Oct/12 ]

I'm afraid that this doesn't solve the initial problem at all. I'll try to describe it in more details to show what I mean. Suppose we have two doctrine extensions each of which contain its own tree walker. Each of these tree walkers need to modify AST and add new component to it (joined with some component already existing in the query). The first problem is that each tree walker has its own queryComponents array which is not passed between them, although they not necessary need to use queryComponents - they could use only AST. The second, bigger problem is that the Parser class does not know anything about modifications of queryComponents in tree walkers and cannot pass modified version to the OutputWalker. The goal of submitting this issue was to allow adding new components to the query in tree walkers which is not achievable by your fix. I think it may be the first step in the right direction. Maybe TreeWalkerAdapter should have public method getQueryComponents() which would be used by the Parser to pass modified queryComponents between different tree walkers and finally to the OutputWalker ? This would not break backward compatibility and solve this issue. What do you think about it?

Comment by Łukasz Cybula [ 08/Oct/12 ]

I've tried to implement the solution mentioned in previous comment but it's also not so clean and easy as I thought. Each tree walker (including TreeWalkerChain) would have to implement getQueryComponents() and setQueryComponent($alias, array $component) methods. The same with SqlWalker, so the TreeWalker interface should have these methods, which would break BC in some way (walkers that do not inherit from SqlWalker or TreeWalkerAdapter will fail to compile). So maybe my first solution (PR #464) is not so bad for now? In the future queryComponents could be replaced by a special object or could be passed by a reference to allow modifications.

Comment by Benjamin Eberlei [ 09/May/13 ]

Marked as improvement as its not a bug.

A solution might probably implement an object holding all the QueryComponent, implementing ArrayAccess. So that way the state can be shared.

Comment by Marco Pivetta [ 14/May/13 ]

Just hit this while developing an ast walker... Will look into it too since I need it more than soon.

Comment by Marco Pivetta [ 14/May/13 ]

As a VERY UGLY workaround, I used a static variable and a custom sql walker in combination with my AST walker.


namespace Comcom\Versioning\ORM\Query;


use Doctrine\ORM\Query\SqlWalker;

class WorkaroundSqlWalker extends SqlWalker
{
    public function __construct($query, $parserResult, array $queryComponents)
    {
        parent::__construct($query, $parserResult, $queryComponents);

        foreach (VersionWalker::$additionalAliases as $alias => $value) {
            $this->setQueryComponent($alias, $value);
        }
    }
}
Comment by Andreas H [ 21/Jan/14 ]

My workaround is to overwrite the TreeWalkerChain class (using Composer) and pulling the query components from the last iteration in walkSelectStatement to the next one.

Comment by Benjamin Eberlei [ 08/Feb/14 ]

Partial fix in https://github.com/doctrine/doctrine2/pull/934





[DDC-2032] DQL fails for Joined Inheritance with Associations on child classes Created: 15/Sep/12  Updated: 21/Sep/12  Resolved: 21/Sep/12

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: DQL
Affects Version/s: 2.3
Fix Version/s: None
Security Level: All

Type: Bug Priority: Critical
Reporter: Philipp Dobrigkeit Assignee: Benjamin Eberlei
Resolution: Invalid Votes: 0
Labels: dql
Environment:

Windows XAMPP



 Description   

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?



 Comments   
Comment by Philipp Dobrigkeit [ 19/Sep/12 ]

Ok, it doesn't seem related to inheritance. Have the same problem now as well with a Join DQL query.

Foo (1:n) Bar
Bar (1:n) Baz

SELECT c FROM Foo c INNER JOIN c.bar cj WITH cj.id IN(1)

Gives me the same error as above when it tries to hydrate Bar

Comment by Philipp Dobrigkeit [ 21/Sep/12 ]

Ok, further investigations on my part have found the problem to be somewhere else entirely... Not sure yet if it is a bug, but the problem arises because during the hydration my application does another query, which uses the same hydrator object and thus does cleanup before the initial query is complete. That results in the _rsm being null when the original hydration continues. Will see if that can be fixed in my application.

Comment by Marco Pivetta [ 21/Sep/12 ]

Philipp Dobrigkeit this is known. You have to pass a custom hydrator to the new query if you use DQL during PostLoad events.





[DDC-2021] Array Data in Member OF Created: 09/Sep/12  Updated: 11/Jul/14  Resolved: 11/Jul/14

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: DQL
Affects Version/s: 2.2.3
Fix Version/s: 2.5
Security Level: All

Type: New Feature Priority: Major
Reporter: vahid sohrabloo Assignee: Marco Pivetta
Resolution: Fixed Votes: 1
Labels: array, dql

Issue Links:
Reference
is referenced by DDC-3210 [GH-1080] possible fix for DDC-2021 Resolved

 Description   

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



 Comments   
Comment by Daniel Sippel [ 09/Jul/14 ]

+1

Simple solution could be this: use SQL IN(value) always instead of the equality sign..

Current situation:
500 Internal Server Error - DBALException
1 linked Exception: PDOException »

An exception occurred while executing 'SELECT w0_.id AS id0, w0_.userId AS userId1, w0_.profileName AS profileName2, w0_.url AS url3, w0_.companyName AS companyName4, w0_.firstName AS firstName5, w0_.lastName AS lastName6, w0_.street AS street7, w0_.houseNo AS houseNo8, w0_.postcode AS postcode9, w0_.city AS city10, w0_.phoneNo AS phoneNo11, w0_.faxNo AS faxNo12, w0_.email AS email13, w0_.websiteUrl AS websiteUrl14, w0_.description AS description15, w0_.created AS created16, w0_.updated AS updated17, w0_.offersFurther AS offersFurther18, w0_.companyLogoImageFileExtension AS companyLogoImageFileExtension19, w0_.location AS location20 FROM profile w0_ WHERE 1 = 1 AND (GLength(LineString(w0_.location, GeomFromText(?)))*100 < 30) AND EXISTS (SELECT 1 FROM profile_attribute_mapping p1_ INNER JOIN profile_attribute w2_ ON p1_.profileattribute_id = w2_.id WHERE p1_.profile_id = w0_.id AND w2_.id = ?, ?)' with params [{}, 1, 2]:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' 2)' at line 1
Comment by Daniel Sippel [ 09/Jul/14 ]

pull request https://github.com/doctrine/doctrine2/pull/1080

Solution: use SQL IN(value) always instead of the equality sign

Comment by Doctrine Bot [ 11/Jul/14 ]

A related Github Pull-Request [GH-1080] was closed:
https://github.com/doctrine/doctrine2/pull/1080

Comment by Marco Pivetta [ 11/Jul/14 ]

Handled in DDC-3210, see https://github.com/doctrine/doctrine2/commit/ae0ee724252b8aaf41be9b397d3db3375767095d





Generated at Sun Aug 31 06:29:50 UTC 2014 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.