[MODM-176] includesReferenceTo query failure Created: 09/Nov/13  Updated: 20/Jan/14

Status: Open
Project: Doctrine MongoDB ODM
Component/s: Query Builder
Affects Version/s: 1.0.0BETA2
Fix Version/s: None

Type: Bug Priority: Minor
Reporter: Hussain Nazan Naeem Assignee: Jonathan H. Wage
Resolution: Unresolved Votes: 0
Labels: dql
Environment:

MongoDB shell version: 2.4.6



 Description   

Problem explained below as an example.
Suppose Car model has referenceMany relation with Wheel model.

/** @ReferenceMany(targetDocument="Wheel", inversedBy="car", simple=true) */
protected $wheels

note the simple=true param. If this param is specified then includesReferenceTo queries fail for Car model.

For example, following query fails:

$cars = $dm->createQueryBuilder('Car')
            ->field('wheels')
            ->includesReferenceTo($wheel)
            ->getQuery()
            ->execute();





[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-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-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-2204] Order by With Equals is not supported Created: 17/Dec/12  Updated: 20/Apr/15  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

Comment by Alexey Kosov [ 20/Apr/15 ]

It does not actually work.

http://stackoverflow.com/questions/25761989/doctrine-select-statement-using-equals-not-accepted





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





[DC-1068] Doctrine Query Language documentation issue Created: 26/May/15  Updated: 26/May/15

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

Type: Documentation Priority: Trivial
Reporter: Sergii Smirnov Assignee: Jonathan H. Wage
Resolution: Unresolved Votes: 0
Labels: documentation, dql


 Description   

There is error in documentation:

http://doctrine-orm.readthedocs.org/en/latest/reference/dql-doctrine-query-language.html

With COUNT DISTINCT:

$query = $em->createQuery('SELECT COUNT(DISTINCT u.name) FROM CmsUser');
$users = $query->getResult(); // array of ForumUser objects

Probably result is single scalar result, not a array of ForumUser objects






[DBAL-1081] Paginator - Query Limit for SQL Server - SqlServerPlatform.php Created: 16/Dec/14  Updated: 16/Jun/15

Status: Open
Project: Doctrine DBAL
Component/s: None
Affects Version/s: 2.5
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Maciej Grajcarek Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: dbal, dql, orderBy, sqlserver
Environment:

Windows



 Description   

Hi!
I have a problem with Query results limit when ordering by SUM of a field.

My query looks like this:

            SELECT ypk.name keyword, SUM(ypk.value) total
            FROM YpBundle:YellowPageKeyword ypk
              JOIN ypk.yellowpage yp
              JOIN yp.listing l
            WHERE l.customer = :customerId
              AND ypk.originDate >= :dateFrom
              AND ypk.originDate <= :dateTo
            GROUP BY ypk.nameCrc, ypk.name
            ORDER BY SUM(ypk.value) DESC

First I was catching following error:
message: "[Syntax Error] line 0, col 395: Error: Expected known function, got 'SUM'"
class: Doctrine\ORM\Query\QueryException

It only accourse if SUM is used in ORDER BY clause.

I have registered new class Sum which extends FunctionNode.

Now, query is build and executed but it has an error:

'SELECT * FROM 
     (SELECT y0_.name AS name_0, SUM(y0_.value) AS sclr_1, ROW_NUMBER() OVER (ORDER BY value) DESC) AS doctrine_rownum FROM yellowpage_keywords y0_ INNER JOIN yellowpages y1_ ON y0_.yellowpage_id = y1_.id INNER JOIN listings l2_ ON y1_.listing_id = l2_.id WHERE l2_.customer_id = ? AND y0_.origin_date >= ? AND y0_.origin_date <= ? GROUP BY y0_.name_crc, y0_.name) AS doctrine_tbl WHERE doctrine_rownum BETWEEN 1 AND 10 ORDER BY doctrine_rownum' with params ["111", "2013-01-01 00:00:00.000000", "2014-12-31 23:59:59.000000"]

The line :

ROW_NUMBER() OVER (ORDER BY value) DESC)

should look like

ROW_NUMBER() OVER (ORDER BY SUM(y0_.value) DESC)

In doModifyLimitQuery method I have modified:

                $pattern = sprintf('/%s\.%s\s+(?:AS\s+)?([^,\s)]+)/i', $column['table'], $column['column']);

to:

                $pattern = sprintf('/%s\.%s\s+(?:AS\s+)?([^,\s)]+)/i', str_replace('(', '\(', $column['table']), str_replace(')', '\)', $column['column']));

Now preg_match founds matching strings and OVER part of query is build correctly.

I checked other issues about this problem (which are marked as already fixed) and I have no idea why it's not working for me.

Thanks in advance!



 Comments   
Comment by Marco Pivetta [ 17/Dec/14 ]

Seems like a bit of information is missing: Is this issue related to the paginator API or not?

Comment by Maciej Grajcarek [ 17/Dec/14 ]

First of all - thank you for formatting my issue.
Secondly yes - issue is directly connected with paginator API.

Here is a code which should help you to replicate the problem:

            SELECT ypk.name keyword, SUM(ypk.value) total
            FROM YpBundle:YellowPageKeyword ypk
              JOIN ypk.yellowpage yp
              JOIN yp.listing l
            WHERE l.customer = :customerId
              AND ypk.originDate >= :dateFrom
              AND ypk.originDate <= :dateTo
            GROUP BY ypk.nameCrc, ypk.name
            ORDER BY SUM(ypk.value) DESC

        $dql = $this->getEntityManager()->createQuery($query);
        $dql
            ->setParameter('customerId', $customerId)
            ->setParameter('dateFrom', $dateFrom)
            ->setParameter('dateTo', $dateTo);
        $dql->setMaxResults(10);    

        $keywords = $dql->getArrayResult(); 
Comment by Marco Pivetta [ 17/Dec/14 ]

That doesn't involve the paginator, just DQL.

SUM() and computed values are not supported in the ORDER BY clause: you have to select them first. Try:

            SELECT ypk.name keyword, SUM(ypk.value) total
            FROM YpBundle:YellowPageKeyword ypk
              JOIN ypk.yellowpage yp
              JOIN yp.listing l
            WHERE l.customer = :customerId
              AND ypk.originDate >= :dateFrom
              AND ypk.originDate <= :dateTo
            GROUP BY ypk.nameCrc, ypk.name
            ORDER BY total DESC
Comment by Maciej Grajcarek [ 18/Dec/14 ]

If I will do it, it will result in a following query:

SELECT *
FROM (
	SELECT y0_.name AS name_0, SUM(y0_.value) AS sclr_1, ROW_NUMBER() OVER (ORDER BY sclr_1 DESC) AS doctrine_rownum 
	FROM yellowpage_keywords y0_
	INNER JOIN yellowpages y1_ ON y0_.yellowpage_id = y1_.id
	INNER JOIN listings l2_ ON y1_.listing_id = l2_.id
	WHERE l2_.customer_id = 111
		AND y0_.origin_date >= '2014-01-01'
		AND y0_.origin_date <= '2014-12-01'
	GROUP BY y0_.name_crc, y0_.name
) AS doctrine_tbl
WHERE doctrine_rownum BETWEEN 1 AND 10
ORDER BY doctrine_rownum

It's an incorrect query for an SQL Server. Take a look on this part:

ROW_NUMBER() OVER (ORDER BY sclr_1 DESC) AS doctrine_rownum

SQL Server do not support aliasing in OVER clause.

It should look like this, to work:

ROW_NUMBER() OVER (ORDER BY SUM(y0_.value) DESC) AS doctrine_rownum

It looks like this is the copy of this issue: http://www.doctrine-project.org/jira/browse/DBAL-788
I'm on doctrine version 2.5 which has patch from that issue included, but I have no idea why it's not working for me.

Comment by Steve Müller [ 24/Dec/14 ]

Maciej Grajcarek looks like it is an issue with your SUM function implementation. If you change your DQL to use ORDER BY COUNT(ypk.value) instead of ORDER BY SUM(ypk.value), does it work then? If so, there is something wrong with your SUM function and therefore not an issue with DBAL.

Comment by Steve Müller [ 24/Dec/14 ]

well forget about it I think I am wrong here.

Comment by Maciej Grajcarek [ 29/Dec/14 ]

Hi,
I will try to use COUNT as soon as I will be able to do that.

But I already tried other aggregation functions before and the result was exactly the same.

Comment by Luca Cerretani [ 16/Jun/15 ]

I get a similar error using this sql:

$sql = "SELECT table_one.id, table_one.number, table_two.name " . 
           "FROM table_one " .
	   "LEFT JOIN table_two	ON table_two.table_one_id = table_one.id " .
           "ORDER BY table_one.id DESC";

using the doModifyLimitQuery i get the uncorrect sql

SELECT * FROM (SELECT table_one.id, table_one.number, table_two.name, ROW_NUMBER() OVER (ORDER BY id DESC) AS doctrine_rownum  FROM table_one LEFT JOIN table_two ON table_two.table_one_id = table_one.id) AS doctrine_tbl WHERE doctrine_rownum BETWEEN 1 AND 1 ORDER BY doctrine_rownum

it should be

SELECT * FROM (SELECT table_one.id, table_one.number, table_two.name, ROW_NUMBER() OVER (ORDER BY table_one.id DESC) AS doctrine_rownum  FROM table_one LEFT JOIN table_two ON table_two.table_one_id = table_one.id) AS doctrine_tbl WHERE doctrine_rownum BETWEEN 1 AND 1 ORDER BY doctrine_rownum




[DBAL-397] PostgreSQL - getDateTimeTzFormatString() Created: 12/Dec/12  Updated: 17/Dec/12  Resolved: 16/Dec/12

Status: Resolved
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: 2.3.1
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Phill Pafford Assignee: Benjamin Eberlei
Resolution: Invalid Votes: 0
Labels: datetime, dql, format, postgresql
Environment:

PostgreSQL Database



 Description   

In:

  • vendor/doctrine/dbal/lib/Doctrine/DBAL/Platforms/PostgreSqlPlatform.php

The function:

getDateTimeTzFormatString()

only returns one datetime format:

    /**
     * {@inheritDoc}
     */
    public function getDateTimeTzFormatString()
    {
        //return 'Y-m-d H:i:sO'; // original format
        return 'Y-m-d H:i:s.uO'; // format also needed
    }


Here is the error I get using the original format:

Could not convert database value "2012-12-07 16:01:52.580789-05" to Doctrine Type datetimetz. Expected format: Y-m-d H:i:s.O

If I use this format: 'Y-m-d H:i:s.uO' it works.



 Comments   
Comment by Phill Pafford [ 12/Dec/12 ]

adding html tags for code to display correctly

Comment by Benjamin Eberlei [ 16/Dec/12 ]

DateTimeTz is created as 'TIMESTAMP(0) WITH TIME ZONE' on DBAL. If you have 'TIMESTAMP(6) WITH TIME ZONE' instead, then you have to create your own datatye.

Comment by Phill Pafford [ 17/Dec/12 ]

Why would this be a new data type? I understand how to fix the problem with creating my own data type to handle the formatting issue and would even suggest just overriding the data type with

        Type::overrideType('datetimetz', 'Doctrine\DBAL\Types\VarDateTimeType');
    Type::overrideType('datetime', 'Doctrine\DBAL\Types\VarDateTimeType');

But it's not a new data type, it's a formatting issue.





Generated at Mon Aug 31 02:53:39 EDT 2015 using JIRA 6.4.10#64025-sha1:5b8b74079161cd76a20ab66dda52747ee6701bd6.