[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-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-2842] Leave out discriminator part of Doctrine' generated SQL Created: 06/Dec/13  Updated: 06/Dec/13

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

Type: Improvement Priority: Minor
Reporter: Roel Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None


 Description   

Assume the following AbstractPage model:

/*

  • @ORM\Entity
  • @ORM\Table(name="page")
  • @ORM\InheritanceType("SINGLE_TABLE")
  • @ORM\DiscriminatorColumn(name="type", type="string")
  • @ORM\DiscriminatorMap
  • ( { * "page" = "Page", * "link" = "Link" * }

    )
    */

And the following DQL query:

SELECT p FROM \Page\Model\AbstractPage

The generated SQL will be:

SELECT ... FROM page p0_ WHERE p0_.type IN ('page', 'link')

Now to the question: how can I remove the WHERE clause from this query. On more complex queries this part of the WHERE clause makes it not possible to use some indexes that are defined. This can be resolved by adding type to the indexes, but this makes my indexes larger and I feel this is not necessary.

The AbstractPage is the root in the inheritance tree. Thus we are interested in ALL records in the table. Omiting the WHERE part does precisely that.

So the question is: how can I make Doctrine remove this WHERE part where it is not necessary.






[DDC-1916] Centralize the Cache mechanism simplifying the query creation Created: 09/Jul/12  Updated: 09/Jul/12

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

Type: New Feature Priority: Minor
Reporter: liuggio Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None


 Description   

Hi all,

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

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

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

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

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

Thanks

liuggio






[DDC-1754] Allow use of Foregin Keys in DQL LIKE condition Created: 03/Apr/12  Updated: 09/Apr/12

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

Type: Improvement Priority: Minor
Reporter: Tim Roediger Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Php 5.3



 Description   

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

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

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

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

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

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

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

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



 Comments   
Comment by Benjamin Eberlei [ 07/Apr/12 ]

This would only work if Department#Name is the primary key. Is it?

DQL is not about optimized performance in very single edge case. We need to keep some parts open to keep the code simple. This is at best a feature request, not a bug.

Comment by Tim Roediger [ 09/Apr/12 ]

Thanks for your reply Benjamin,

I agree with your assesment that this is more accurately an improvement request than a bug. My appologies it was placed in the wrong category.

No, Department#Name is not the primary key, but it does have a unique index.

I've worked a little more to understand the DQL grammar. It appears that only one small, simple change is required:

FROM:
StringPrimary ::= StateFieldPathExpression | string | InputParameter | FunctionsReturningStrings | AggregateExpression

TO:
StringPrimary ::= SingleValuedPathExpression | string | InputParameter | FunctionsReturningStrings | AggregateExpression

This would improve consistancy with other aspects of DQL. For example, AggregateExpression COUNT, NullComparisonExpression , GroupByItem, and ArithmeticPrimary all allow a SingleValuedPathExpression rather than the more strict StateFieldPathExpression.

Bascially my frustration is that as DQL currently stands, foregin keys which are already existant in a db table cannot be used in a LIKE expression without doing an unnessessary JOIN. Foreign keys can already be used in DQL for BETWEEN, IS NULL and comparison expressions, so why not LIKE expressions also? It appears the only thing holding this back is an unrequired restriction in the DQL grammar.

Cheers, Tim





[DDC-2633] How i can use arithmetic expression in the ORDER BY clause? Created: 26/Aug/13  Updated: 26/Aug/13

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

Type: Documentation Priority: Trivial
Reporter: Liverbool Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None


 Description   

I found solution in this site: http://michelsalib.com/2012/03/04/doctrine2-ninja-with-the-hidden-keyword/

I want to use:
... ORDER BY DATE_FORMAT(field, '%Y')
or
... ORDER BY CONVERT(field USING [CHARSET])

and it catch exception like:

<br />
<b>Fatal error</b>: Uncaught exception 'Doctrine\ORM\Query\QueryException' with message 'SELECT t.id, t.label, t.id AS value, t.label AS text FROM Entities\Repair\ProductType t WHERE t.bch_id=?1 ORDER BY DATE_FORMAT(t.create_date,'%Y') ASC' in /home/joyprice.core/Vendor/Doctrine/orm/lib/Doctrine/ORM/Query/QueryException.php:39
Stack trace:
#0 /home/joyprice.core/Vendor/Doctrine/orm/lib/Doctrine/ORM/Query/Parser.php(396): Doctrine\ORM\Query\QueryException::dqlError('SELECT t.id, t....')
#1 /home/joyprice.core/Vendor/Doctrine/orm/lib/Doctrine/ORM/Query/Parser.php(744): Doctrine\ORM\Query\Parser->syntaxError('end of string')
#2 /home/joyprice.core/Vendor/Doctrine/orm/lib/Doctrine/ORM/Query/Parser.php(229): Doctrine\ORM\Query\Parser->QueryLanguage()
#3 /home/joyprice.core/Vendor/Doctrine/orm/lib/Doctrine/ORM/Query/Parser.php(304): Doctrine\ORM\Query\Parser->getAST()
#4 /home/joyprice.core/Vendor/Doctrine/orm/lib/Doctrine/ORM/Query.php(233): Doctrine\ORM\Query\Parser->parse()

But have solution from DQL?

Sorry to ask the question in Issues Tracker.
Thanks






[DDC-1761] Small error on DQL documentation page Created: 03/Apr/12  Updated: 03/Apr/12

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

Type: Documentation Priority: Trivial
Reporter: Tim Roediger Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None


 Description   

On this document page:
http://docs.doctrine-project.org/projects/doctrine-orm/en/2.0.x/reference/dql-doctrine-query-language.html#dql-select-examples

The following lines are present:
$query = $em->createQuery('SELECT u.username, u.name FROM CmsUser u');
$users = $query->getResults(); // array of CmsUser username and name values
echo $users[0]['username'];

There is an error in:
$query->getResults();

It should read
$query->getResult();






Generated at Fri Sep 04 06:27:38 EDT 2015 using JIRA 6.4.10#64025-sha1:5b8b74079161cd76a20ab66dda52747ee6701bd6.