[DC-972] MySQL field aliases with triple ticks Created: 16/Feb/11  Updated: 18/Jul/13

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

Type: Bug Priority: Blocker
Reporter: Roland Huszti Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 1
Labels: None
Environment:

MySQL 5, PHP 5


Attachments: File BaseTerritoryCombined.php    

 Description   

In revision 7691 something has happened. Ever since I updated my Doctrine to that revision all my queries having " ... fieldname AS aliasname ... " go crazy and make the PHP to throw an exception, like this:

'Doctrine_Connection_Mysql_Exception' with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 't.`id`' in 'field list'. Failing Query:
"SELECT `t`.`id` AS `t_id`, `t`.```id``` AS `t0`, `t`.```name``` AS `t1`, `t`.`id` AS `t0`, `t`.`name` AS `t_1` FROM `territoryCombined` `t` ORDER BY `t`.`name` asc"'
in ...path here.../doctrine/lib/Doctrine/Connection.php:1082

The problem is that the DQL parser somewhere along the process encapsualtes aliases in ticks, but then it does it again in lib/Doctrine/Formatter.php : quoteIdentifier() , which is called in lib/Doctrine/Connection : quoteIdentifier() , which is called in lib/Doctrine/Query.php : processPendingFields() @ between lines 485 and 512. The problem is that by the time the alias name gets to line 507 it is already encapsualted in ticks, but it does it again. At the end we end up with ```alias``` , which is not good.

It only happens to aliases. If I say select('*') or select("t.id, t.name") then it executes properly. Only the aliases couse problems.

A test query:

$vTerritories = Doctrine_Query::create()
->select("t.id as territory_id, t.name as territory_name")
->from('TerritoryCombined t')
->orderBy('t.name asc')
->fetchArray();

MY PROPOSED PATCH:

If I change the Formatter::quoteIdentifier() to this:

public function quoteIdentifier($str, $checkOption = true)

{ $tmp = $this->conn->identifier_quoting; // I move up this line to here because I need it if ( (substr($str, 0, 1) == $tmp['start']) && (substr($str, -1) == $tmp['end']) ) return $str; // new line; is it already quoted? if yes, then don't do it again // the rest is unchanged }

then it works correctly. Please note I only tested that in MySQL, as we use MySQL in all our projects.



 Comments   
Comment by Mishal [ 18/Jul/13 ]

Bringing dead things back to life

Fixed in: https://github.com/mishal/doctrine1/commit/aca0a00c2278498aef997d208cc91ecd52a9c0d3





[DC-926] Doctrine ignored PORTABILITY_FIX_CASE and ATTR_FIELD_CASE settings when building query Created: 12/Nov/10  Updated: 12/Nov/10

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

Type: Bug Priority: Blocker
Reporter: Dziamid Zayankouski Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Tested on CentOS 5, Ubuntu 10.4, MYSQL and MSSQL databases



 Description   

Setting:
$conn->setAttribute(Doctrine_Core::ATTR_PORTABILITY, Doctrine_Core::PORTABILITY_FIX_CASE)
$conn->setAttribute(Doctrine_Core::ATTR_FIELD_CASE, CASE_UPPER)

I expect column names in built queries to be uppercase, but they remain to be lowercase.






[DC-918] Causing ORA-01791 when try to sort on relation field and use limit in query to Oracle DB Created: 06/Nov/10  Updated: 06/Nov/10

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

Type: Bug Priority: Blocker
Reporter: Dmitriy Assignee: Jonathan H. Wage
Resolution: Unresolved Votes: 2
Labels: None
Environment:

Windows 2003 Server, Oracle 10g, Symfony 1.4.8



 Description   

Schema in yml format
in Symfony it looks more simple, but i'm going to show relation in detail

PrType:
  columns:   
    name:                   { type: string(255), notnull: true }                          

PrTypeTranslation:
  columns:   
    id:                     { type: integer, notnull: true }
    name:                   { type: string(255), notnull: true }
    lang:                   { type: string(255), notnull: true }
  relations:
    PrType:                 { onDelete: CASCADE, local: id_id, foreign: id, foreignAlias: Translation }

When i try to execute this code:

   $q = Doctrine_Query::create()
            ->from('PrType tp')
            ->leftJoin('tp.Translation t WITH t.lang = ?', 'ru')
            ->orderBy('t.name')
            ->limit(10);

doctrine executes next statement:

SELECT "p"."id", "p2"."name" AS "p2__name", "p2"."lang" AS "p2__lang"
FROM "pr_type" "p"
LEFT JOIN "pr_type_translation" "p2" ON "p"."id" = "p2"."id" AND ("p2"."lang" = :oci_b_var_1)
WHERE "p"."id" IN (
              SELECT a."id" FROM ( 
                          SELECT DISTINCT "p3"."id"
                          FROM "pr_type" "p3"
                          INNER JOIN "pr_type_translation" "p4" ON "p3"."id" = "p4"."id" AND ("p4"."lang" = 'ru') 
                          ORDER BY "p4"."name" ) a 
                          WHERE ROWNUM <= 10) 
ORDER BY "p2"."name"

This sql code produces next error

ORA-01791: not a SELECTed expression

Error occures, because (from ORACODE)

There is an incorrect ORDER BY item. The query is a SELECT DISTINCT query with an ORDER BY clause. In this context, all ORDER BY items must be constants, SELECT list expressions, or expressions whose operands are constants or SELECT list expressions



 Comments   
Comment by Dmitriy [ 06/Nov/10 ]

Some very similar issue were reported and resolved here http://trac.doctrine-project.org/ticket/1038.

Comment by Dmitriy [ 06/Nov/10 ]

Reason of issue was founded. It appears because i'm using oci8 driver, and this drivername not be listed in if statement on line 1401 in Doctrine/Query.php:

LINE 1401: if ($driverName == 'pgsql' || $driverName == 'oracle' || $driverName == 'oci' || $driverName == 'mssql' || $driverName == 'odbc') {

I changed to:

LINE 1401: if ($driverName == 'pgsql' || $driverName == 'oracle' || $driverName == 'oci' || $driverName == 'oci8' || $driverName == 'mssql' || $driverName == 'odbc') {

Sorry, but i don't know how to create patch diff file.





[DC-627] Work on link-table and leftJoin Created: 13/Apr/10  Updated: 13/Apr/10

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

Type: Bug Priority: Blocker
Reporter: Thomas Tourlourat - Armetiz Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Windows Seven - WAMP 2.0i


Attachments: PNG File composition.png    

 Description   

Looking at the attachment to see the little schema.
Tree table : items, items_children and children.

The link-table items_children is here to create a many-to-many relationships between Items and Children.

So, after configure the YAML and generated Models.
We can do something like that :

$query = Doctrine_Query::create ()
->select ("items.title, children.title")
->from ("Item items")
->leftJoin ("item.children children");
$items = $query->execute ();

$items //An array of items
$items[0]->title //A string
$items[0]->children //An array of children

The SQL Output of the query is :

SELECT `i`.`id_item` AS `i_id_item`, `i`.`title` AS `ititle`, `c`.`id_child` AS `cid_child`, `c`.`title` AS `c_title` FROM `items` `i` LEFT JOIN `items_children` `i2` ON (`i`.`id_item` = `i2`.`id_item`) LEFT JOIN `children` `c` ON `i2`.`id_child` = `c`.`id_child`

All array are order by something, in this case it's a natural order which come from the items_children order into database.

Now, I want to apply a condition, or a filter on the items_children table.
If I translate my demand to SQL Query :

SELECT `i`.`id_item` AS `i_id_item`, `i`.`title` AS `ititle`, `c`.`id_child` AS `cid_child`, `c`.`title` AS `c_title` FROM `items` `i` LEFT JOIN `items_children` `i2` ON (`i`.`id_item` = `i2`.`id_item`) LEFT JOIN `children` `c` ON `i2`.`id_child` = `c`.`id_child` ORDER BY `c1`.`index`

Or something crazy :

SELECT `i`.`id_item` AS `i_id_item`, `i`.`title` AS `ititle`, `c`.`id_child` AS `cid_child`, `c`.`title` AS `c_title` FROM `items` `i` LEFT JOIN `items_children` `i2` ON (`i`.`id_item` = `i2`.`id_item`) LEFT JOIN `children` `c` ON `i2`.`id_child` = `c`.`id_child` WHERE `c1`.`index` > 2

But how can I do that with DQL ? Because I have no access to the Link-table.

Link-Table are use to create many-to-many, that could be OOP array. So we have to be allowed to add "index" to order the result. Also, we have to be allowed to add condition on this tables, like "datePromote" or simply "enabled".

The solution should be on the following DQL Query :

$query = Doctrine_Query::create ()
->select ("items.title, children.title")
->from ("Item items")
->leftJoin ("item.children children")
->leftJoin ("ItemsChildren link_table")
->orderBy ("link_table.index")
$items = $query->execute ();

But the SQL Output is :

SELECT `i`.`id_item` AS `i_id_item`, `i`.`title` AS `ititle`, `c`.`id_child` AS `cid_child`, `c`.`title` AS `c_title` FROM `items` `i` LEFT JOIN `items_children` `i2` ON (`i`.`id_item` = `i2`.`id_item`) LEFT JOIN `children` `c` ON `i2`.`id_child` = `c`.`id_child`, `items_children` `i3` ORDER BY `i3`.`index`

To conclude, it's a blocker problem. Because I can't use a standard DB schema.

Regards,
Armetiz.






[DC-665] Named parameters doesn't work on MSSQL anymore Created: 05/May/10  Updated: 22/Sep/10

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

Type: Bug Priority: Blocker
Reporter: Dennis Pettersson Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Windows Server 2003. PHP 5.2.12. MS SQL Server 2008



 Description   

After upgrading to Doctrine 1.2.2 queries with named arguments doesn't work anymore.
Whenever querying like
$query = Doctrine_Query::create();
$customer = $query->from('Customers')
->where("id = :id"
, array('id'=>$user['customer_id']))
->fetchOne(array(), Doctrine::HYDRATE_ARRAY);

I get the error
SQLSTATE[HY000]: General error: 10007 Incorrect syntax near ':'. [10007] (severity 5)

If changing the query to
$query = Doctrine_Query::create();
$customer = $query->from('Customers')
->where("id = ?"
, array($user['customer_id']))
->fetchOne(array(), Doctrine::HYDRATE_ARRAY);
it works fine.
Testing with MySQL works fine, so it seems contained to MSSQL



 Comments   
Comment by Dennis Pettersson [ 06/May/10 ]

Should mention I upgraded from 1.1.6 to 1.2.2, so the problem might be present in earlier 1.2.x versions as well.





[DC-1009] save() also updates fields which should not be Created: 08/Jun/11  Updated: 08/Jun/11

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

Type: Bug Priority: Critical
Reporter: Yan Urquiza Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Windows server 2003 PHP 5.2.17 / XP PRO 32bits XAMPP PHP 5.3.5
MSSQL / MYSQL
Symfony 1.4.6


Attachments: PNG File after_with_execute.PNG     PNG File after_with_save.PNG     PNG File before.PNG     File retrieveByExamBatchStatus.php     File schema.yml    

 Description   

When I want to do a simple update like this :

$batches = ExamResultsBatchTable::getInstance()->retrieveByExamBatchStatus(ExamResultsBatch::valid_status_code);
foreach($batches as $batch)
{
$batch->setExamBatchStatusId($batchStatusId);
$batch->setStatusDate(date('Y-m-d'));
$batch->save();
}
Only exam_batch_status_id and status_date should be updated (see screenshot before), but columns exam_batch_status_id ,status_date AND exam_subject_id are updated,with the same value (23) (screenshot after_with_save).

If I run this:
$toto = Doctrine_Query::create()
->update('ExamResultsBatch erb')
->set('erb.exam_batch_status_id', 23)
->set('erb.status_date', date('Y-m-d'))
>where('erb.id = ?' , $batch>getId())
->execute();
Everything is correctly done.

here is the simpliest case.

The same problems are signaled on other tables in the database, but different tables can be impacted by one save() (the execute() query still works fine).

Example : 2 foreign tables will be updated , even if the save() action should only concern the main table, and one field (which is not a foreign key).
The corresponding foreign key fields in the 2 foreign tables, will be updated with the value given (here 23).

Because save() is used in a lot of different places in our app, I need to find a solution to fix save(), or if not possible to override it to run a execute()like query.

Thanks for your help.
Don't hesitate to ask if you want more details.

Yan






[DC-815] Model's default sorting breaks subqueries Created: 11/Aug/10  Updated: 14/Mar/11

Status: Open
Project: Doctrine 1
Component/s: Query, Record
Affects Version/s: 1.2.2
Fix Version/s: None

Type: Bug Priority: Critical
Reporter: Jacek Jędrzejewski Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 1
Labels: None

Attachments: File DC9999TestCase.php    

 Description   

It works except subqueries. Main table's order is added to subquery and vice versa. SQL query looks like this:

{{
SELECT t.id AS t_id FROM ticket_d_c9999_record t WHERE (t.id = (SELECT MAX(t2.id) AS t20 FROM ticket_d_c9999__record t2 ORDER BY t.id ASC, t2.id ASC)) ORDER BY t.id ASC, t2.id ASC
}}

Query fails because there is >>no such column "t.id"<<.

I include a testcase when it is all visible.

BTW. there is nothing about that feature (and relation orderBy) in docs. It is only in UPGRADE file.



 Comments   
Comment by Jacek Jędrzejewski [ 25/Aug/10 ]

Anyone?

Comment by Alan Betteridge [ 10/Mar/11 ]

Am having the same issue!

Eventually found the orderBy option on both the model and on relationships and was over joyed as I'd been trying to find a way of doing this, but it didn't work!!

Found the patch http://www.doctrine-project.org/jira/browse/DC-651 which solved the first problem I encountered but now I'm getting this.

Comment by Alan Betteridge [ 14/Mar/11 ]

Had a look at the code and tried only setting the orderBy if the current component is actually referenced in the from sql part of the query.

Looking at what is selected from, $this->_sqlParts['from'], it appears that the main table ($map['table']->getTableName()) and $sqlAlias is include with a zero index and joined tables are keyed by their $alias (or at least the content of these variables within the loop).

From this I believe I could detect if the current entry in the loop was from a table that was in the current "FROM" part of the query by looking for the table name and alias as an entry or the current alias as a key in the "FROM" array.

Within my sub query the orderBy valeus would still get applied but only in the subquery and not in the main query where they were included before, out of scope.

With patch DC-651 applied my code in Query.php at line 1315 noew looks as follows:


                // Note: Only include orderBy values for tables we're actually selecting from (both the root table or
                // tables referenced from it)
                if (in_array("{$map['table']->getTableName()} {$sqlAlias}", $this->_sqlParts['from']) || array_key_exists($alias, $this->_sqlParts['from'])) {
                    if (isset($map['relation'])) {
                        if (isset($map['ref'])) {
                            $orderBy = $map['relation']['refTable']->processOrderBy($sqlAlias, $map['relation']['orderBy'], true);
                            if ($map['relation']['orderBy'] && $orderBy == $map['relation']['orderBy']) {
                                $orderBy = $map['relation']->getOrderByStatement($sqlAlias, true);
                            }
                        } else {
                            $orderBy = $map['relation']->getOrderByStatement($sqlAlias, true);
                            if ($orderBy == $map['relation']['orderBy']) {
                                $orderBy = null;
                            }
                        }
                    } else {
                        $orderBy = $map['table']->getOrderByStatement($sqlAlias, true);
                    }
                } else {
                        $orderBy = null;
                }

Am I correct in my assumptions?





[DC-347] AddPendingJoinConditions doesn't work if alias equals model name Created: 09/Dec/09  Updated: 29/Jan/10

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

Type: Bug Priority: Critical
Reporter: Christian Jaentsch Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 0
Labels: None

Attachments: File AliasEqualsModelNameTestCase.php    

 Description   

If the alias in a from-query is equal to the model name, I get an exception when executing the query after adding a "pendingJoinCondition".

See attached testCase.



 Comments   
Comment by David Abdemoulaie [ 29/Jan/10 ]

I fail to see why you would use an alias that is equal to the model name.

Comment by Christian Jaentsch [ 29/Jan/10 ]

To clarify again: Using an alias equal to the model name is the same as not using an alias at all. The built sql is the same afterwards (and the addPendingJoinCondition behaviour is the same as well).

I would not use an alias equal to the model name myself, but sometimes this is used in Doctrine core, e.g. in Doctrine_Relation_ForeignKey around line 60. So to solve the problem either all the core queries, where no alias or an alias equal to the model name is used, have to be rewritten or the behaviour of a respective query has to be changed somehow so that applying an addPendingJoinCondition is possible even in the given case.





[DC-371] Lazy loading - doctrine makes extra queries into db Created: 19/Dec/09  Updated: 23/Dec/10

Status: Open
Project: Doctrine 1
Component/s: Behaviors, Documentation, Query, Record
Affects Version/s: 1.2.0-BETA3
Fix Version/s: None

Type: Bug Priority: Critical
Reporter: Roman Drapeko Assignee: Jonathan H. Wage
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Symfony 1.4, Doctrine Version: 1.2.0-BETA3



 Description   

Just downloaded symfony 1.4

First of all I have a query:

$q = \Doctrine_Query::create()
->select('u., ur.')
->from('UserDb u')
->leftJoin('u.RealUserDetailsDb ur')
->leftJoin('u.MockUserDetailsDb um')
->where('u.id = :user_id')
;
$user = $q->fetchOne(array(':user_id' => $uid));

After that I'm accessing the fields of this object:

$userArray = array(
'id' => $this->getUser()->getId(),
'real_user_details_id' => $this->getUser()->getRealUserDetailsId(),
'mock_user_details_id' => $this->getUser()->getMockUserDetailsId(),
'real_user_details' => array(),
'mock_user_details' => array()
);

This is the actual queries into DB:

NR1:
SELECT u.id AS u_id, u.user_real_id AS uuser_real_id, u.user_mock_id AS uuser_mock_id, u2.id AS u2id, u2.nickname AS u2nickname, u2.email AS u2_email FROM user u LEFT JOIN user_real u2 ON u.user_real_id = u2.id LEFT JOIN user_mock u3 ON u.user_mock_id = u3.id WHERE (u.id = :user_id)

NR2:
SELECT u.id AS u_id, u.user_real_id AS uuser_real_id, u.user_mock_id AS u_user_mock_id FROM user u WHERE (u.id = '1') LIMIT 1

As you can see there are TWO queries however there should be only one query. The problem is that u.user_real_id is NULL in database and when I do 'real_user_details_id' => $this->getUser()->getRealUserDetailsId() doctrine does not have enough intelligence to understand that these fields have been already requested in NR1. If I comment this field, everything works well.

SURPRISE!
And now a surprise... if I modify a little bit my first query: ">select('u.')" instead of , ">select('u., ur.*')" it WON'T make TWO queries. It will make ONLY ONE!

As you understand this a very critical bug and of course our system won't go to production with this bug.

P.S. Is it possible to turn off the lazy loading in doctrine?



 Comments   
Comment by Roman Drapeko [ 17/Jan/10 ]

Any comments? Will it be fixed??

Comment by Jonathan H. Wage [ 01/Mar/10 ]

Hi, I'd like to take a look but can you make a failing test case that I can run so that I can see if I can come up with a patch that fixes your case and doesn't break anything else.

Comment by Luke Winiarski [ 01/Jun/10 ]

Hi

I had similar problem but after several hours i did work it out

Try to make get method in your model for getting field which has NULL value in database

public function getUserRealId()

{ return $this->_get("user_real_id", false); }

by making second argument false u force doctrine not to lazy load value and extra sql query is not created

regards

Comment by Jonathan H. Wage [ 08/Jun/10 ]

Has anyone been able to reproduce this in a test case? I am not having much luck so far.

Comment by Gennady Feldman [ 23/Dec/10 ]

I've seen this a ton of times. Basically when it loads related records through the Hydrator using leftJoin() and gets NULLs back. BUT it doesn't save the fact that the related records are NULL. So when you actually do call to getRelated objects it sees that it doesn't have the value cached and runs the query again.

Let me know if I should show you the problem in the Doctrine code base.





[DC-1055] Bug in select query when executed against postgreSQL Created: 25/May/12  Updated: 25/May/12

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

Type: Bug Priority: Major
Reporter: Damian Bergantinnos Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 0
Labels: None
Environment:

symfony-1.4.17 php 5.3.5 apache 2.2.17 WIndows xp/7 PostgreSQL 9.1.2


Attachments: File schema.rar    

 Description   

In the attached Squema run this query against postgreSQL.
(it runs ok In mysql)

$lang = 'en';
$session = 1;
$q = Doctrine_Query::create()
->from('Sys_Trace t')
->leftJoin('t.Sys_Session s')
->leftJoin('t.Translation tr WITH tr.lang = ?', array($lang))
->leftJoin('t.Sys_Oper so')
->leftJoin('so.Translation tr2 WITH tr2.lang = ?', array($lang))
>where('t.session_id = ?', array($session));

SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for integer: "en"






[DC-1041] Using ->limit() in conjunction with many-to-many with mysql generates wrong SQL Created: 30/Nov/11  Updated: 30/Nov/11

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

Type: Bug Priority: Major
Reporter: Evgeniy Afonichev Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 0
Labels: None
Environment:

mysql



 Description   

Using ->limit() in conjunction with many-to-many relationships with mysql leads to strange SQL generated. The condition id IS NULL is added in such case which is not correct at all.

Here's example schema

User:
  columns:
    username: { type: string(255) }
  relations:
    Operators:
      foreignAlias: Users
      class:        Operator
      refClass:     OperatorUser

Operator:
  columns:
    username: { type: string(255) }
    type:     { type: integer }


OperatorUser:
  columns:
    user_id:      { type: integer }
    operator_id:  { type: integer }
  relations:
    Operator:
      foreignAlias: OperatorUser
    User:
      foreignAlias: OperatorUser

And here's query which generates wrong SQL

Doctrine_Core::getTable('User')
  ->createQuery('User')
  ->leftJoin('User.Operators Operator')
  ->addWhere('Operator.type = ?', 1)
  ->limit(10)
  ->offset(0)
  ->execute()
;

Expected SQL generated:

SELECT u.id AS u__id, u.username AS u__username, o.id AS o__id, o.username AS o__username, o.type AS o__type
FROM user u
LEFT JOIN operator_user o2  ON (u.id = o2.user_id)
LEFT JOIN operator o        ON o.id = o2.operator_id
WHERE (o.type = '1')
LIMIT 10

Actual SQL generated:

SELECT u.id AS u__id, u.username AS u__username, o.id AS o__id, o.username AS o__username, o.type AS o__type
FROM user u
LEFT JOIN operator_user o2  ON (u.id = o2.user_id)
LEFT JOIN operator o        ON o.id = o2.operator_id
WHERE
  u.id IS NULL # is not expected
  AND (o.type = '1')
# there's no LIMIT clause

Seems like here's code which causes the bug https://github.com/doctrine/doctrine1/blob/master/lib/Doctrine/Query.php#L1307






[DC-1010] When putting a subquery in the where clause which includes a join and a limit the limit subquery algorithm mistakenly modifies the subquery Created: 21/Jun/11  Updated: 21/Jun/11

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

Type: Bug Priority: Major
Reporter: will ferrer Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 0
Labels: None
Environment:

XP Xamp



 Description   

I have fixed this in my own version of doctrine but unfortunately I am to far diverged from the trunk to offer a patch.

here is a test case:

public function testSubqueryInWhereWithJoinAndLimit()
    {
        $q = new Doctrine_Query();
        $q->select('u.id');
        $q->from('User u');
        $q->where('u.id NOT IN (SELECT a.id FROM User u2 LEFT JOIN u2.Album a LIMIT 1)');
        $this->assertEqual($q->getSqlQuery(), 'SELECT e.id AS e__id FROM entity e WHERE (e.id NOT IN (SELECT a.id AS a__id FROM entity e2 LEFT JOIN album a ON e2.id = a.user_id WHERE (e2.type = 0) LIMIT 1) AND (e.type = 0))');
    }

To fix the issue I changed this line in Doctrine_Query as follows:

if ( ( ! empty($this->_sqlParts['limit']) || ! empty($this->_sqlParts['offset'])) && $needsSubQuery && $limitSubquery) {

=

if ( ( ! empty($this->_sqlParts['limit']) || ! empty($this->_sqlParts['offset'])) && $needsSubQuery && $limitSubquery && !$this->isSubquery()) {

Hope that helps.

Sincerely

Will Ferrer






[DC-1008] missing oci_type in Doctrine_Adapter_Statement_Oracle->bindParam Created: 31/May/11  Updated: 17/Apr/14

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

Type: Bug Priority: Major
Reporter: Tomasz Madeyski Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 0
Labels: None


 Description   

in bindParam method there is:
switch ($type) {
case Doctrine_Core::PARAM_STR:
$oci_type = SQLT_CHR;
break;
}
I think there should be other oci_types too. I had to add:
case Doctrine_Core::PARAM_INT:
$oci_type = SQLT_INT;
because I got ORA-06502: PL/SQL: numeric or value error: character string buffer too small. while executing
$stmt->bindParam(":result", $result, Doctrine_Core::PARAM_INT);

After adding SQLT_INT everything is ok






[DC-1000] Wrong parsing on HAVING clause Created: 28/Apr/11  Updated: 17/Apr/14

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

Type: Bug Priority: Major
Reporter: Pierrot Evrard Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 0
Labels: None
Environment:

symfony 1.4.12-DEV / Windows XP / Apache 2.0 / MySQL 5.1.37 / PHP 5.3.0


Attachments: Text File Doctrine-DC-1000.patch    

 Description   

With Doctrine::ATTR_QUOTE_IDENTIFIER enabled, when you launch a query with a complex having clause, Doctrine_Query_Having class does not handle it correctly.

By example, when you track the having clause interpretation:

$query->addHaving( 'SUM( IF( s.id = ? , 1 , 0 ) ) = 0' , 7 );

At some point, Doctrine_Query_Having at line 70 return something like "`s10`.`id = ?`" instead of "`s10`.`id` = ?".

I just fix it using:

return $this->query->parseClause($func);

instead of:

return $this->_parseAliases($func);

Now, the parseAliases function is not used anymore...

See patch attached...

Loops






[DC-999] Query cache key can be incorrectly generated Created: 28/Apr/11  Updated: 17/Apr/14

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

Type: Bug Priority: Major
Reporter: Jakub Zalas Assignee: Roman S. Borschel
Resolution: Unresolved Votes: 0
Labels: None


 Description   

1. We have two versions of the application on the same server.
2. Second application has an updated database. New field is added to one of the models.
3. When the second app is hit first, query is stored in APC.
4. First app finds cached query and tries to call it. Exception is thrown as it doesn't know anything about the new field yet.

Situation often happens on shared development machine when one developer adds a field but others don't have in their models yet. It also happens on staging server if it's shared with production.

I suspect it only affects queries without explicitly listed fields.

To quickly fix the issue in my symfony project I extended Doctrine_Cache_Apc to implement namespaces (https://gist.github.com/944524). More appropriate place to fix it would be Doctrine_Query_Abstract::calculateQueryCacheHash().



 Comments   
Comment by Pablo Grass [ 27/Jun/11 ]

Could this be a duplicate of http://www.doctrine-project.org/jira/browse/DC-389 ?
Are you querying a model with a *-to-many relation and applying a limit?

See also http://www.doctrine-project.org/documentation/manual/1_2/en/dql-doctrine-query-language:limit-and-offset-clauses:the-limit-subquery-algorithm





[DC-997] Doctrine collections are overwritten when created by inner join queries that agree on the WHERE Created: 13/Apr/11  Updated: 17/Apr/14

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

Type: Bug Priority: Major
Reporter: Richard Forster Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 0
Labels: None
Environment:

OS X 10.6.6 with PHP 5.3.3, Windows with PHP 5.3.1


Attachments: File models.yml     Zip Archive test.zip    

 Description   

In brief:
Doing $result1 = Doctrine_Query::create()>... followed by $result2 = Doctrine_Query::create()>... can lead to a situation where the content of $result1 has become the value in $result2.

In detail:
The attached models.yml defines two simple tables with a One-to-Many relationship; we have people and names and each person can have multiple names. The DB can be propagated along the lines of:

INSERT INTO `tblname` VALUES (1,1,'alpha'),(2,2,'beta'),(3,3,'gamma'),(4,4,'delta'),(5,5,'epsilon'),(6,1,'aleph');
INSERT INTO `tblperson` VALUES (1),(2),(3),(4),(5);

Applying the query:

$results1 = Doctrine_Query::create()
->from('Person ppa')
->innerJoin('ppa.Name n')
->where('ppa.id = ?', 1)
->andWhere('n.text = ?', 'alpha')
->execute()
->getFirst()
->Name;

and then producing output though

print 'Results (1): '.count($results1)."\n";
foreach ($results1 as $result) print $result['text'] . "\n";
print "\n\n";

produces the expected:

Results (1): 1
alpha

Doing a similarly query to a new variable:

$results2 = Doctrine_Query::create()
->from('Person ppa')
->innerJoin('ppa.Name n')
->where('ppa.id = ?', 1)
->andWhere('n.text = ?', 'aleph')
->execute()
->getFirst()
->Name;

and printing with

print 'Results (2): '.count($results2)."\n";
foreach ($results2 as $result) print $result['text'] . "\n";
print "\n\n";

produces the expected:

Results (2): 1
aleph

but printing out the first result object again at this point gives:

Results (1): 1
aleph

which is unexpected - "aleph" rather than "alpha".

If, the second query was altered to

->where('ppa.id = ?', 2)
->andWhere('n.text = ?', 'beta')

then all three output results are as expected.

test.zip contains corresponding test files.






[DC-996] UPDATE query generate ambiguous statement Created: 13/Apr/11  Updated: 13/Apr/11

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

Type: Bug Priority: Major
Reporter: John Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 2
Labels: None
Environment:

MAMP on MacBook Pro 10.6.7, with Symfony 1.4.9



 Description   

When creating an UPDATE query, the table names are not aliased like in a SELECT statement. This causes ambiguous column names when JOINING in an UPDATE.

E.g.
$q = $this->createQuery('st')
->update('SomeTable st')
->set('st.position','st.position + 1')
->leftJoin('st.SomeOtherTable sot ON st.some_id = sot.id')
->where('st.id <> ?', $someId)
->andWhere('sot.some_column = ?', $someValue)

The generated SQL for this is :
UPDATE some_table
LEFT JOIN some_other_table sot ON st.some_id = sot.id
SET position = position + 1, updated_at = 2011-04-13 11:01:03, updated_at = 2011-04-13 11:01:03
WHERE (id <> 4 AND some_column = 7)

Clearly here "updated_at" and "id" are ambiguous columns. Why the tables are not automatically aliased with unique aliases like in a SELECT statement, and the aliases written before the column name ?

Thanks.






[DC-973] Statements with empty results are not correctly closed Created: 17/Feb/11  Updated: 17/Feb/11

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

Type: Bug Priority: Major
Reporter: Miloslav "adrive" Kmet Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 0
Labels: None


 Description   

Statements that return no result are not correctly closed in Doctrine_Hydrator_Graph::hydrateResultSet().

Oracle has limited number of opened cursors, and this bug prevents unsing doctrine in batch task like indexing models with sfSolrPlugin.

Oracle throws an error `ORA-01000: maximum open cursors exceeded : ` in my case after indexing only 100 records.

I'll send a pull request via github for this issue.






[DC-944] Precedence problem in SQL generation allows bypass of pending joins Created: 03/Dec/10  Updated: 17/Apr/14

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

Type: Bug Priority: Major
Reporter: Walter Hop Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 0
Labels: None
Environment:

PHP 5.2, 5.3


Attachments: File Query.pendingjoin.diff    

 Description   

'Pending join conditions' are used by listeners to inject extra SQL conditions into a query. They are often used to add basic constraints on every query. An example is the bundled SoftDelete template. Its listener adds extra constraints such as s.deleted_at IS NULL to a query, to make sure that deleted rows are never retrieved on a query.

However, in the emitted SQL, Doctrine_Query does not use parentheses to group normal SQL conditions together. The pending join condition is simply added to the string without encapsulating existing expressions. This makes it possible to bypass the pending join conditions entirely by using the OR operator.

Example

For instance, the following query exhibits this problem:

$query = Doctrine_Query::create()
->from("SoftDeleteTest")
->where("name=?", "faulty")
->orWhere("name=?", "faulty");

This query emits the following SQL:

SELECT s.name AS s_name, s.deleted_at AS s_deleted_at FROM soft_delete_test s WHERE (s.name = 'faulty' OR s.name = 'faulty' AND (s.deleted_at IS NULL))

which returns also a deleted row.

Expected behavior

One would expect the pending join conditions always to hold, and to have precedence over regularly added SQL conditions. This could be accomplished in the most simple fashion by:

SELECT s.name AS s_name, s.deleted_at AS s_deleted_at FROM soft_delete_test s WHERE ( ( s.name = 'faulty' OR s.name = 'faulty' ) AND (s.deleted_at IS NULL));

As the existing expressions are now encapsulated by parentheses, it is no longer possible to bypass the pending join conditions injected by the query listener.

Full test case details:

init.sql

create database softdelete;
grant all privileges on softdelete.* to softdelete@localhost identified by 'uahwqeruwer';

use softdelete;
CREATE TABLE soft_delete_test (name VARCHAR(255), 
    deleted_at DATETIME DEFAULT NULL, 
    PRIMARY KEY(name)) ENGINE = INNODB;

insert into soft_delete_test values ('fine', null);
insert into soft_delete_test values ('faulty', now());

run.php

<?php

require "./1.2.3/lib/Doctrine.php";

spl_autoload_register(array('Doctrine', 'autoload'));

require "SoftDeleteTest.php";

$conn = Doctrine_Manager::connection("mysql://softdelete:uahwqeruwer@localhost/softdelete");
$conn->setAttribute(Doctrine::ATTR_USE_DQL_CALLBACKS, true);

$query = Doctrine_Query::create()
    ->from("SoftDeleteTest")
    ->where("name=?", "faulty")
    ->orWhere("name=?", "faulty");

$found = $query->execute();
foreach ($found as $f) {
    echo "ERROR! Found a deleted row: $f->name\n";
}
echo "Done.\n";

SoftDeleteTest.php (copied from Doctrine manual)

<?php

class SoftDeleteTest extends Doctrine_Record
{
    public function setTableDefinition()
    {
        $this->hasColumn('name', 'string', null, array(
                'primary' => true
            )
        );
    }

    public function setUp()
    {
        $this->actAs('SoftDelete');
    }
}


 Comments   
Comment by Walter Hop [ 03/Dec/10 ]

Fixing quote formatting

Comment by Walter Hop [ 03/Dec/10 ]

Final formatting fixes.





[DC-937] Cross Schema stored procedures are not recognized Created: 22/Nov/10  Updated: 08/Dec/10

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

Type: Bug Priority: Major
Reporter: will ferrer Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 0
Labels: None
Environment:

XP Xamp


Attachments: Text File DC_937_fix.patch    

 Description   

When you call a stored procedure from a schema other than that of the current connection: [schema_name].[stored_procedure_name]([stored_procedure_arguments]) doctrine miss understands the string and throws a "Couldn't get short alias for" exception.

I fixed this by adding some more regex to the getExpressionOwner method of the Query Class.

I will post the patch shortly.

Will Ferrer



 Comments   
Comment by will ferrer [ 08/Dec/10 ]

Fixed an issue where the code wouldn't work with calls to stored procedure that were nestted in groups in selects.





[DC-927] Query with left join and group clause returns only one row, even though there are multiple results Created: 14/Nov/10  Updated: 19/May/11

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

Type: Bug Priority: Major
Reporter: Bart van den Burg Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 4
Labels: None
Environment:

Windows 7-64 bit
Symfony 1.4.8



 Description   

under certain circumstances, Doctrine will only return one result out of a bunch of results, for example:

$ symfony doctrine:dql "from Tafel t, t.Reservering r where t.restaurant_id=4 select date(t.tijd), count(t.id) tafels, count(r.id) reserveringen group by date(t.tijd)"
>> doctrine executing dql query
DQL: from Tafel t, t.Reservering r where t.restaurant_id=4 select date(t.tijd), count(t.id) tafels, count(r.id) reserveringen group by date(t.tijd)
found 2 results
-
date: '2010-11-14'
tafels: '1'
reserveringen: '1'

Expected outcome:
found 2 results
-
date: '2010-11-14'
tafels: '1'
reserveringen: '1'
-
date: '2010-11-16'
tafels: '1'
reserveringen: '0'

The query works fine without the left join:
$ symfony doctrine:dql "from Tafel t where t.restaurant_id=4 select date(t.tijd), count(t.id) tafels group by date(t.tijd)"
>> doctrine executing dql query
DQL: from Tafel t where t.restaurant_id=4 select date(t.tijd), count(t.id) tafels, group by date(t.tijd)
found 2 results
-
date: '2010-11-14'
tafels: '1'
-
date: '2010-11-16'
tafels: '1'



 Comments   
Comment by Bart van den Burg [ 14/Nov/10 ]

As you can see, by the way, it does actually say "found 2 results", but then returns only one.

Comment by Willem van Duijn [ 08/Feb/11 ]

There are multiple reports from people that are hurt by this bug:

http://www.devcomments.com/doctrine-execute-only-returns-one-row-to286270.htm
http://www.devcomments.com/Problem-with-Doctrine-and-Join-GroupBy-query-at87536.htm

Setting the Hydration-mode to HYDRATE_NONE yields multiple result rows (but is not useful).

Comment by Victor Ruiz [ 18/Feb/11 ]

Related in some way with multiple order by clauses. If I remove all of them but one it works, the problem appears when I put more than one order by criteria.

Comment by Mike Seth [ 19/May/11 ]

This is a hydration problem that occurs because the ID columns of the joined tables are not SELECT'ed explicitly. The offending code is a loop in the graph base hydrator, but I don't understand it well enough to fix it with any certainty that I don't break anything.





[DC-930] Complex query with DISTINCT and LIMIT on pgsql causes a SQLSTATE exception - problem in doctrine_subquery_alias Created: 16/Nov/10  Updated: 16/Nov/10

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

Type: Bug Priority: Major
Reporter: Jacek Dębowczyk Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 0
Labels: None
Environment:

pgsql



 Description   

There is a problem in the following code in Doctrine/Query.php (lines 1257-1279) inside the buildSqlQuery() method:

            $subquery = $this->getLimitSubquery();

            // what about composite keys?
            $idColumnName = $table->getColumnName($table->getIdentifier());

            switch (strtolower($this->_conn->getDriverName())) {
                case 'mysql':
[...]
                case 'pgsql':
                    $subqueryAlias = $this->_conn->quoteIdentifier('doctrine_subquery_alias');

                    // pgsql needs special nested LIMIT subquery
                    $subquery = 'SELECT ' . $subqueryAlias . '.' . $this->_conn->quoteIdentifier($idColumnName)
                            . ' FROM (' . $subquery . ') AS ' . $subqueryAlias;

                    break;
            }

The above code is executed when a query consist of DISTINCT and LIMIT clauses. The most common situation is using pager.
The problem is in the subquery variable. The $idColumnName variable often has value "id". In such a situation in case of $subquery consist of some JOINs and some tables have column named "id", we have:

SELECT doctrine_subquery_alias.id FROM ((SELECT DISTINCT d1.id, d2.id FROM ...)) AS doctrine_subquery_alias

It, of course, causes the "ambiguous column name" pgsql exception.






[DC-920] The ability to add sql in the query between the first word and body of the query (allowing "SELECT STRAIGHT_JOIN" etc) Created: 09/Nov/10  Updated: 09/Nov/10

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

Type: New Feature Priority: Major
Reporter: will ferrer Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 0
Labels: None
Environment:

XP XAMP



 Description   

I recently discovered that I could greatly optimize some of the queries that were being run through our system by adding a STRAIGHT_JOIN keyword to the front of the select

I added a feature to doctrine which allows me to inject sql into the query in the right place to enable features such as "STRAIGHT_JOIN" but I can't post the patch because my patches are starting to run together – the syntax with in the generated patch would also contain parts of other patches I have posted to jira but have not yet been included in the doctrine svn.

I still wanted to make this post because it will give me a ticket number to base my test cases around.

Will Ferrer



 Comments   
Comment by will ferrer [ 09/Nov/10 ]

In order to show what this patch fixes I am including my test case for the patch below:

<?php
/*
 *  $Id$
 *
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
 * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
 * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
 * A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
 * OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
 * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
 * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
 * DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
 * THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
 * OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 *
 * This software consists of voluntary contributions made by many individuals
 * and is licensed under the LGPL. For more information, see
 * <http://www.doctrine-project.org>.
 */

/**
 * Doctrine_Ticket_DC920_TestCase
 *
 * @package     Doctrine
 * @author      Will Ferrer
 * @license     http://www.opensource.org/licenses/lgpl-license.php LGPL
 * @category    Object Relational Mapping
 * @link        www.doctrine-project.org
 * @since       1.0
 * @version     $Revision$
 */
class Doctrine_Ticket_DC920_TestCase extends Doctrine_UnitTestCase 
{

    public function testBeforeBodySelect()
    {
        $q = new Doctrine_Query();
        
        $q->parseDqlQuery("SELECT DISTINCT STRAIGHT_JOIN u.name, p.id FROM User u LEFT JOIN u.Phonenumber p ON p.phonenumber = '123 123'");
		$this->assertEqual($q->getSqlQuery(), "SELECT DISTINCT STRAIGHT_JOIN e.id AS e__id, e.name AS e__name, p.id AS p__id FROM entity e LEFT JOIN phonenumber p ON (p.phonenumber = '123 123') WHERE (e.type = 0)");
        $this->assertEqual($q->getDql(), "SELECT DISTINCT STRAIGHT_JOIN u.name, p.id FROM User u LEFT JOIN u.Phonenumber p ON p.phonenumber = '123 123'");
    }

	public function testBeforeBodySelectNoneDQL() 
    {
        $q = new Doctrine_Query();
        $q->select("DISTINCT STRAIGHT_JOIN u.name, p.id");
		$q->from('User u');
		$q->leftJoin("u.Phonenumber p ON (p.phonenumber = '123 123')");
        $this->assertEqual($q->getSqlQuery(), "SELECT DISTINCT STRAIGHT_JOIN e.id AS e__id, e.name AS e__name, p.id AS p__id FROM entity e LEFT JOIN phonenumber p ON (p.phonenumber = '123 123') WHERE (e.type = 0)");
        $this->assertEqual($q->getDql(), "SELECT DISTINCT STRAIGHT_JOIN u.name, p.id FROM User u LEFT JOIN u.Phonenumber p ON (p.phonenumber = '123 123')");
	}
	
    public function testBeforeBodyDelete() 
    {
        $q = new Doctrine_Query();

        $q->parseDqlQuery('DELETE IGNORE FROM User');
        $this->assertEqual($q->getSqlQuery(), 'DELETE IGNORE FROM entity WHERE (type = 0)');
        $this->assertEqual($q->getDql(), "DELETE IGNORE FROM User");
    }
	
	public function testBeforeBodyDeleteNoneDQL() 
    {
        $q = new Doctrine_Query();
        $q->delete('IGNORE');
		$q->from('User');
        $this->assertEqual($q->getSqlQuery(), 'DELETE IGNORE FROM entity WHERE (type = 0)');
        $this->assertEqual($q->getDql(), "DELETE IGNORE FROM User");
    }
	
	public function testBeforeBodyUpdate() 
    {
        $q = new Doctrine_Query();

        $q->parseDqlQuery("UPDATE IGNORE User u SET u.name = 'someone'");
        $this->assertEqual($q->getSqlQuery(), "UPDATE IGNORE entity SET name = 'someone' WHERE (type = 0)");
        $this->assertEqual($q->getDql(), "UPDATE IGNORE User u SET u.name = 'someone'");
    }
	
	public function testBeforeBodyUpdateNonDql() 
    {
        $q = new Doctrine_Query();
        $q->update('IGNORE');
		$q->from('User u');
		$q->set('name', "'someone'");
        $this->assertEqual($q->getSqlQuery(), "UPDATE IGNORE entity SET name = 'someone' WHERE (type = 0)");
        $this->assertEqual($q->getDql(), "UPDATE IGNORE User u SET name = 'someone'");
    }

}





[DC-916] fetchOne defect Created: 05/Nov/10  Updated: 24/Jan/11

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

Type: Improvement Priority: Major
Reporter: Roman Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 1
Labels: None


 Description   

Query fetchOne method now retrieves and hydrates all collection, which can be time consumable. I suggest to add limit 1 in fetchOne method.



 Comments   
Comment by Gennady Feldman [ 21/Jan/11 ]

This is a defect. People assume there's an implied limit(1) in the query because of fetchOne(). Please fix this, this is pretty serious stuff.

Comment by Gennady Feldman [ 21/Jan/11 ]

Doctrine_Table actually "works around" the issue but explicitly doing limit(1) before doing fetchOne():

public function findOneBy($fieldName, $value, $hydrationMode = null)

{ return $this->createQuery('dctrn_find') ->where($this->buildFindByWhere($fieldName), (array) $value) ->limit(1) ->fetchOne(array(), $hydrationMode); }
Comment by Jonathan H. Wage [ 23/Jan/11 ]

Was this always like this or did it change recently?

Comment by Gennady Feldman [ 24/Jan/11 ]

Frankly I have no idea.

Also adding a limit(1) shouldn't break anything and is straight forward. We would also want to fix findOneBy not to do limit(1) since fetchOne() should take care of this after the fix is in place.





[DC-910] Sub queries do not work properly in the on clause of a join Created: 01/Nov/10  Updated: 02/Nov/10

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

Type: Bug Priority: Major
Reporter: will ferrer Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 0
Labels: None
Environment:

XP Xamp


Attachments: Text File DC_910_fix.patch    

 Description   

When subqueries are used in the on part of a join clause the Doctrine_Query_JoinCondition class does not always create the proper sql. For instance when there are 2 subqueries used in a between doctrine tries to parse the statement as 1 subquery rather 2 subqueries with an "and".

I will post my patch that fixes this issue after I make some test cases for it. I also fixed an issue where "(SQL:" syntax was breaking the join as well.

Will



 Comments   
Comment by will ferrer [ 02/Nov/10 ]

took out some commented code chunks





[DC-889] Using RANDOM() AS rand as last field WITHOUT a comma between them works, but not randomly Created: 14/Oct/10  Updated: 14/Oct/10

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

Type: Bug Priority: Major
Reporter: Dennis Gearon Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 0
Labels: None
Environment:

ubuntu 64 bit, using a task in symfony



 Description   

The difference between the two code samples below is that there is a comma after 'lo.postal'_code in the second example:

This code DOES NOT RANDOMIZE But also DOES NOT PRODUCE A PARSER ERROR
$q = Doctrine_Query::create()
->select("li.id,
ap.name,
act.title, act.title_short, act.family_friendly,
o.unix_ts_begin, o.unix_ts_end,
act.description,
act.cost_min, act.tags, act.cost_min, act.cost_notes, act.organization,
lo.thoroughfare, lo.address_extra, lo.locality, lo.administrative_area, lo.country_name_code, lo.postal_code
RANDOM() AS rand")
->from(blah, blah)
->where(primary key equality statements for joining)
->orderBy('rand');

This DOES
$q = Doctrine_Query::create()
->select("li.id,
ap.name,
act.title, act.title_short, act.family_friendly,
o.unix_ts_begin, o.unix_ts_end,
act.description,
act.cost_min, act.tags, act.cost_min, act.cost_notes, act.organization,
lo.thoroughfare, lo.address_extra, lo.locality, lo.administrative_area, lo.country_name_code, lo.postal_code,
RANDOM() AS rand")
->from(blah, blah)
->where(primary key equality statements for joining)
->orderBy('rand');






[DC-876] Basic Request return one element. Created: 30/Sep/10  Updated: 05/Oct/10

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

Type: Bug Priority: Major
Reporter: rudybruneau Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 1
Labels: None
Environment:

Windows XP Pro. Service Pack 3, Eclipe PDT, Doctrine 1.2.3, Php 5.2.11


Attachments: JPEG File image.jpg    

 Description   

i try to get all preferences with an IdUser specified.

schema.yml
UserHasPreference:
  connection: hopscore
  actAs:
    I18n:
      fields: [value]
    Timestampable:
      created:
        name: created_at
        type: timestamp
        format: Y-m-d H:i:s
  columns:
    idUserHasPreference:
      name: idUserHasPreference as id
      type: integer(4)
      unsigned: true
      primary: true
      autoincrement: true
    UsersIdUser:
      name: UsersIdUser as idUser
      type: integer(4)
      unsigned: true
      notnull: true
    PreferencesIdPreference:
      name: PreferencesIdPreference as idPreference
      type: integer(4)
      unsigned: true
      notnull: true
    MatchLinksMatchLinkId:
      name: MatchLinksMatchLinkId as MatchLinkId
      type: integer(4)
      unsigned: true
      notnull: true
    value:
      type: string(100)
  relations:
    Users:
      class: User
      local: idUser
      foreign: id
      type: one
    Preferences:
      class: Preference
      local: idPreference
      foreign: id
      type: one
    MatchLinks:
      class: MatchLink
      local: MatchLinkId
      foreign: id
      type: one
  options:
    collate: utf8_unicode_ci
    charset: utf8
Doctrine Request
$preferences = Doctrine_Query::create()
		->select('uhp.idPreference as idPref')
		->addSelect('uhpt.value as value')
		->addSelect('uhp.MatchLinkId as idItem')
		->from('UserHasPreference uhp')
		->innerJoin('uhp.Translation uhpt')
		->where('uhp.idUser = ?', intval($idUser))
		->execute(array(), Doctrine::HYDRATE_ARRAY);
	    return $preferences;

With $preferences->getSqlQuery(); and getParams(); and report this SQL query in phpmydadmin.
With SQL i have 13 elements. But in my object $preference i have one element (Doctrine::HYDRATE_ARRAY)
With "Doctrine::HYDRATE_NONE", i have 13 elements as SQL query.



 Comments   
Comment by Eirik Hoem [ 05/Oct/10 ]

I have the same problem, and I think this is related to the select statement. It seems that a select statement where all fields are aliased will cause this behavior. A simple work-around is to select one field without aliasing it.

Code for reproducing / work-around:

$query = Doctrine_Query::create();
$query->from('Results r');
$query->select('p.id as myid, r.value as foo');
$query->innerJoin('r.Profil p on (r.pid=123)');
$results = $query->execute(array(), Doctrine_Core::HYDRATE_ARRAY);

count($results) = 1

$query = Doctrine_Query::create();
$query->from('Results r');
$query->select('p.id as myid, r.value');
$query->innerJoin('r.Profil p on (r.pid=123)');
$results = $query->execute(array(), Doctrine_Core::HYDRATE_ARRAY);

count($results) = 250





[DC-874] Allow parameters to be passed to Doctrine_Query::select() Created: 30/Sep/10  Updated: 23/Dec/10

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

Type: Improvement Priority: Major
Reporter: Gerry Vandermaesen Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 1
Labels: None
Environment:

Any



 Description   

While I believe it's not so extraordinary to have parameters in a SELECT clause, Doctrine_Query::select() does not allow to pass parameters, next to the SELECT clause. You can still pass any parameters to execute(), but I do believe it would be nice to be able to pass the parameter values right away to select() as you can with where() etc.

Example:

Doctrine_Query::create()>select('f.*, (f.id = ?) AS selected'))>from('Foo f')->execute(array($selected_id));

This principle would apply to any select-field that has a calculated value that comes from a parameter.



 Comments   
Comment by Piotr Leszczyński [ 23/Dec/10 ]

I believe this should be major improvement for Doctrine. Without this feature, some queries can't be created.





[DC-877] Hydrator fatal error: Found non-unique key mapping named 'lang' Created: 30/Sep/10  Updated: 22/Oct/10

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

Type: Bug Priority: Major
Reporter: Ilya Sabelnikov Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 0
Labels: None
Environment:

PHP 5.2.13


Attachments: File Ticket_DC877_TestCase.php    

 Description   

You could find the ticket's test case in the attachments.






[DC-873] Update Execute Params do not persist Created: 26/Sep/10  Updated: 26/Sep/10

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

Type: Bug Priority: Major
Reporter: Kyle Clarke Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 0
Labels: None
Environment:

LAMP php5.2.6 Symfony 1.4



 Description   

I have been trying to update numerous records with the one doctrine_query object (which could be my problem) and passing the query execute params to persist to the dbase. eg

$q = Doctrine_Query::create()
->update('TdPackageType pt')
->set('pt.name', '?')
->set('pt.group_type_id', '?')
->where('pt.id = ?')
;

Then iterating over an array of values, passing the required values to the execute method eg
foreach($foobars as $foobar) {
$q->execute(array($foobar[0], $foobar[1], $foobar[2]));
}

I thought this the best way by creating only the one query instance and then assigning the vars as required. Trouble being, the data did not persist? I had no errors returned from Doctrine - eg I had the correct number of matched params - but the update would not update. To move on I ended up instantiating a new query object each time I iterated over my array of data values. eg

foreach($foobars as $foobar) {

$q = Doctrine_Query::create()
->update('TdPackageType pt')
->set('pt.name', '?', $foo[0])
->set('pt.group_type_id', '?', $foo[1])
->where('pt.id = ?', $foo[2])
;

$q->execute();
}

The values did then persist correctly to the dbase? Am I missing something really fundamental here? eg I would have thought the first code struct was a much better design to re-use the one query object. Or is it as silly as me not adding a hydration method to the execute method?

Any all help appreciated.
Thanks
Kyle






[DC-828] MSSQL - ORDER BY on text columns throws mssql error 306 [+patch] Created: 16/Aug/10  Updated: 17/Apr/14

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

Type: Improvement Priority: Major
Reporter: Enrico Stahn Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 0
Labels: None


 Description   
  • Table: foo (id:integer, title:text)
  • Created Query: SELECT [id], [title] FROM [foo] ORDER BY [title]

Throws:

Server: Msg 306, Level 16, State 2, Line 1
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

Solution:

  • Created Query: SELECT [id], [title] FROM [foo] ORDER BY CAST([title] AS VARCHAR(8000))

References:

Patch will be supplied soon ...



 Comments   
Comment by Enrico Stahn [ 20/Aug/10 ]

http://github.com/estahn/doctrine1/compare/DC-828
http://github.com/estahn/doctrine1/tree/DC-828

I guess we need more TestCases for the SubQuery stuff.

Comment by Enrico Stahn [ 02/Sep/10 ]

I made a mistake with github, the updated branch can be found at
http://github.com/estahn/doctrine1/tree/DC-828-2





[DC-809] Doctrine_Query_Abstract::set() Does not handle boolean values correctly. Created: 05/Aug/10  Updated: 05/Aug/10

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

Type: Bug Priority: Major
Reporter: Alexandre Ravey Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 0
Labels: None

Attachments: Text File Abstract.patch    

 Description   

When passing booleans to the set() function on an update DQL query object, the value false is not handled correctly and end up in a corrupted query.

Test schema:

schema.yml
Test:
  columns:
    bool:
      type: boolean
      default: false
      notnull: true

Steps to reproduce:

code.php
Doctrine_Query::create()->update('Test')
                        ->set('bool', false)
                        ->execute()
                        ->getSqlQuery();

Result Query:
'UPDATE test SET bool = '

Expected Query:
'UPDATE test SET bool = false'

See attached patch file for a possible solution. (I don't have deep know of Doctrine internals...)






[DC-799] Doctrine_Query::parseFunctionExpression() produces unexpected results if the expression contains a function Created: 27/Jul/10  Updated: 27/Jul/10

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

Type: Bug Priority: Major
Reporter: Henning Glatter-Gotz Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Mac OS X, LINUX



 Description   

When Doctrine_Query::parseFunctionExpression() is called with the following parameter and $this->_conn->expression is of type Doctrine_Expression_MySql:

$expr = "DATE_FORMAT(datefield,'%Y-%m-%d'),someotherfield"

The expected result from Doctrine_Query::parseFunctionExpressoin() would be

DATE_FORMAT(l.datefield,'%Y-%m-%d'),l.someotherfield

However, the actual result is

DATE_FORMAT(l.datefield,'%Y-%m-%d'),someotherfiel

Note the missing leading "l." and the last character ("d") on "someotherfield".
Obviously this leads to fatal errors.

This example is a result of calling addGroupby("DATE_FORMAT(datefield,'%Y-%m-%d'),someotherfield") on a Doctrine_Query object.






[DC-787] Join & On override Created: 15/Jul/10  Updated: 15/Jul/10

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

Type: Improvement Priority: Major
Reporter: Thomas Tourlourat - Armetiz Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 0
Labels: None


 Description   

Users & Books are linked by CompoUsersBooks. So, It's a m:m relationship.

Here, a crazy DQL query to fetch users & specifics books.

[code]
$query = Doctrine_Query::create ()
->select ("users., books.")
->from ("Users users")
->leftJoin ("users.alternateBooks alternateBooks")
->leftJoin ("users.books books ON books.id_book = alternateBooks.id_book");
[/code]

The SQL query
[code]
SELECT `s`.`id_show` AS `s__id_show`
FROM `users` `u`
LEFT JOIN `compo_users_books` `c1`
ON ( `u`.`id_user` = `c2`.`id_user` )
LEFT JOIN `books` `b1`
ON `c1`.`id_book` = `b1`.`id_book`
LEFT JOIN `compo_users_books` `c2`
ON `u`.`id_user` = `c2`.`id_user`
LEFT JOIN `books` `b2`
ON `b1`.`id_book` = `b2`.`id_book`
[/code]

As you can see,
The SQL query is correctly build.
But, this SQL Query isn't optimized at all.
Because the following part of code isn't use :
[code]
LEFT JOIN `compo_users_books` `c2`
ON `u`.`id_user` = `c2`.`id_user`
[/code]

And also, the left join is a cartesian product ! The number of row return is really big for nothing...

That really sux because It's performance killer.






[DC-792] Doctrine query with the Oracle SOUNDEX function Created: 16/Jul/10  Updated: 16/Jul/10

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

Type: Bug Priority: Major
Reporter: Ahmed GHALI Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Ubuntu, oracle 11g, symfony 1.4.3



 Description   

Hi all,
We create a doctrine query with the Oracle SOUNDEX function.
The Dql generated work fine when we run it on Oracle
on the development envirenment an error is thrown (Unknown function soundex)
this is the Dql generated:
SELECT * FROM ACTOR A WHERE A.bocompanyid = 32 AND soundex(actor_fname || ' ' || actor_name) = soundex('EEEE')

related to :
$q = $this->createQuery('A')->select('*')
->where('A.bocompanyid = ?', $companyId)
->andWhere("soundex(actor_fname || ' ' || actor_name) = soundex ('" .$actorName."')");






[DC-766] preQuery() and getConnection() - multiple connections Created: 25/Jun/10  Updated: 25/Jun/10

Status: Open
Project: Doctrine 1
Component/s: Query
Affects Version/s: 1.2.0, 1.2.1, 1.2.2
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Maciej Mazur Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 0
Labels: None


 Description   

When preQuery() hook is invoked, any calls to $this->getConnection() in preQuery() body is useless because it always returns the current connection instead of the connection bound with component that is being queried.

I am using this hook to switch the connection to connection to master host for schema of the queried component , but cannot do this because the is no way to obtain information about which component this query uses and what would be bounded connection.
The connection is propely set by Doctrine but after the preQuery() hook is invoked, but when this finally happens i have no control over the execution of the query.






[DC-759] MSSQL Server / ODBC: Cannot order by a column aggregate when a limit subquery is required. Created: 22/Jun/10  Updated: 22/Jun/10

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

Type: Bug Priority: Major
Reporter: Craig Marvelley Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Windows XP
Apache 2.2
PHP 5.3.2


Attachments: File DC759TestCase.php     Text File doctrine_aggregate_function_order_patch.patch    

 Description   

It's not possible to use a column aggregate function and a limit subquery together with SQL Server; the logic that constructs the limit subquery is flawed, not recognising the presence of a function and assuming it to be a column. The resulting error occurs:

SQLSTATE[42S22]: Column not found: 207 [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid column name ...

This can be observed when running the random sorting example from the manual against SQL Server through ODBC:
http://www.doctrine-project.org/documentation/manual/1_2/hu/dql-doctrine-query-language:order-by-clause:using-random-order

In addition to the syntax error, it seems SQL Server requires that all aggregate functions that appear in the order by section of the query are declared (i.e. aliased) in the select section which is not currently happening.

I also noticed what I think is a bug in the ordering of results within the sub-select on line 214 of Doctrine_Connection_Mssql:
$query .= (stripos($sorts[$i], 'asc') !== false) ? 'DESC' : 'ASC';

which appears to be sorting in the opposite order as to what was intended.

I've a fix and a test case which attempts to address these issues. I noticed two other open issues surrounding this section of code (#586 and #744) - if this patch is accepted I think these issues will also be resolved.



 Comments   
Comment by Craig Marvelley [ 22/Jun/10 ]

Uploaded patch and test case.





[DC-760] MSSQL Server: Support for RAND() in Doctrine_Expression_MSSQL Created: 24/Jun/10  Updated: 24/Jun/10

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

Type: Improvement Priority: Major
Reporter: Craig Marvelley Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Windows XP
PHP 5.3.2


Attachments: Text File doctrine_expression_mssql_rand.patch    

 Description   

Doctrine_Expression_MSSQL doesn't provide a method for handling SQL Server's RAND() method. I've attached a patch to support it.






[DC-757] In SQL query, field name is not replaced with real column name in right part of join condition Created: 21/Jun/10  Updated: 21/Jun/10

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

Type: Bug Priority: Major
Reporter: Guilliam X Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Doctrine SVN 1.2 r7676


Attachments: Text File DC757-patch_1.txt    

 Description   

Hi,
I'm blocked on a bad issue

I have a schema that uses fields aliases:

schema.yml
User:

Phonenumber:
  columns:
    theuser:
      name: colprefix_user_id as user_id   #note the column alias
      type: integer
    thenumber:
      name: colprefix_number as number
      type: string(20)
  relations:
    User:
      local: user_id
      foreign: id
      foreignAlias: Phonenumbers

Now if I make the following 2 DQL queries, the first "plain" and the second with redefining the ON clause, then print generated SQL:

$q1 = Doctrine_Query::create()
    ->from('User u')
    ->innerJoin('u.Phonenumbers p');

$q2 = Doctrine_Query::create()
    ->from('User u')
    ->innerJoin('u.Phonenumbers p ON u.id = p.user_id');

echo $q1->getSqlQuery() . "\n";
echo $q2->getSqlQuery() . "\n";

I get (lines formatted a little):

SELECT u.id AS u__id, p.id AS p__id, p.colprefix_user_id AS p__colprefix_user_id, p.colprefix_number AS p__colprefix_number
        FROM user u INNER JOIN phonenumber p ON u.id = p.colprefix_user_id

SELECT u.id AS u__id, p.id AS p__id, p.colprefix_user_id AS p__colprefix_user_id, p.colprefix_number AS p__colprefix_number
        FROM user u INNER JOIN phonenumber p ON (u.id = p.user_id)

Note how, on the right end, p.user_id is not replaced with p.colprefix_user_id in the 2nd query!
If I try to execute $q2 I get

Fatal error: Uncaught exception 'Doctrine_Connection_Mysql_Exception' with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 'p.user_id' in 'on clause'' in /[...]/doctrine_test/lib/vendor/doctrine/Doctrine/Connection.php:1082

This only happens for the right member of the join condition. If i swap the fields like this:

$q3 = Doctrine_Query::create()
    ->from('User u')
    ->innerJoin('u.Phonenumbers p ON p.user_id = u.id'); //swapped

echo $q3->getSqlQuery() . "\n";

then we can see that the left member is replaced well:

SELECT u.id AS u__id, p.id AS p__id, p.colprefix_user_id AS p__colprefix_user_id, p.colprefix_number AS p__colprefix_number
        FROM user u INNER JOIN phonenumber p ON (p.colprefix_user_id = u.id)

(This is only an example, there's no point redefining the ON clause here, but the same is occurring in the project I work on, in a WITH clause).

I think the problem is in Doctrine_Query_JoinCondition::load(), in which

  • the left member of condition is directly parsed in a call to Doctrine_Query::parseClause();
  • whereas the right member is checked if it's a subquery, and if not, is transformed into the result of Doctrine_Expression::getSql() then passed to Doctrine_Query_JoinCondition::parseLiteralValue() or Doctrine_Query::parseClause() if it is an SQL function...

This can also produce weird results with SQL functions:

$q4 = Doctrine_Query::create()
    ->from('User u')
    ->innerJoin('u.Phonenumbers p WITH SUBSTRING(p.number,0,3) = \'555\''); //left

$q5 = Doctrine_Query::create()
    ->from('User u')
    ->innerJoin('u.Phonenumbers p WITH \'555\' = SUBSTRING(p.number,0,3)'); //right

echo $q4->getSqlQuery() . "\n";
echo $q5->getSqlQuery() . "\n";

Output:

SELECT u.id AS u__id, p.id AS p__id, p.colprefix_user_id AS p__colprefix_user_id, p.colprefix_number AS p__colprefix_number
        FROM user u INNER JOIN phonenumber p ON u.id = p.colprefix_user_id AND (SUBSTRING(p.colprefix_number FROM 0 FOR 3) = '555')

SELECT u.id AS u__id, p.id AS p__id, p.colprefix_user_id AS p__colprefix_user_id, p.colprefix_number AS p__colprefix_number
        FROM user u INNER JOIN phonenumber p ON u.id = p.colprefix_user_id AND ('555' = SUBSTRING(p.colprefix_number FROM 0 FOR 3 FROM ))

Now, the correct column name is always used, but in the 2nd case the "SUBSTRING" function looks like it has been parsed twice :/

I think both members (left and right) of condition should be treated equally (but I have no fix for now... :s)

Thank you



 Comments   
Comment by Guilliam X [ 21/Jun/10 ]

Waiting for better, I attach the patch I used;
but it's not a good one, as it only "solves" the field name problem but not the SUBSTRING one, and is more a patch than a fix... (sorry I don't feel to refactor Doctrine_Query_JoinCondition::load() :s)





[DC-727] ReOpen DC-46 - Unexpected behavior with whereIn() and empty array Created: 11/Jun/10  Updated: 12/Oct/11

Status: Open
Project: Doctrine 1
Component/s: Query
Affects Version/s: 1.2.1, 1.2.2, 1.2.3
Fix Version/s: None

Type: Bug Priority: Major
Reporter: David Jeanmonod Assignee: Jonathan H. Wage
Resolution: Unresolved Votes: 3
Labels: None
Environment:

PHP 5.3.1 (cli) (built: Feb 11 2010 02:32:22)
mysql Ver 14.14 Distrib 5.1.41, for apple-darwin9.5.0 (i386) using readline 5.1
Doctrine version 1.2.2 from SVN: http://doctrine.mirror.svn.symfony-project.com/tags/1.2.2/lib/Doctrine.php


Attachments: Text File DC-727_refactored.patch     Text File DC-727_with_duplicates.patch     File DC727TestCase.php     File DC727TestCase_more_specific.php    

 Description   

I reopen the DC-46 as it'seems not fix at all.
When I do a whereIn with empty array, the condition is just drop and I get no Exception.

Here is a simple test case:

require_once('doctrine/lib/Doctrine.php');
spl_autoload_register(array('Doctrine', 'autoload'));
$manager = Doctrine_Manager::getInstance();
$manager->setAttribute(Doctrine::ATTR_EXPORT, Doctrine::EXPORT_ALL);
$conn = Doctrine_Manager::connection('mysql://root:root@localhost/test_doctrine');
echo "Connection is set up\n";

class Record extends Doctrine_Record {
    public function setTableDefinition(){
        $this->setTableName('record');
    }
}

// Create the db
try {Doctrine::dropDatabases();}catch(Exception $e){} // Drop if exist :-)
Doctrine::createDatabases();Doctrine::createTablesFromArray(array('Record'));

// Test
echo Doctrine::getTable('Record')->createQuery()->select('id')->whereIn('id', array())->getSqlQuery() , "\n";
echo Doctrine::getTable('Record')->createQuery()->select('id')->whereIn('id', array())->fetchArray() , "\n";

// Result is:
// SELECT r.id AS r__id FROM record r
// Array


 Comments   
Comment by Guilliam X [ 16/Jun/10 ]

The problem is that the change for "new" behavior (throw exception instead of return unchanged query) was only done in _processWhereIn() but not cascaded to andWhereIn() and orWhereIn() (another example we should avoid code duplication).
The patch is simple, but it causes Doctrine_Ticket_1558_TestCase to fail. Indeed that (old) test expects the "old" behavior (return unchanged query, don't throw exception)... So the 2 fixes are incompatible, you'll have to choose :/

I still attach a new test case and 2 versions of a patch (the first one just applies changes of _processWhereIn also to the 2 other functions but adds more duplicate code, the second is a little refactored and seems better to me).

Regards

Comment by Guilliam X [ 16/Jun/10 ]

added a more specific test case (expects Doctrine_Query_Exception instead of simple Exception)

Comment by Jan Schütze [ 22/Nov/10 ]

Hi,

the issue is still present in 1.2.3. Are there any plans to apply it?

Kind regards,

Comment by Jim Persson [ 12/Oct/11 ]

I would like to add that this also applies to delete-queries which can cause serious data loss.
We had a case where a table of serialized data was completely emptied which caused a database cascade that deleted quite a lot of data.





[DC-692] Can not create a subquery in where Created: 18/May/10  Updated: 19/May/10

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

Type: Bug Priority: Major
Reporter: will ferrer Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 0
Labels: None
Environment:

XP Xamp



 Description   

Hi All

I can not figure out how to make a subquery work in doctrine. I get the subquery to look fine in DQL but when doctrine converts the DQL to SQL the subquery is automatically removed.

Here is an example:
This PHP:

$q = Doctrine_Query::create(); 
$q->from('Customer Customer'); 
$q->addWhere(' Customer.id in (SELECT Customer.id as customer_id FROM Customer Customer)'); 
$q->addSelect('Customer.id'); 
$q->addSelect('Customer.id as customer_id');
$q->limit(20); 

Creates this DQL:

SELECT Customer.id, Customer.id as customer_id FROM Customer Customer WHERE Customer.id in (SELECT Customer.id as customer_id FROM Customer Customer) LIMIT 20

Which creates this broken SQL:

SELECT p.id AS p__id, p.id AS p__0 FROM product_customers p WHERE (p.id in ()) LIMIT 20

Notice that the subquery has been replaced with just "()".

Is there something wrong with my execution, does doctrine just not support subqueries, or is there a bug here?

Thanks much in advance.

Sincerely

Will Ferrer



 Comments   
Comment by will ferrer [ 19/May/10 ]

I have found a way around my problem (looked through the code to figure out how it worked) – seems that if I put 'SQL:' in front of my sub query, and use SQL instead of DQL in the sub query like so:

$q = Doctrine_Query::create(); 
$q->from('Customer Customer'); 
$q->addWhere(' Customer.id in (SQL:SELECT p.id AS p__0 FROM product_customers p)'); 
$q->addSelect('Customer.id'); 
$q->addSelect('Customer.id as customer_id'); 

The correct sql is made:

SELECT p.id AS p__id, p.id AS p__0 FROM product_customers p WHERE (p.id in (SELECT p.id AS p__0 FROM product_customers p)) LIMIT 20

I am not still not sure what is up with trying to use the DQL instead. Looking through the code I see that on line 842 of Doctrine_Query my DQL subquery was being passed to the function $this->createSubquery()->parseDqlQuery($trimmed) like so:

 $q = $this->createSubquery()->parseDqlQuery($trimmed);
$trimmed = $q->getSqlQuery();

$trimmed was coming back as false here which is why my subquery wasn't working.

Hope that is helpful.

Best Regards

Will Ferrer





[DC-680] HYDRATE_ARRAY causes timeout with no fields selected Created: 13/May/10  Updated: 13/May/10

Status: Open
Project: Doctrine 1
Component/s: Query
Affects Version/s: 1.2.1, 1.2.2
Fix Version/s: None

Type: Bug Priority: Major
Reporter: David Ash Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Windows 7, Ubuntu 9.10 (Linux), PHP 5.2.10, Zend Framework 1.10



 Description   

Calling HYDRATE_ARRAY on a query with

a) No values selected
b) select('*')
c) select('Table.*')

causes the entire query object to be loaded, rather than just returning an array of all values. If a limit(1) is used, the correct result is returned. But as soon as it becomes a collection, the entire query object is returned.

Code to produce problem..

$query = Doctrine_Query::create()
        ->from('Order ord')
        ->limit(2)
        ->execute(array(), Doctrine_Core::HYDRATE_ARRAY);
        
        print_r($query);





[DC-651] [PATCH] Doctrine_Record::option('orderBy', ...) of join's right side being applied to refTable in m2m relationship Created: 26/Apr/10  Updated: 17/Apr/14

Status: Open
Project: Doctrine 1
Component/s: Query, Relations
Affects Version/s: 1.2.2, 1.2.3
Fix Version/s: 1.2.2, 1.2.3

Type: Bug Priority: Major
Reporter: suhock Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 2
Labels: None
Environment:

CentOS 5.4
PHP 5.3.2
MySQL 5.1.44, for unknown-linux-gnu (x86_64)


Attachments: File DC651TestCase.php     File Query_orderBy_relation.diff     Text File Ticket_DC651.patch    

 Description   

When using the Doctrine_Record::option('orderBy', ...) feature on a table definition, where that table is the target of a many-to-many join, the specified orderBy columns are applied to the relation table's alias. So for example, given the following definitions:

class User extends Doctrine_Record {
  public function setTableDefinition() {
    $this->hasColumn('uid', 'integer', null, array('primary' => true));
    $this->option('orderBy', 'uid');
  }

  public function setUp() {
    $this->hasMany('Group as groups', array('refClass' => 'UserGroup', 'local' => 'user_uid', 'foreign' => 'group_id'));
  }
}

class Group extends Doctrine_Record {
  public function setTableDefinition() {
    $this->hasColumn('gid', 'integer', null, array('primary' => true));
  }

  public function setUp() {
    $this->hasMany('User as users', array('refClass' => 'UserGroup', 'local' => 'group_gid', 'foreign' => 'user_id'));
  }
}

class UserGroup extends Doctrine_Record {
  public function setTableDefinition() {
    $this->hasColumn('user_uid', 'integer', null, array('primary' => true));
    $this->hasColumn('group_gid', 'integer', null, array('primary' => true));
  }

  public function setUp() {
    $this->hasOne('User as user', array('local' => 'user_uid', 'foreign' => 'uid'));
    $this->hasOne('Group as group', array('local' => 'group_gid', 'foreign' => 'gid'));
  }
}

the following queries:

$query = Doctrine_Query::create()
  ->select('u.*')
  ->from('User u')
  ->leftJoin('u.groups g WITH g.gid=?', 1);
echo $query->getSqlQuery() . "\n";

$query = Doctrine_Query::create()
  ->select('g.*')
  ->from('Group g')
  ->leftJoin('g.users u WITH u.uid=?', 1);
echo $query->getSqlQuery() . "\n";

will output the following:

SELECT u.uid AS u__uid FROM user u LEFT JOIN user_group u2 ON (u.uid = u2.user_uid) LEFT JOIN group g ON g.gid = u2.group_id AND (g.gid = ?) ORDER BY u.uid
SELECT g.gid AS g__gid FROM group g LEFT JOIN user_group u2 ON (g.gid = u2.group_gid) LEFT JOIN user u ON u.uid = u2.user_id AND (u.uid = ?) ORDER BY u.uid, u2.uid

The orderBy option() call is applied to the User definition. The SQL for the first query is correct (where User is on the left side of the join). The SQL for the second query (where User is on the right-most side of the join), however, is obviously incorrect (UserGroup doesn't even have a uid column). Basically, User's orderBy option is being applied to both the User table and its respective reference table, UserGroup, when it is the target of a join.

After digging through the source for a while, I believe I've come up with a patch for this issue (which should be checked by someone more knowledgeable of Doctrine's internals). Basically, in the Doctrine_Query::buildSqlQuery() function, a call is made to Doctrine_Relation::getOrderByStatement() with the reference table (UserGroup)'s alias (u2), which in turn makes a call to Doctrine_Table::getOrderByStatement() on the referenced table (User), filling in the ORDER BY clause with User columns using UserGroup's alias. My solution was to reorder the logic so that the test for a reference class is made before the initial call to getOrderByStatement() is made. It seems to work against my test case and the test cases in the repository. I'll post my patch momentarily.

This bug was first mentioned in the comments in DC-313, but the original ticket comes across as more of a feature request for the hasMany() orderBy feature.



 Comments   
Comment by suhock [ 26/Apr/10 ]

attached a test case for this bug

Comment by suhock [ 26/Apr/10 ]

patch against /branches/1.2 HEAD (should also work apply to 1.2.2 tag)

Comment by Dan Ordille [ 30/Aug/10 ]

I can confirm this as an issue. However I don't think the above patch adequately fixes the problem it seems like with it an order by is still added for the ref column however the relation alias is lost.

My query with the patch became
SELECT g.gid AS g__gid FROM group g LEFT JOIN user_group u2 ON (g.gid = u2.group_gid) LEFT JOIN user u ON u.uid = u2.user_id AND (u.uid = ?) ORDER BY u.uid, uid

I made an another patch that prevents this extra order by clause from being added and have attached it.

Comment by suhock [ 21/Sep/10 ]

I tried out the new patch (Query_orderby_relation.diff), but it provides a reversed diff (patching goes from a patched version to the original). After applying it manually, it fails the provided test case and several additional test cases from the repository.

The original patch DOES pass the provided test case, when applied against 1.2.2, 1.2.3, or the 1.2 branch from the repository. It does not pass, however, Doctrine_Query_Orderby_TestCase. As the previous poster mentioned, it fails to resolve aliases in instances where the 'orderBy' option is specified in a relation definition.

I deleted the original patch and am providing a revised patch (Ticket_DC651.patch) against branch 1.2 HEAD (also works with 1.2.3), which fixes this issue. It passes all working test cases, including Doctrine_Query_Orderby_TestCase and DC651TestCase.

Comment by José De Araujo [ 31/Aug/11 ]

I had this issue recently on a application I'm working on as described the oderBy option was applied on the joined table on a column that even doesn't exist in it. I used the DC651 patch provided and it solved the issue, so far I haven't seen any side effect to it.





[DC-637] Many-to-many: Using Association Class (refclass) property in query adds leftJoin Created: 20/Apr/10  Updated: 20/Apr/10

Status: Open
Project: Doctrine 1
Component/s: Query, Relations
Affects Version/s: 1.2.2
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Pablo Grass Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 0
Labels: None
Environment:

PHP 5.3.2 (cli) (built: Mar 4 2010 22:12:20)
mysql 5.1.45
centOS


Attachments: File example.php    

 Description   

When trying to use a refclass property - i.e. normalization table column - in a query, an unnecessary join is added to the query created, which is then used to "access" the property.

This issue has already been discussed back in May 2008 but still seems to persist.
Please see the google group discussion http://groups.google.com/group/doctrine-user/browse_thread/thread/c655832a450ccd1a/9cb6eca6ae1c3dee for example + comments.

Unfortunately it is hardly possible to write a test case for this, as working SQL is generated, but the query is just not right (test case basically would have to assert the query string, wich does not make much sense).
Another way to test if it does "what it should", could be to assert contents of $q->getTableAliasMap() - but does not seem ideal either.

John Wage commented on this issue
"Can you create a ticket for this with a failing test case? I believe this
should be possible and we should investigate a fix for it.
Thanks, Jon "
http://groups.google.com/group/doctrine-user/browse_thread/thread/c655832a450ccd1a/9cb6eca6ae1c3dee#msg_c73934a38515cadb






[DC-625] Doctrine_Query throws exception for custom hydrator that extends stock hydrator Created: 09/Apr/10  Updated: 20/Apr/10

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

Type: Bug Priority: Major
Reporter: Jared Mellentine Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Windows Vista, Apache 2.2, PHP 5.3, MySQL 5.1


Attachments: File Query.diff    

 Description   

I have a custom hydrator that extends Doctrine_Hydrator_ScalarDriver, for the sole purpose of simplifying the column keys (by passing false as the 3rd parameter on _gatherRowData):

class Doctrine_Hydrator_AssocDriver extends Doctrine_Hydrator_ScalarDriver
{
public function hydrateResultSet($stmt)
{
$cache = array();
$result = array();

while ($data = $stmt->fetch(Doctrine_Core::FETCH_ASSOC))

{ $result[] = $this->_gatherRowData($data, $cache, false); }

return $result;
}
}

The processPendingFields method in Doctrine_Query is unnecessarily throwing a Doctrine_Query_Exception at line 465. This can be resolved by skipping hydration methods that extend None, Scalar and SingleScalar.

A diff of the changes is attached.






[DC-606] [Oracle] Query on M:M with relation name doesn't work Created: 29/Mar/10  Updated: 29/Mar/10

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

Type: Bug Priority: Major
Reporter: Bertrand Zuchuat Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 0
Labels: None


 Description   

My schema:

---
Actuality:
  connection:         web
  tableName:          actuality
  actAs:
    softDelete:       ~
    Timestampable:    ~
    Sluggable:
      fields:         [name]
      builder:        [tool, slugify]
      indexName:      actuality_sluggable
      canUpdate:      true
  columns:
    id:
      type:           integer(4)
      primary:        true
      unsigned:       true
      sequence:       actuality
    created_at:
      type:           timestamp
    updated_at:
      type:           timestamp
    published_at:
      type:           timestamp
      notnull:        true
    unpublished_at:
      type:           timestamp
    name:
      type:           string(255)
      notnull:        true
    description_short:
      type:           string(1000)
      notnull:        true
    description:
      type:           clob
      notnull:        true
    is_professional:
      type:           boolean
      default:        true
  relations:
    Categories:
      class: ActualityCategory
      local: actuality_id
      foreign: actuality_category_id
      refClass: ActualityActualityCategory
      foreignAlias: Actualities

And my query:

public function retrieveCloud($is_professional, $limit = 20)
  {
    return $this->createQuery('ac')
    ->leftJoin('ac.Actualities a2')
    ->where('ac.id IN (SELECT aac.actuality_category_id FROM Actuality a LEFTJOIN a.ActualityActualityCategory aac WHERE a.published_at < ? AND a.is_professional = ?)', array(date('Y-m-d H:i:s'), $is_professional))
    ->orderBy('ac.name')
    ->limit($limit)
    ->execute();
  }

I have this error: Doctrine_Connection_Oracle_Exception

ORA-01008: not all variables bound : SELECT a.id AS a__id, a.created_at AS a__created_at, a.updated_at AS a__updated_at, a.name AS a__name, a.deleted_at AS a__deleted_at, a.slug AS a__slug, a2.id AS a2__id, a2.created_at AS a2__created_at, a2.updated_at AS a2__updated_at, a2.published_at AS a2__published_at, a2.unpublished_at AS a2__unpublished_at, a2.name AS a2__name, a2.description_short AS a2__description_short, a2.description AS a2__description, a2.is_professional AS a2__is_professional, a2.deleted_at AS a2__deleted_at, a2.slug AS a2__slug FROM actuality_category a LEFT JOIN actuality_actuality_category a3 ON (a.id = a3.actuality_category_id) LEFT JOIN actuality a2 ON a2.id = a3.actuality_id AND (a2.deleted_at IS NULL) WHERE a.id IN (SELECT a4.id FROM ( SELECT DISTINCT a4.id FROM actuality_category a4 LEFT JOIN actuality_actuality_category a6 ON (a4.id = a6.actuality_category_id) LEFT JOIN actuality a5 ON a5.id = a6.actuality_id AND (a5.deleted_at IS NULL) WHERE a4.id IN (SELECT a8.actuality_category_id AS a5__actuality_category_id FROM actuality a7 LEFT JOIN actuality_actuality_category a8 ON a7.id = a8.actuality_id WHERE (a7.published_at < :oci_b_var_1 AND a7.is_professional = :oci_b_var_2)) AND (a4.deleted_at IS NULL) ORDER BY a4.name ) a4 WHERE ROWNUM <= 20) AND (a.id IN (SELECT a5.actuality_category_id AS a5__actuality_category_id FROM actuality a4 LEFT JOIN actuality_actuality_category a5 ON a4.id = a5.actuality_id WHERE (a4.published_at < :oci_b_var_3 AND a4.is_professional = :oci_b_var_4)) AND (a.deleted_at IS NULL)) ORDER BY a.name





[DC-610] Chaining innerjoins can only be made uni-directional; order matters Created: 30/Mar/10  Updated: 31/Dec/10

Status: Open
Project: Doctrine 1
Component/s: Query
Affects Version/s: 1.2.1, 1.2.2
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Exception e Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 0
Labels: None
Environment:

I've witnessed the problem in Doctrine 1.2.2 and 1.2.1



 Description   

The following code fails

This doesn't work
Doctrine_Query::create()
	->select('Table t1')
	->innerJoin('f1.Foreign2 f2 WITH f1.field = 2')
	->innerJoin('t1.Foreign1 f1')

Doctrine seems only to be happy when we change the order of the inner joins

This works
Doctrine_Query::create()
	->select('Table t1')
	->innerJoin('t1.Foreign1 f1')
	->innerJoin('f1.Foreign2 f2 WITH f1.field = 2')


 Comments   
Comment by Exception e [ 30/Mar/10 ]

fixed typo

Comment by dquintard [ 31/Dec/10 ]

What about this issue.
I've got the same:

$q2 = Doctrine_Query::create()
->select('*')
->from('Model_TForfaitType ft')
->innerJoin('ft.TServiceForfait sf');
echo $q2->getSqlQuery();
$results = $q2->fetchArray();
echo "results:".count($results)."<br>"; => 3 results

$q3 = Doctrine_Query::create()
->select('*')
->from('Model_TServiceForfait sf')
->innerJoin('sf.TForfaitType ft');
echo $q3->getSqlQuery();
$results = $q3->fetchArray();
echo "results:".count($results)."<br>"; => 16 results

$q4 = Doctrine_Query::create()
->select('*')
->from('Model_TServiceForfait,Model_TForfaitType');
echo $q4->getSqlQuery();
$results = $q4->fetchArray();
echo "results:".count($results)."<br>"; => 16 results

Moreover i tried with Doctrine 1.3 and this problem is not resolved.





[DC-608] Limit and Offset breaks query with multiple non-join FROM statements Created: 30/Mar/10  Updated: 30/Mar/10

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

Type: Bug Priority: Major
Reporter: Sid GBF Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 0
Labels: None

Attachments: File query.diff    

 Description   

$query = $query->select('sf_guard_permission.id,
cms_section_translation.slug||\' \'||SUBSTRING("name", POSITION(\'@\' in "name") + 1) as name,
sf_guard_permission.description,
sf_guard_permission.created_at, sf_guard_permission.updated_at');
$query = $query->from('sfGuardPermission sf_guard_permission');
$query = $query->addfrom('CmsSection cms_section');
$query = $query->innerJoin('cms_section.WithCmsSectionTranslation cms_section_translation');
$query = $query->where('cast(SUBSTRING("name", 1, POSITION(\'@\' in "name") - 1) as integer) = cms_section.id');
$query = limit(20);
$query = offset(1);

Generated:

SELECT s.id AS s_id, s.description AS sdescription, s.created_at AS screated_at, s.updated_at AS supdated_at, c2.slug||' '||SUBSTR("name", POSITION('@' in "name") + 1) AS c2_0 FROM sf_guard_permission s, cms_section c INNER JOIN cms_section_translation c2 ON c.id = c2.sec_id WHERE s.id IN (SELECT doctrine_subquery_alias.id FROM (SELECT DISTINCT s2.id, c3.deleted_at FROM sf_guard_permission s2 cms_section c3 INNER JOIN cms_section_translation c4 ON c3.id = c4.sec_id WHERE cast(SUBSTR("name", 1, POSITION('@' in "name") - 1) as integer) = c3.id ORDER BY c3.deleted_at DESC LIMIT 20) AS doctrine_subquery_alias) AND (cast(SUBSTR("name", 1, POSITION('@' in "name") - 1) as integer) = c.id) ORDER BY c.deleted_at DESC

Notice:
SELECT DISTINCT s2.id, c3.deleted_at FROM sf_guard_permission s2 comma that is missing cms_section c3






[DC-601] When using a join and giving an alias to each select column the hyrdrator only returns one row. Created: 25/Mar/10  Updated: 02/Aug/10

Status: Open
Project: Doctrine 1
Component/s: Query, Relations
Affects Version/s: 1.2.2
Fix Version/s: None

Type: Bug Priority: Major
Reporter: will ferrer Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 2
Labels: None
Environment:

XP Xamp



 Description   

Hi All
I am running into a strange problem using Doctrine-1.2.2-- I have a multi row result set but only the first row of it is returned in the hyrdated array that is generated.
I have tracked this down in the code to get a basic idea of whats going on – it seems that the $id variable in the hydrateResultSet function in Doctrine_Hydrator_Graph isn't being populated properly with data about the fields that are returning from my query. The _gatherRowData function seems to never detect that one of my columns is an identifier ("if ($cache[$key]['isIdentifier'])

{" doesn't return true ever so the next line of code: "$id[$dqlAlias] .= '|' . $value;" doesn't run). I think this is the problem but I don't totally understand how the mapping process is taking place in this function so can't be absolutely sure. This hydration problem goes away if I don't use left joins in my query, or if I use a query where not every field I am requesting is assigned an alias (at least one of the fields doesn't use the 'as' syntax). For instance this php: {code}

$q = Doctrine_Query::create();
$q->from('Customer Customer');
$q->leftJoin('Customer.Zip Zip');
$q->addSelect('Customer.firstname as first_name');
$q->addSelect('Customer.postalcode as postalcode');
$q->setHydrationMode(Doctrine::HYDRATE_ARRAY);

Generates this DQL: 

SELECT Customer.firstname as first_name, Customer.postalcode as postalcode FROM Customer Customer LEFT JOIN Customer.Zip Zip

And this SQL:

SELECT c.firstname AS c_0, c.postalcode AS c_1 FROM customers c LEFT JOIN zips z ON c.postalcode = z.postalcode

Which results in this return after hyrdration: 

array('0'=>array('first_name'=>'Armando', 'postalcode'=>'00659'))

However the following code hydrates just fine: 

$q = Doctrine_Query::create();
$q->from('Customer Customer');
$q->addSelect('Customer.firstname as first_name');
$q->setHydrationMode(Doctrine::HYDRATE_ARRAY);

As does this code: 

$q = Doctrine_Query::create();
$q->from('Customer Customer');
$q->leftJoin('Customer.Zip Zip');
$q->addSelect('Customer.firstname');
$q->addSelect('Customer.postalcode as postalcode');
$q->setHydrationMode(Doctrine::HYDRATE_ARRAY);

Here is the yaml for the sample data I am testing on: 

detect_relations: false
package: Example
options:
type: INNODB
charset: utf8
Customer:
tableName: customers
columns:
customer_id:
type: integer(4)
primary: true
notnull: true
autoincrement: true
firstname:
type: string(45)
lastname:
type: string(45)
streetaddress:
type: string(45)
city:
type: string(45)
state:
type: string(45)
postalcode:
type: string(45)
relations:
Order:
type: many
local: customer_id
foreign: customer_id
Zip:
type: one
local: postalcode
foreign: postalcode
options:
type: InnoDB
Zip:
connection: default_schema
tableName: zips
columns:
postalcode:
type: varchar(30)
primary: true
latitude: 'float(10,6)'
longitude: 'float(10,6)'
city: string(50)
state: string(50)
country: string(50)
type: string(50)
relations:
Customer:
type: many
local: postalcode
foreign: postalcode


Perhaps there is something simple I am overlooking. To get around this I am just always selecting the primary key from my main table in every query.

Thanks in advance for any advice.
Will Ferrer



 Comments   
Comment by will ferrer [ 03/Apr/10 ]

This problem also exists in 1.2.2 so I have updated the post to reflect this.

Comment by Peter Bücker [ 06/Apr/10 ]

I experienced the same problem with Doctrine 1.2 (r7329). I also fixed this by adding the primary key of the table I select from to the select list.

Comment by will ferrer [ 07/Apr/10 ]

Hi Peter

Thats how I am currently working around the bug as well but hopefully it will get rectified in a later version of doctrine.

Thanks for the comment.

Will

Comment by Sam Doun [ 08/Jun/10 ]

Hi all,

I'm new to doctrine and since yesterday, I'm experiencing exactly the same behavior.
Any news about this subject ? Is there a doctrine version where the pb is solved ?
Help !

Regards,
Doun

Comment by will ferrer [ 08/Jun/10 ]

Hi Sam

Currently I am working around this bug by always adding the primary key of the table to the select (like peter also mentions above).

It is a work around for the problem but so far it has been reliable for me.

I hope that helps until there is a patch for it.

Will Ferrer

Comment by Sam Doun [ 09/Jun/10 ]

Hi Will

I'll do so.
Thank you VM.

Sam Doun

Comment by Shuchi Sethi [ 02/Aug/10 ]

Hi,

Has there been any patch release for the same?
We just upgraded from Doctrine 1.1 to 1.2 and our project now crashes.

For DQL -

function getServiceDetails($merchantId,$merchantServiceId) {
    $q = Doctrine_Query::create()
        ->select('b.*, m.name as merchant_service_name,payment_mode.name as payment_mode_name,payment_mode.id as paymentMode,p.id as paymentModeOption ')
        ->from('ServicePaymentModeOption b')
        ->leftJoin("b.MerchantService m")
        ->leftJoin("m.Merchant merchant")
        ->leftJoin("b.PaymentModeOption p")
        ->leftJoin("p.PaymentMode payment_mode")
        ->where("merchant.id=?",$merchantId)
        ->andWhere("b.merchant_service_id=?",$merchantServiceId)
        ->groupBy('p.name');

    return $res = $q->execute(array(),Doctrine::HYDRATE_ARRAY);
  }  

Result with Doctrine 1.1 is

Array
(
    [0] => Array
        (
            [id] => 1
            [merchant_service_id] => 1
            [payment_mode_option_id] => 1
            [created_at] =>
            [updated_at] =>
            [deleted] =>
            [created_by] =>
            [updated_by] =>
            [merchant_service_name] => NIS Passport
            [merchant_name] => NIS
            [payment_mode_option_name] => Bank
            [payment_mode_name] => Bank
            [paymentMode] => 1
            [paymentModeOption] => 1
            [MerchantService] => Array
                (
                    [merchant_service_name] => NIS Passport
                    [Merchant] => Array
                        (
                            [merchant_name] => NIS
                        )

                )

            [PaymentModeOption] => Array
                (
                    [payment_mode_option_name] => Bank
                    [paymentModeOption] => 1
                    [PaymentMode] => Array
                        (
                            [payment_mode_name] => Bank
                            [paymentMode] => 1
                        )

                )

        )
)

Result with Doctrine 1.2 is

Array
(
    [0] => Array
        (
            [id] => 1
            [merchant_service_id] => 1
            [payment_mode_option_id] => 1
            [created_at] =>
            [updated_at] =>
            [deleted_at] =>
            [created_by] =>
            [updated_by] =>
            [merchant_service_name] => NIS Passport
            [merchant_name] => NIS
            [payment_mode_option_name] => Bank
            [payment_mode_name] => Bank
            [paymentMode] => 1
            [paymentModeOption] => 1
        )
)

We have used Hydration for the result set at lot of places. Please suggest if there could be a fix without going about revising everything that has been coded.

Looking forward to a quick response.





[DC-600] Query Cache causes exception when using array parameter and IN in a where clause Created: 25/Mar/10  Updated: 21/Jul/10

Status: Open
Project: Doctrine 1
Component/s: Query
Affects Version/s: 1.2.1, 1.2.2
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Jay Klehr Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 1
Labels: None
Environment:

PHP 5.2.11 Linux, Mysql 5.0.x


Attachments: File DC600TestCase.php     Text File quickfix.txt    

 Description   

Following the documentation, I'm using an IN condition in a where clause on a Doctrine_Query with Query cache enabled (APC). Syntax:

$values = array(1, 2, 3, 4);

$q = Doctrine_Query::create()>from('SomeModel s')>where('s.column IN ?', array($values));

$results = $q->execute();

The first time this query is run, all is fine, and any subsequent runs will be fine as well, UNLESS the values array changes length, like so:

$values = array(1, 2, 3, 4, 5);

$q = Doctrine_Query::create()>from('SomeModel s')>where('s.column IN ?', array($values));

$results = $q->execute();

With the query cache enabled, this will throw a PDO "Invalid Parameter Count" exception, because it appears that Doctrine uses the cached query which only has 4 placeholders, but the passed parameters now has a count of 5.

A change in the length of the parameters array in this situation should cause Doctrine to insert a new entry into the query cache for this query.

Let me know if you need more information regarding this, if I find time I can try to put together a Test case.

Thanks!



 Comments   
Comment by Jay Klehr [ 30/Mar/10 ]

Failing test case exhibiting this bug.

Comment by Jay Klehr [ 30/Mar/10 ]

I've attached a TestCase that I put together that shows this bug.

Turns out that when using the sqlite adapter, the exception isn't thrown, so I had to look at the profiler in order to show that the query fetched from the cache has the wrong parameter count.

I also tested this with the "whereIn()" method instead of just "where()". whereIn works correctly (but isn't as flexible as where, so I don't believe it's an acceptable replacement in all situations).

Comment by Jay Klehr [ 30/Mar/10 ]

fixing my quoted blocks

Comment by Jay Klehr [ 30/Mar/10 ]

Updated test case. I discovered that sqlite DOES throw an exception in the array's length grows in the second query so added another test to my test case to exhibit this.

Comment by Till Seifert [ 21/Jul/10 ]

Hi, this bug just hit me hard, and made a quick fix:

in the Query/Abstract.php

the function calculateQueryCacheHash was augmented with the param-count:

public function calculateQueryCacheHash($params = array())

{ $dql = $this->getDql(); /// quickfox by Till Seifert $hash = md5($dql . '|' . count($this->getFlattenedParams($params)) . '|' . var_export($this->_pendingJoinConditions, true) . 'DOCTRINE_QUERY_CACHE_SALT'); return $hash; }

and in protected function _execute($params) the params are passed:

Line:927
++ $hash = $this->calculateQueryCacheHash($dqlParams);

cann anyone comment on if this could be horribly wrong? for know this fixes this bug for me.

[EDIT: posted code as attachment, for better readability)

Comment by Till Seifert [ 21/Jul/10 ]

proposed fix. not a patch, sorry.

it's also faulty: it runs getFlattenedParams twice, and it fails when you have more than 1 "x IN Array" condition. If the global number or queryparams stays the same, no new cache-entry is created, so you wold have somehow incorporate the individual param-parts-sizes in the cache-hash, not just the number of params.





[DC-595] Array hydration fails when sorting DESC Created: 23/Mar/10  Updated: 23/Mar/10

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

Type: Bug Priority: Major
Reporter: admirau Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Windows, Ubuntu, PostgreSQL,



 Description   

Wrong array hydration results.

Running toArray() on non hydrated collection
produces different results than using array hydrator.

Case1.
Sorting ASC (works)

$reputationHydrated = Doctrine_Query::create()
->from('User_Model_UserReputation ur')
->leftJoin('ur.User u')
->leftJoin('ur.Thread t')
->leftJoin('t.ForumEntry fe')
->where('u.id = ?', 1)
->orderBy('ur.event ASC)
->execute(null, Doctrine_Core::HYDRATE_ARRAY);

$reputation = Doctrine_Query::create()
->from('User_Model_UserReputation ur')
->leftJoin('ur.User u')
->leftJoin('ur.Thread t')
->leftJoin('t.ForumEntry fe')
->where('u.id = ?', 1)
->orderBy('ur.event ASC)
->execute();

$reputationToArray = $reputation->toArray();

die($reputationToArray === $reputationHydrated); // true, as expected

Case 2.
Sorting DESC (does not work)

$reputationHydrated = Doctrine_Query::create()
->from('User_Model_UserReputation ur')
->leftJoin('ur.User u')
->leftJoin('ur.Thread t')
->leftJoin('t.ForumEntry fe')
->where('u.id = ?', 1)
->orderBy('ur.event DESC)
->execute(null, Doctrine_Core::HYDRATE_ARRAY);

$reputation = Doctrine_Query::create()
->from('User_Model_UserReputation ur')
->leftJoin('ur.User u')
->leftJoin('ur.Thread t')
->leftJoin('t.ForumEntry fe')
->where('u.id = ?', 1)
->orderBy('ur.event DESC)
->execute();

$reputationToArray = $reputation->toArray();

die($reputationToArray === $reputationHydrated); // false, should be true

Other hydrators do hydrate as expected.

Seems that something is wrong in Doctrine_Hydrator_Graph.



 Comments   
Comment by admirau [ 23/Mar/10 ]

Changed one condition in Graph.php, and it works.
Here is a patch:

— Doctrine/Hydrator/Graph.php
+++ Doctrine/Hydrator/Graph-new.php
@@ -212,7 +212,7 @@
$indexExists = isset($identifierMap[$path][$id[$parent]][$id[$dqlAlias]]);
$index = $indexExists ? $identifierMap[$path][$id[$parent]][$id[$dqlAlias]] : false;
$indexIsValid = $index !== false ? isset($prev[$parent][$relationAlias][$index]) : false;

  • if ( $indexExists || ! $indexIsValid) {
    \ No newline at end of file
    + if ( ! $indexExists || ! $indexIsValid) {
    \ No newline at end of file
    $element = $this->getElement($data, $componentName);
    $event->set('data', $element);
    $listeners[$componentName]->postHydrate($event);

Plase check if this does not breaks something else.
Tests are passing.





[DC-581] ORACLE: Missing fields on subquery with Many:Many Created: 18/Mar/10  Updated: 18/Mar/10

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

Type: Bug Priority: Major
Reporter: Bertrand Zuchuat Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 0
Labels: None

Attachments: File DC581TestCase.php    

 Description   

I use this schema:

---
Actuality:
  connection:         web
  tableName:          actuality
  actAs:
    softDelete:       ~
    Timestampable:    ~
    Sluggable:
      fields:         [name]
      indexName:      actuality_sluggable
      canUpdate:      true
  columns:
    id:
      type:           integer(4)
      primary:        true
      unsigned:       true
      sequence:       actuality
    created_at:
      type:           timestamp
    updated_at:
      type:           timestamp
    published_at:
      type:           timestamp
      notnull:        true
    unpublished_at:
      type:           timestamp
    name:
      type:           string(255)
      notnull:        true
    description_short:
      type:           string(1000)
      notnull:        true
    description:
      type:           clob
      notnull:        true
    is_professional:
      type:           boolean
      default:        true
  relations:
    Categories:
      class: ActualityCategory
      local: actuality_id
      foreign: actuality_category_id
      refClass: ActualityActualityCategory
      foreignAlias: Actualities


ActualityActualityCategory:
  connection:         web
  tableName:          actuality_actuality_category
  options:
    symfony:
      form:   false
      filter: false
  columns:
    actuality_id:
      type:           integer(4)
      primary:        true
      unsigned:       true
    actuality_category_id:
      type:           integer(2)
      primary:        true
      unsigned:       true
  relations:
    Actuality:
      onDelete:       CASCADE
    

ActualityCategory:
  connection:         web
  tableName:          actuality_category
  actAs:
    softDelete:       ~
    Timestampable:    ~
    Sluggable:
      fields:         [name]
      indexName:      actualityc_sluggable
      canUpdate:      true
  columns:
    id:
      type:           integer(2)
      primary:        true
      unsigned:       true
      sequence:       actualitycategory
    created_at:
      type:           timestamp
    updated_at:
      type:           timestamp
    name:
      type:           string(60)
      notnull:        true

Function in my model Actuality

public function retrieveActive($is_authenticated = false)
{
  $q = $this->createQuery('a')
  ->leftJoin('a.Categories c')
  ->orderBy('a.published_at DESC');
  
  if (!$is_authenticated)
  {
    $q->where('a.is_professional = ?', false);
  }
  
  return $q;
}

with that, i receive the message "Doctrine_Connection_Oracle_Exception" because Oracle is strict and in the subselect, the field published_at is missing in select. This is the query generate by doctrine:

SELECT a.id AS a__id, a.published_at AS a__published_at, a.name AS a__name, a.description_short AS a__description_short, a.slug AS a__slug, a2.id AS a2__id, a2.name AS a2__name FROM actuality a LEFT JOIN actuality_actuality_category a3 ON (a.id = a3.actuality_id) LEFT JOIN actuality_category a2 ON a2.id = a3.actuality_category_id WHERE a.id IN (SELECT a4.id FROM ( SELECT DISTINCT a4.id FROM actuality a4 LEFT JOIN actuality_actuality_category a6 ON (a4.id = a6.actuality_id) LEFT JOIN actuality_category a5 ON a5.id = a6.actuality_category_id WHERE a4.is_professional = 0 ORDER BY a4.published_at DESC ) a4 WHERE ROWNUM <= 5) AND (a.is_professional = 0) ORDER BY a.published_at DESC

If i execute this query and add the field published_at in subquery, this is work.

SELECT a.id AS a__id, a.published_at AS a__published_at, a.name AS a__name, a.description_short AS a__description_short, a.slug AS a__slug, a2.id AS a2__id, a2.name AS a2__name FROM actuality a LEFT JOIN actuality_actuality_category a3 ON (a.id = a3.actuality_id) LEFT JOIN actuality_category a2 ON a2.id = a3.actuality_category_id WHERE a.id IN (SELECT a4.id FROM ( SELECT DISTINCT a4.id, a4.published_at  FROM actuality a4 LEFT JOIN actuality_actuality_category a6 ON (a4.id = a6.actuality_id) LEFT JOIN actuality_category a5 ON a5.id = a6.actuality_category_id WHERE a4.is_professional = 0 ORDER BY a4.published_at DESC ) a4 WHERE ROWNUM <= 5) AND (a.is_professional = 0) ORDER BY a.published_at DESC

Thanks for your help.



 Comments   
Comment by Bertrand Zuchuat [ 18/Mar/10 ]

I specify another thing. I use this with a pager.

Comment by Bertrand Zuchuat [ 18/Mar/10 ]

I create this testcase to find the bug but with that, the test is OK.

I execute the same code with symfony/doctrine but the query isn't the same. Why ????

Extract from symfony exception (Doctrine_Connection->execute)

SELECT
a.id AS a__id, a.published_at AS a__published_at, a.name AS a__name, a.description_short AS a__description_short,
a.slug AS a__slug, a2.id AS a2__id, a2.name AS a2__name
FROM actuality a
LEFT JOIN actuality_actuality_category a3 ON (a.id = a3.actuality_id)
LEFT JOIN actuality_category a2 ON a2.id = a3.actuality_category_id WHERE a.id
IN (SELECT a4.id FROM ( SELECT DISTINCT a4.id FROM actuality a4 LEFT JOIN actuality_actuality_category a6 ON (a4.id = a6.actuality_id)
LEFT JOIN actuality_category a5 ON a5.id = a6.actuality_category_id
WHERE a4.is_professional = ? ORDER BY a4.published_at DESC ) a4 WHERE ROWNUM <= 5) AND (a.is_professional = ?) ORDER BY a.published_at DESC




[DC-575] Select distinct limit problem Created: 16/Mar/10  Updated: 30/Jun/10

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

Type: Bug Priority: Major
Reporter: O. Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 0
Labels: None
Environment:

php 5.3.0, mysql 5.1.36, symfony 1.4.3


Attachments: Java Source File schema.yml    

 Description   

1)Code
Doctrine_Query::create ()
->select ( "u.user_id,mr.message_id,mr.message_senddate,mr.message_title,mr.message_content,mr.message_alias,mr.message_categoryid," )
->from ( "user u" )
->leftJoin ( "u.messages mr ON u.user_id = mr.message_sender" )
->where ( "u.user_id = ?", 2 )
->limit(1)
->fetchArray ();

This code must return only one result but it returns all results in which user_id is 2 in the database

If i looked it in Symfony Debug bar, i saw that;

2)Symfony debug toolbar values:

#Query A
SELECT DISTINCT u2.user_id FROM users u2 LEFT JOIN messages m2 ON (u2.user_id = m2.message_sender) WHERE u2.user_id = 2 LIMIT 1

#Query B
SELECT u.user_id AS u_user_id, m.message_id AS mmessage_id, m.message_senddate AS mmessage_senddate, m.message_title AS mmessage_title, m.message_content AS mmessage_content, m.message_alias AS mmessage_alias, m.message_categoryid AS m_message_categoryid FROM users u LEFT JOIN messages m ON (u.user_id = m.message_sender) WHERE u.user_id IN ('2') AND (u.user_id = 2)

And if i test this code in MYSQL query browser i got a result like that;

SELECT u.user_id AS u_user_id, m.message_id AS mmessage_id, m.message_senddate AS mmessage_senddate, m.message_title AS mmessage_title, m.message_content AS mmessage_content, m.message_alias AS mmessage_alias, m.message_categoryid AS m_message_categoryid
FROM users u
LEFT JOIN messages m ON ( u.user_id = m.message_sender )
WHERE u.user_id
IN (
'2'
)
AND (
u.user_id =2
)
LIMIT 0 , 30

If i changed SQL code like below and put the limit at end, the problem solved:

#Query A
SELECT DISTINCT u2.user_id FROM users u2 LEFT JOIN messages m2 ON (u2.user_id = m2.message_sender) WHERE u2.user_id = 2

#Query B
SELECT u.user_id AS u_user_id, m.message_id AS mmessage_id, m.message_senddate AS mmessage_senddate, m.message_title AS mmessage_title, m.message_content AS mmessage_content, m.message_alias AS mmessage_alias, m.message_categoryid AS m_message_categoryid FROM users u LEFT JOIN messages m ON (u.user_id = m.message_sender) WHERE u.user_id IN ('2') AND (u.user_id = 2) LIMIT 1

But doctrine's limit(1) metod is Select Distinct

The query which starts with ... is at query A but it needs to be at the end of query B



 Comments   
Comment by O. [ 16/Mar/10 ]

My schema file.

Comment by Jonathan H. Wage [ 16/Mar/10 ]

It is hard to understand your issue description. Can you make a clear failing Doctrine test case for us to look at? Thanks, Jon

Comment by O. [ 17/Mar/10 ]

Adding problem screencast:
http://www.screencast-o-matic.com/watch/c6ebiN1qS

Comment by O. [ 17/Mar/10 ]

I found the source of the problem:
lib\vendor\doctrine\Doctrine\Query.php in getSqlQuery() function.

$limitSubquery values are default TRUE, but FALSE have to be here.. (to me)
How can we define the value of the DQL $limitSubquery?

Comment by will ferrer [ 30/Jun/10 ]

Hi O

I made a patch which will allow you to turn off the limit subquery. If you would like to check it out here is the bug it is posted on:

http://www.doctrine-project.org/jira/browse/DC-701

Hope that helps.

Will





[DC-554] Distinct query does not work Created: 09/Mar/10  Updated: 15/Mar/10

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

Type: Bug Priority: Major
Reporter: Mishal Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 0
Labels: None


 Description   

$limit = 10;
$q = 'Me';
$field = 'contractor';

$q = Doctrine_Query::create()
->select(sprintf('DISTINCT PublicContract.%s', $field))
->from('PublicContract')
->where(sprintf('PublicContract.%s LIKE ?', $field), sprintf('%%%s%%', $q))
->limit($limit);

The QUERY is WRONG:

// SELECT DISTINCT p.id AS p_id, p.contractor AS p_contractor FROM public_contract p WHERE (p.contractor LIKE ?) LIMIT 10 - (%Me%)

I can simply GROUP BY the contractor field, because DISTINCT can be considered as a special case of GROUP BY - and have the results I want:

$q = Doctrine_Query::create()
->select(sprintf('PublicContract.%s', $field))
->from('PublicContract')
->where(sprintf('PublicContract.%s LIKE ?', $field), sprintf('%%%s%%', $q))
->groupBy(sprintf('PublicContract.%s', $field))
->limit($limit);



 Comments   
Comment by Jonathan H. Wage [ 15/Mar/10 ]

Test case or patch?





[DC-499] Doctrine_Query_Set incorrectly rewrites expressions Created: 14/Feb/10  Updated: 04/Feb/11

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

Type: Bug Priority: Major
Reporter: Lukas Kahwe Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 1
Labels: None


 Description   

First up Doctrine_Query_Set should extend Doctrine_Query_Condition so that the parseLiteralValue method is available in _processPossibleAggExpression().

However I am not sure what this method is really supposed to do. It seems to do some reordering of clauses, but in my case it actually drops off some parts of my expression:
Doctrine_Query::create()
->update('Document')
->set('clause_ordering', "IF(clause_ordering IS NULL, '$id', CONCAT(clause_ordering, ',$id'))")
->where('id = ?', $document_id)
->execute();

Results in "UPDATE document SET clause_ordering = IF(clause_ordering IS NULL, '67', CONCAT(clause_ordering)) WHERE (id = ?)"

And not as expected "UPDATE document SET clause_ordering = IF(clause_ordering IS NULL, '67', CONCAT(clause_ordering, ',67')) WHERE (id = ?)"

Adding a space after IF and CONCAT fixes the issue, but is obviously a hack to get around the regexp in _processPossibleAggExpression()



 Comments   
Comment by Raphael Araújo [ 19/Jun/10 ]

Guilherme,
Como eu sei que você fala portuguese e I dont speek english, vou falar em português mesmo. Qualquer coisa você traduz.

Implementei uma solução meio nebulosa aqui: http://pastie.org/1011675

O que esta pegando é a expressão regular. Ela não esta aceitando muito bem quando existe na "$hasAggExpression" argumentos separados por vírgula.
O meu teste foi um pouco diferente do que o criado pelo Lukas. Veja:

->set('p.tempo_atual', 'IFNULL(p.tempo_atual, p.lance_tempo) - 1')

Na implementação atual ele descarto o segundo argumento do IFNULL(). Não tenho certeza se minha proposta de implementação é correta. não entendi muito bem o funcionamento do preg_match_all. Ao meu ver ele sempre retornaria apenas a posicao 0, mas foi uitlizado um foreach, não entendi o pq. Então como esta com foreach eu deixei e coloquei uma 'glue' variável para o implode no final.

Comment by Sebastião Farias Júnior [ 04/Feb/11 ]

Tem como ignorar esta verificação? Pq o que esta acontecendo e que se eu digitar numa frase, exemplo, " teste( 89,0%), " o doctrine pensa que é uma expressão, como evitar isto?





[DC-451] preDql events on connection & manager level Created: 25/Jan/10  Updated: 25/Jan/10

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

Type: Improvement Priority: Major
Reporter: Sune Kibsgaard Pedersen Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 0
Labels: None


 Description   

I think it would be very helpful if the preDql events also worked on connection and manager level. If it is anyway possible.

attach a record listener to a connection object, and it should also call the preDql events when doing DQL queries.






[DC-424] using boolean logic in select don't work Created: 14/Jan/10  Updated: 27/Jan/10

Status: Open
Project: Doctrine 1
Component/s: Query
Affects Version/s: 1.1.6, 1.2.1
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Ivo Võsa Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 0
Labels: None


 Description   

Continuing with same setup as in http://www.doctrine-project.org/jira/browse/DC-417
I discovered another issue

$result = Doctrine_Query::create()
    ->select('u.*, m.*, COUNT(m.id) > 0 AS match_check')
    ->from('User u')
    ->leftJoin('u.Matches m')
    ->groupBy('u.id')
    ->orderBy('match_check DESC, RAND()')
    ->execute(array(), Doctrine::HYDRATE_ARRAY);

match_check will actually be count of matches not 0 or 1 as i expected.
is it bug or feature?



 Comments   
Comment by Guilherme Blanco [ 14/Jan/10 ]

SelectExpressions cannot contain ComparisonExpressions.

That's a limitation of Doctrine 1, which was resolved in Doctrine 2.

Cheers,

Comment by Josh Boyd [ 27/Jan/10 ]

Something like this will work:

$result = Doctrine_Query::create()
    ->select('u.*, m.*)
    ->addSelect("QUOTE(IF(COUNT(m.id) > 0),1,0)) match_check')
    ->from('User u')
    ->leftJoin('u.Matches m')
    ->groupBy('u.id')
    ->orderBy('match_check DESC, RAND()')
    ->execute(array(), Doctrine::HYDRATE_ARRAY);




[DC-402] aliased fields from root class are not recognized als root class fields, if they're the only one Created: 06/Jan/10  Updated: 06/Jan/10

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

Type: Bug Priority: Major
Reporter: Christian Michel Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 0
Labels: None


 Description   

If all selected fields from the root class are aliased,
Doctrine_Query->processPendingFields() won't recognize them as fields of the table and the following exception will be thrown:
"The root class of the query (alias c) must have at least one field selected."

In my real world example, this could only be fixed, using the primary key of the root class/table in select






[DC-389] query cache doesn't cache _isLimitSubqueryUsed Created: 26/Dec/09  Updated: 17/Apr/14

Status: Open
Project: Doctrine 1
Component/s: Caching, Query
Affects Version/s: 1.1.5, 1.1.6
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Peter Kovacs Assignee: Jonathan H. Wage
Resolution: Unresolved Votes: 1
Labels: None
Environment:

Postgres db, php5.2, linux



 Description   

The problem is that _isLimitSubqueryUsed is not cached with query cache.
It gets calculated when building query, but when the query is coming
from cache it's not.
Because of this, line 1087 of Query/Abstract.php is never executed,
when coming from cache:
if ($this->isLimitSubqueryUsed() &&
$this->_conn->getAttribute(Doctrine::ATTR_DRIVER_NAME) !==
'mysql')

{ $params = array_merge((array) $params, (array) $params); } Maybe it is on purpose, but I didn't get any answer on the google-groups. Here is a diff I use now: Index: trunk/gui/doctrine-library/Doctrine/Query/Abstract.php =================================================================== --- a/trunk/gui/doctrine-library/Doctrine/Query/Abstract.php +++ b/trunk/gui/doctrine-library/Doctrine/Query/Abstract.php @@ -1286,4 +1286,5 @@ $cached = unserialize($cached); $this->_tableAliasMap = $cached[2]; + $this->_isLimitSubqueryUsed = $cached[3]; $customComponent = $cached[0]; @@ -1346,5 +1347,5 @@ }

  • return serialize(array($customComponent, $componentInfo,
    $this->getTableAliasMap()));
    + return serialize(array($customComponent, $componentInfo,
    $this->getTableAliasMap(), $this->isLimitSubqueryUsed()));
    }


 Comments   
Comment by Pablo Grass [ 27/Jun/11 ]

This still seems to be a problem in version 1.2.4, rendering the query cache unusable for our project.
The suggested fix works fine, and seems to hold litte potential for trouble.

Anyone still listening/reading here? We are aware of the EOL, but would love to produce a test case to anyone ("official") trying to fix this - just not sure if it is still worth bothering...





[DC-386] Doctrine_Hydrator_ArrayDriver may segfault Php when loaded by Zend Framework Autoloader Created: 24/Dec/09  Updated: 24/Dec/09

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

Type: Bug Priority: Major
Reporter: Michael Gatto Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Ubuntu 9.04 fully updated with Apache 2.2.11, Php 5.2.6, Zend Framework 1.9.6, Doctrine 1.2.1


Attachments: File Doctrine.php     File doctrine_trace.txt.xt     File TestingController.php    

 Description   

Apache and Php segfault with no error information in the logs and a white screen of death when I use Doctrine 1.2.1 in a Zend Framework controller.

I've traced the problem to line 126 of Zend/Loader/Autoloader.php which only seemed to occur when it autoloaded Doctrine_Hydrator_ArrayDriver during a call to save() in the controller.

I debugged with xdebug and have attached a simple test controller with no other code. The ZF page works fine when all Doctrine is commented out and a Doctrine test page with the same code but outside of ZF worked fine via Apache as did a CLI script.

Workaround is to not push Doctrine to Zend Loader in the bootstrap.

Here is the debug output just before a segfault, the stack and the variable values:

Remote Launch (stepping)
Library/Zend/Loader/Autoloader.php.Zend_Loader_Autoloader::autoload : lineno 126
Library/Zend/Loader/Autoloader.php.is_subclass_of : lineno 0
Library/Doctrine/Query.php.Doctrine_Query->processPendingFields : lineno 477
Library/Doctrine/Query.php.Doctrine_Query->buildSqlQuery : lineno 1213
Library/Doctrine/Query.php.Doctrine_Query->getSqlQuery : lineno 1122
Library/Doctrine/Query/Abstract.php.Doctrine_Query_Abstract->_getDqlCallbackComponents : lineno 1137
Library/Doctrine/Query/Abstract.php.Doctrine_Query_Abstract->_preQuery : lineno 1106
Library/Doctrine/Query/Abstract.php.Doctrine_Query_Abstract->execute : lineno 1001
Library/Doctrine/Template/Listener/Sluggable.php.Doctrine_Template_Listener_Sluggable->getUniqueSlug : lineno 207
Library/Doctrine/Template/Listener/Sluggable.php.Doctrine_Template_Listener_Sluggable->buildSlugFromFields : lineno 120
Library/Doctrine/Template/Listener/Sluggable.php.Doctrine_Template_Listener_Sluggable->preInsert : lineno 65
Library/Doctrine/Record/Listener/Chain.php.Doctrine_Record_Listener_Chain->preInsert : lineno 342
Library/Doctrine/Record.php.Doctrine_Record->invokeSaveHooks : lineno 355
Library/Doctrine/Connection/UnitOfWork.php.Doctrine_Connection_UnitOfWork->insert : lineno 551
Library/Doctrine/Connection/UnitOfWork.php.Doctrine_Connection_UnitOfWork->saveGraph : lineno 81
Library/Doctrine/Record.php.Doctrine_Record->save : lineno 1691
Application/controllers/TestingController.php.TestingController->indexAction : lineno 20
Library/Zend/Controller/Action.php.Zend_Controller_Action->dispatch : lineno 513
Library/Zend/Controller/Dispatcher/Standard.php.Zend_Controller_Dispatcher_Standard->dispatch : lineno 289
Library/Zend/Controller/Front.php.Zend_Controller_Front->dispatch : lineno 946
Library/Zend/Application/Bootstrap/Bootstrap.php.Zend_Application_Bootstrap_Bootstrap->run : lineno 77
Application/Bootstrap.php.Bootstrap->run : lineno 52
Library/Zend/Application.php.Zend_Application->run : lineno 346
Public/index.php.

{main}

: lineno 26

=====-=

$class Doctrine_Hydrator_ArrayDriver
$object Zend_Loader_Autoloader
_autoloaders Array [1]
0 Array [2]
0 Doctrine
1 autoload
_defaultAutoloader Array [2]
0 Zend_Loader
1 loadClass
_fallbackAutoloader false
_internalAutoloader Array [2]
0 Zend_Loader_Autoloader
1 _autoload
_namespaces Array [5]
Zend_ true
ZendX_ true
Lisantra_ true
Persistence_ true
Doctrine_ true
_namespaceAutoloaders Array [1]
Array [1]
0 Array [2]
0 Doctrine
1 autoload
_suppressNotFoundWarnings false
$self Zend_Loader_Autoloader
_autoloaders Array [1]
0 Array [2]
0 Doctrine
1 autoload
_defaultAutoloader Array [2]
0 Zend_Loader
1 loadClass
_fallbackAutoloader false
_internalAutoloader Array [2]
0 Zend_Loader_Autoloader
1 _autoload
_namespaces Array [5]
Zend_ true
ZendX_ true
Lisantra_ true
Persistence_ true
Doctrine_ true
_namespaceAutoloaders Array [1]
Array [1]
0 Array [2]
0 Doctrine
1 autoload
_suppressNotFoundWarnings false
$method _autoload
$autoloader Array [0]






[DC-387] mssql fails on find($key) Created: 25/Dec/09  Updated: 05/Jan/10

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

Type: Bug Priority: Major
Reporter: zerkms Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 0
Labels: None


 Description   

Doctrine_Core::getTable('Project')->find($id)
this kind of expression produces query like "SELECT TOP 1 [inner_tbl].[id] AS [p__id] FROM (SELECT TOP 1 [p].[id] AS [p__id], [p].[name] AS [p__name], [p].[description] AS [p__description] FROM [project] [p] WHERE ([p].[id] = ?)) AS [inner_tbl]"
which fails because there is no [inner_tbl].[id]



 Comments   
Comment by Michael Card [ 05/Jan/10 ]

Close as duplicate of DC-289





[DC-362] Doctrine fails to create correct table structure if model is named "User" Created: 16/Dec/09  Updated: 08/Jan/10

Status: Open
Project: Doctrine 1
Component/s: Behaviors, Query, Relations
Affects Version/s: 1.2.0-RC1
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Michael Henriksen Assignee: Jonathan H. Wage
Resolution: Unresolved Votes: 1
Labels: None
Environment:

Mac OS X 10.6.2 (10C540) (Snow Leopard) with MAMP 1.8 bundle:
PHP Version: 5.2.10,
MySQL Version: 5.1.37



 Description   

When building models and database structure from YAML schema, Doctrine ignores the behaviors and relations on models if it is named "User":

...

User:  
  actAs:
    Timestampable:
    Sluggable:
      unique: true
      fields: username
      canUpdate: true
  columns:
    id:
      type: integer(4)
      primary: true
      autoincrement: true
    company_id
      type: integer(4)
    timezone_id:
      type: integer(1)
    role_id:
      type: integer(1)
    email:
      type: string(255)
    username:
      type: string(255)
      unique: true
    password:
      type: string(40)
    firstname:
      type: string(255)
    lastname:
      type: string(255)
    last_login:
      type: datetime
  relations:
    Company:
      local: company_id
      foreign: id
    Timezone:
      local: timezone_id
      foreign: id
    Role:
      local: role_id
      foreign: id

...

This creates the following table structure:

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `company_id` int(11) DEFAULT NULL,
  `timezone_id` tinyint(4) DEFAULT NULL,
  `role_id` tinyint(4) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `username` varchar(255) DEFAULT NULL,
  `password` varchar(40) DEFAULT NULL,
  `firstname` varchar(255) DEFAULT NULL,
  `lastname` varchar(255) DEFAULT NULL,
  `last_login` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The table structure is both missing foreign key constraints to Company, Timezone and Role. It is also missing columns updated_at and created_at for Timestampable behavior. The slug column for Sluggable behavior is missing, as well.

When looking at the BaseUser::setUp() method it looks as it is supposed to:

class BaseUser extends Doctrine_Record
{

    ....

    public function setUp()
    {
        parent::setUp();
        $this->hasOne('Company', array(
             'local' => 'company_id',
             'foreign' => 'id'));

        $this->hasOne('Timezone', array(
             'local' => 'timezone_id',
             'foreign' => 'id'));

        $this->hasOne('Role', array(
             'local' => 'role_id',
             'foreign' => 'id'));

        $timestampable0 = new Doctrine_Template_Timestampable();
        $sluggable0 = new Doctrine_Template_Sluggable(array(
             'unique' => true,
             'fields' => 'username',
             'canUpdate' => true,
        ));
        $this->actAs($timestampable0);
        $this->actAs($sluggable0);
    }

    ....

}

So the only area where it goes wrong, is when generating the queries for creating the model tables. I don't know if the same problem appears with other model names, but it doesn't like models named "User".

If I rename the model to "Person" and rebuild, it all works perfectly as it should.



 Comments   
Comment by Michael Henriksen [ 16/Dec/09 ]

Forgot to mention, that it works perfectly when renaming model to something else than "User"

Comment by Sander [ 08/Jan/10 ]

Have the same problem in 1.2.1 at PostgreSQL 8.4





[DC-353] Doctrine_Query can't map a main query colum inside a subquery with join Created: 10/Dec/09  Updated: 10/Dec/09

Status: Open
Project: Doctrine 1
Component/s: Query
Affects Version/s: 1.0.12, 1.0.14
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Arian Maykon de Araújo Diógenes Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Apache 2.2.13, PHP 5.3, Fedora 11 or Ubuntu 9.04 (and Symfony 1.2.9/1.2.10 with Doctrine 1.0.12/1.0.14)



 Description   

If i have the following schema:

Medidor:
  tableName: TBLMEDIDOR
  columns:
    CODIGO:
      name: NRCODIGO as codigo
      type: integer(10)
      primary: true
      sequence: SEQMEDCODIGO
    DESCRICAO:
      name: TXDESCRICAO as descricao
      type: string(1000)
      notnull: true

AnaliseMedidor:
  tableName: TBLANALISE_MEDIDOR
  columns:
    CODIGO:
      name: NRCODIGO as codigo
      type: integer(10)
      primary: true
      sequence: SEQANMCODIGO
    DATA:
      name: DTDATA as dataAnalise
      type: date
      notnull: true
    MEDIDOR:
      name: FKMEDCODIGO as medidor_codigo
      type: integer(10)
      notnull: true
    OBSERVACAO:
      name: TXOBSERVACAO as observacao
      type: string(1000)
  relations:
    Medidor: { local: medidor_codigo, foreign: codigo, foreignAlias: Analises }

and execute the following query:

$query = Doctrine::getTable('Medidor')->createQuery()
    ->innerJoin('m.Analises a')
    ->where('a.dataAnalise = (SELECT MAX(a2.dataAnalise) FROM '
        . 'AnaliseMedidor a2 WHERE a2.medidor_codigo = a.medidor_codigo)');

the "a.medidor_codigo" in the subquery don't get correctly mapped. So Doctrine tells me:

{sfDoctrineLogger} executeQuery : SELECT t.nr80102codigo AS t__nr80102codigo, t.tx80102descricao AS t__tx80102descricao FROM TBL80102MEDIDOR t INNER JOIN TBL80103ANALISE_MEDIDOR t2 ON t.nr80102codigo = t2.fk80102codigo WHERE t2.dt80103data = (SELECT MAX(t3.dt80103data) AS t3__0 FROM TBL80103ANALISE_MEDIDOR t3 WHERE t3.fk80102codigo = t2.medidor_codigo)
{Doctrine_Connection_Oracle_Exception} SQLSTATE[HY000]: General error: 904 OCIStmtExecute: ORA-00904: "T2"."MEDIDOR_CODIGO": invalid identifier
 (/root/PDO_OCI-1.0/oci_statement.c:142)

If i put the real name column it runs ok:

$query = Doctrine::getTable('Medidor')->createQuery()
    ->innerJoin('m.Analises a')
    ->where('a.dataAnalise = (SELECT MAX(a2.dataAnalise) FROM '
        . 'AnaliseMedidor a2 WHERE a2.medidor_codigo = a.FKMEDCODIGO)');

But, this should not be done, right?

In Doctrine 1.2 it works fine. But in the related versions don't.



 Comments   
Comment by Arian Maykon de Araújo Diógenes [ 10/Dec/09 ]

Haven't added a TestCase because Doctrine 1.0.14 don't have them yet!





[DC-335] Oracle identifier too long in normal SELECT when using longer table name or column Created: 07/Dec/09  Updated: 13/Oct/10

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

Type: Bug Priority: Major
Reporter: Miloslav "adrive" Kmet Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 1
Labels: None
Environment:

Oracle



 Description   

Unfortunatelly oracle has max identifier length for 30 characters. When using longer model names eg. misCompanyContributor with column names the generated aliases for selected columns mix tableized modelname with colum name joined by two __, and sometimes the generated alias should exceed the allowed 30 characters long identifiers, and oracle complains with:

{{
ORA-00972: identifier is too long
}}

An example of large select with alias:
{{
SELECT
...
mis_contributor.contributor_id AS mis_contributor__contributor_id
..
FROM mis_contributor ...
}}



 Comments   
Comment by Michel D'HOOGE [ 13/Oct/10 ]

A workaround is available in DC-72 for version 1.1.4

Comment by Michel D'HOOGE [ 13/Oct/10 ]

I have applied the following patch to use aliases of identifiers (what is after the "as" when defining field name).

So far, it seems to work... But I am quite sure I didn't take all possible cases into consideration.

--- lib/vendor/doctrine/Doctrine/Hydrator/Graph.php.orig	2010-10-07 19:09:10.000000000 +0200
+++ lib/vendor/doctrine/Doctrine/Hydrator/Graph.php	2010-10-14 00:08:05.000000000 +0200
@@ -298,10 +298,10 @@
                 }
 
                 $e = explode('__', $key);
-                $last = strtolower(array_pop($e));
+                $fieldName = strtolower(array_pop($e));
                 $cache[$key]['dqlAlias'] = $this->_tableAliases[strtolower(implode('__', $e))];
                 $table = $this->_queryComponents[$cache[$key]['dqlAlias']]['table'];
-                $fieldName = $table->getFieldName($last);
+                $last = $table->getColumnName($fieldName);
                 $cache[$key]['fieldName'] = $fieldName;
                 if ($table->isIdentifier($fieldName)) {
                     $cache[$key]['isIdentifier'] = true;

--- lib/vendor/doctrine/Doctrine/Query.php.orig	2010-10-07 19:09:10.000000000 +0200
+++ lib/vendor/doctrine/Doctrine/Query.php	2010-10-14 00:03:28.000000000 +0200
@@ -494,12 +494,12 @@
                 $parentAlias = $this->getSqlTableAlias($componentAlias . '.' . $parent->getComponentName());
                 $sql[] = $this->_conn->quoteIdentifier($parentAlias) . '.' . $this->_conn->quoteIdentifier($columnName)
                        . ' AS '
-                       . $this->_conn->quoteIdentifier($tableAlias . '__' . $columnName);
+                       . $this->_conn->quoteIdentifier($tableAlias . '__' . $fieldName);
             } else {
-                $columnName = $table->getColumnName($fieldName);
+                $columnName = $table->getColumnName($fieldName); // Really needed?
                 $sql[] = $this->_conn->quoteIdentifier($tableAlias) . '.' . $this->_conn->quoteIdentifier($columnName)
                        . ' AS '
-                       . $this->_conn->quoteIdentifier($tableAlias . '__' . $columnName);
+                       . $this->_conn->quoteIdentifier($tableAlias . '__' . $fieldName);
             }
         }
 
@@ -554,7 +554,7 @@
 
             return $this->_conn->quoteIdentifier($tableAlias . '.' . $name)
                    . ' AS '
-                   . $this->_conn->quoteIdentifier($tableAlias . '__' . $name);
+                   . $this->_conn->quoteIdentifier($tableAlias . '__' . $field);
         }
     }




[DC-331] Wrong SQL output from Doctrine_Expression for pgsql with timestamp with time zone field Created: 06/Dec/09  Updated: 05/Jan/10

Status: Open
Project: Doctrine 1
Component/s: Query
Affects Version/s: 1.2.0-RC1
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Dennis Gearon Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 1
Labels: None
Environment:

This is with Symfony 1.3 alpha (which is pretty good) Can't tell you which Doctrine it comes with



 Description   

(1) pgsql connection
(2) timestamp with time zone column
(3) $doctrine_expression = new Doctrine_Expression('NOW()');
echo $doctrine_expression->getSQL();
gives:
LOCALTIMESTAMP(0)

What I'd like to get access to using that Doctrine construct is the
functionality of 'CURRENT_TIMESTAMP', (has time zone,
stores time in TIMESTAMP WITH TIME ZONE columns in UTC time)

Fortunately, all the databases that I want to use implement
'CURRENT_TIMESTAMP', but not all PDO/Doctrine databases
support that construct, even though it's ANSI SQL standard,
(which ANSI I don't know.)



 Comments   
Comment by Michael Pearson [ 05/Jan/10 ]

I'm also seeing this with Symfony 1.4.1 (w/Doctrine 1.2). Affects timestampable tables (created_at, updated_at).

also, NOW() AT TIME ZONE 'UTC' translates to LOCALTIMESTAMP(0) AT TIME ZONE 'UTC' (via buildSqlQuery).





[DC-280] Add pre/postHydrateResultSet() events Created: 23/Nov/09  Updated: 08/Jun/10

Status: Open
Project: Doctrine 1
Component/s: Connection, Query, Record
Affects Version/s: None
Fix Version/s: None

Type: New Feature Priority: Major
Reporter: Adam Jensen Assignee: Jonathan H. Wage
Resolution: Unresolved Votes: 0
Labels: None


 Description   

Over the last several weeks I've been working on streamlining the access control logic in the application I'm working on, and I realized that Doctrine's event listeners might be able to help. For more detailed background information take a look at http://groups.google.com:80/group/dallasphp/browse_thread/thread/91e3f107cd611adf ...but here's the problem in a nutshell:

Since my application's access control logic is all implemented in PHP rather than in the database (i.e., I can't add access control to my queries as a simple WHERE clause), I'm looking for a way to hook into the query process just after the records have been hydrated and actually modify the result set that gets returned, such that only permissible records show up in the final result set.

Unfortunately, although there are several listener methods that look promising for this, none of them seem to have access to the data being returned. For instance, if I run a custom query via Doctrine_Query::execute(), the postQuery() hook definitely runs ...but it doesn't give me access to anything but the query string itself.

Since, in some instances (e.g., hydration listeners), the Doctrine_Event object is assigned arbitrary data that the listener can modify, I'm wondering if the same thing couldn't be done more universally?



 Comments   
Comment by Jonathan H. Wage [ 24/Nov/09 ]

In Doctrine 1.2 you can create custom hydrators. You can extend the core hydrators to remove the data you want? I think that would work.

Comment by Adam Jensen [ 24/Nov/09 ]

Sure enough, that does the trick!

There are a couple of downsides, though, that might be worth considering in terms of future development:

1. It would be nice to be able to specify constructor arguments for the hydrator, so that collaborators can be injected. In my example, the hydrator needs access to the application's access control list object; currently it's simply retrieving it from a global registry, but it would be nice for testing's sake to be able to inject it instead.
2. It would also be nice to be able to chain multiple hydrators together; that's one reason I was looking at listeners, since they've got that capability already. That approach allows you to keep distinct behavior distinct a lot more easily.

Ultimately, I'd still kind of like to see another listener method available ...say, preHydrateResultSet() and postHydrateResultSet()? I think that would be a more flexible approach, even though the custom hydrator solution works quite well.

Thanks!
Adam

Comment by Jonathan H. Wage [ 24/Nov/09 ]

I like the idea. I'll move this to 1.3. I don't think we're gonna have a 1.3 version but if we do, it'll be there.

Comment by Jonathan H. Wage [ 15/Mar/10 ]

We can include this in a 1.2.x release if you would like to provide a patch and some tests. Thanks, Jon





[DC-254] getLimitSubquery Not Including All Order By Parts Created: 18/Nov/09  Updated: 27/Feb/11

Status: Reopened
Project: Doctrine 1
Component/s: Query
Affects Version/s: 1.2.0-BETA3
Fix Version/s: 1.2.0-RC1

Type: Bug Priority: Major
Reporter: Michael Card Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 1
Labels: None


 Description   

The getLimitSubquery function is only including the first orderby field and ignoring all remaining parts. The function also needs to be changed to include mssql and odbc.

The diff's below provide corrections.

Thanks,

Mike

diff -r Doctrine-1.1.2\lib\Doctrine\Query.php Doctrine-1.1.2-Fixes\lib\Doctrine\Query.php
1367c1367
<         if ($driverName == 'pgsql' || $driverName == 'oracle' || $driverName == 'oci') {
---
>         if ($driverName == 'pgsql' || $driverName == 'oracle' || $driverName == 'oci' || $driverName == 'mssql' || $driverName == 'odbc') {
1371,1387c1371,1392
<                 $part_original = trim($e[0]);
<                 $callback = create_function('$e', 'return trim($e, \'[]`"\');');
<                 $part = trim(implode('.', array_map($callback, explode('.', $part_original))));
< 
<                 if (strpos($part, '.') === false) {
<                     continue;
<                 }
< 
<                 // don't add functions
<                 if (strpos($part, '(') !== false) {
<                     continue;
<                 }
< 
<                 // don't add primarykey column (its already in the select clause)
<                 if ($part !== $primaryKey) {
<                     $subquery .= ', ' . $part_original;
<                 }
---
> 
>                 foreach ($e as $f) {
>                     if ($f == 0 || $f % 2 == 0) {
>                         $part_original = trim($f);
>                         $callback = create_function('$e', 'return trim($e, \'[]`"\');');
>                         $part = trim(implode('.', array_map($callback, explode('.', $part_original))));
> 
>                         if (strpos($part, '.') === false) {
>                             continue;
>                         }
> 
>                         // don't add functions
>                         if (strpos($part, '(') !== false) {
>                             continue;
>                         }
> 
>                         // don't add primarykey column (its already in the select clause)
>                         if ($part !== $primaryKey) {
>                             $subquery .= ', ' . $part_original;
>                         }
>                     }
>                 }



 Comments   
Comment by Michael Card [ 22/Dec/09 ]

Additonal fix for this issue, was missing dblib originally.

Mike


diff -r Doctrine-1.1.2\lib\Doctrine\Query.php Doctrine-1.1.2-Fixes\lib\Doctrine\Query.php
1367c1367
< if ($driverName == 'pgsql' || $driverName == 'oracle' || $driverName == 'oci' || $driverName == 'mssql' || $driverName == 'odbc') {
—
> if ($driverName == 'pgsql' || $driverName == 'oracle' || $driverName == 'oci' || $driverName == 'mssql' || $driverName == 'odbc' || $driverName == 'dblib') {

Comment by Andrej Pavlovic [ 27/Feb/11 ]

Come on guys, please include this fix into 1.2.4...





[DC-49] Wrong query parameter order when using limit() on PostgreSQL Created: 22/Sep/09  Updated: 26/Jul/10

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

Type: Bug Priority: Major
Reporter: Nicholas Kasyanov Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 2
Labels: None
Environment:

latest doctrine 1.1 revision, linux x64, PostgeSQL 8.4, php 5.2.10


Attachments: File Doctrine_Query_Abstract.diff     File PostgresParamsBugTestCase.php    

 Description   

Trying to add limit() to complex query with many where's and some joins. In pgsql it causes nested limit subquery and in some cases parameter order (when limit subquery exists params array just merges with itself, i. e. (1,2) becomes (1,2,1,2)) makes result query wrong, i. e. params array order doesn't correspond placeholders order in query.

With named query parameters all ok.

Testcase attached.



 Comments   
Comment by Jonathan H. Wage [ 24/Sep/09 ]

here is the working test case:

class Doctrine_Ticket_DC49_TestCase extends Doctrine_UnitTestCase 
{
        public static $query;
        public static $params;

        public function init()
        {
                $this->dbh = new Doctrine_Adapter_Mock('pgsql');
                $this->conn = Doctrine_Manager::getInstance()->openConnection($this->dbh);
                $this->conn->addListener(new PPB_QueryListener());
        }
        
        public function prepareTables()
        {
                $this->tables = array('PPB_Section', 'PPB_Post', 'PPB_PostVote');
                
                parent::prepareTables();
        }
        
        public function testBug()
        {
                $q = Doctrine_Query::create()->from('PPB_Post p')
                                             ->where('p.section_id = ?', 1)
                                             ->addWhere('p.id = ?', 15)
                                             ->leftJoin('p.Votes v WITH v.user_id = ?', 10)
                                             ->limit(10);
                                             
                $q->execute();
                
                $this->assertEqual(self::$query, "SELECT p.id AS p__id, p.section_id AS p__section_id, p2.id AS p2__id, p2.post_id AS p2__post_id, p2.user_id AS p2__user_id FROM p_p_b__post p LEFT JOIN p_p_b__post_vote p2 ON p.id = p2.post_id AND (p2.user_id = ?) WHERE p.id IN (SELECT doctrine_subquery_alias.id FROM (SELECT DISTINCT p3.id FROM p_p_b__post p3 LEFT JOIN p_p_b__post_vote p4 ON p3.id = p4.post_id AND (p4.user_id = ?) WHERE p3.section_id = ? AND p3.id = ? LIMIT 10) AS doctrine_subquery_alias) AND (p.section_id = ? AND p.id = ?)");
                
                $this->assertEqual(implode(",", self::$params), '10,10,1,15,1,15'); // correct order of params
        }
}

class PPB_Post extends Doctrine_Record
{
        public function setTableDefinition()
        {
                $this->hasColumn('section_id', 'integer');
        }
        
        public function setUp()
        {
                $this->hasOne('PPB_Section', array('local' => 'section_id',
                                                   'foreign' => 'id'));
                                                   
                $this->hasMany('PPB_PostVote as Votes', array('local' => 'id',
                                                              'foreign' => 'post_id'));
        }
}

class PPB_Section extends Doctrine_Record
{
        public function setTableDefinition()
        {
        }
}

class PPB_PostVote extends Doctrine_Record
{
        public function setTableDefinition()
        {
                $this->hasColumn('post_id', 'integer');
                $this->hasColumn('user_id', 'integer');
        }
}

class PPB_QueryListener extends Doctrine_EventListener
{
        public function postStmtExecute(Doctrine_Event $event)
        {
                //var_dump($event->getQuery());
                //var_dump($event->getParams());
                
                Doctrine_Ticket_DC49_TestCase::$query = $event->getQuery();
                Doctrine_Ticket_DC49_TestCase::$params = $event->getParams();
        }
}
Comment by Daniel Bojdo [ 26/Jul/10 ]

I fixed this bug. Patch in attachment. I hope it's clear enough.





[DC-1016] Set method in update query ignores 'false' if passed as boolean Created: 05/Jul/11  Updated: 17/Apr/14

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

Type: Bug Priority: Minor
Reporter: Paweł Barański Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Symfony 1.4.11 , Ubuntu 11, PHP 5.3



 Description   

I had to define this function:

public function deactivate($segment_id)

{ $query = $this->createQuery() ->update('Segment s') ->set('s.is_active ', false) //not working // ->set('s.is_active ', (int)false) //works ok // ->set('s.is_active ', true) //works ok ->where('s.id = ?', $segment_id); // var_dump($query->getSqlQuery());die; return $query->execute(); }

Problem is that when setting a column using boolean false you get invalid SQL query like this:
UPDATE segment SET is_active = WHERE (id = ?)

Workaround is to do it like this: set('s.is_active ', (int)false) , but since setting the same column with boolean true works, false should work too.






[DC-1003] _processWhereIn does not allow the use of named query parameters Created: 05/May/11  Updated: 17/Apr/14

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

Type: Improvement Priority: Minor
Reporter: alex pilon Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 0
Labels: None
Environment:

karmic, php 5.2.10, apache2


Attachments: Text File _processWhereIn-named-parameter-v2.patch     Text File _processWhereIn-named-parameter.patch    

 Description   

When writing a query such as

$query = $query->where('entity.myValue = :value', array(':value'=>5));

you are unable to then

$query = $query->whereIn('entity.otherValue', array(':otherValues'=>array(1,2,3)));

Doctrine complains that you may not mix positional and named query parameters.

The attached patch fixes this by checking if the key of the passed in parameter is non numeric and if so setting the "value" of the parameter place holder to the value of the key.



 Comments   
Comment by alex pilon [ 05/May/11 ]

I discovered an issue with the above patch. I am working on a better version.

Comment by alex pilon [ 06/May/11 ]

Here is a second version.. it is a little bit sloppy. Is there a resource I can find on here that will help me to improve code quality/unit test this?





[DC-964] Alias name error when using interval(MySQL) Created: 03/Feb/11  Updated: 03/Feb/11

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

Type: Bug Priority: Minor
Reporter: Tibor Erdész Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 0
Labels: None
Environment:

PHP Version 5.3.3-1ubuntu9.3; Apache/2.2.16 (Ubuntu); MySQL: 5.1.41; Symfony 1.4; Doctrine 1.2.3 (Revision: 7490)



 Description   

I'm using Doctrine with Symfony and i have some troubles when i'm using the mysql command: interval. Here's a detailed explanation:
Part of the schema:

User:
  columns:
    name: { type: string(255), notnull: true }
    nick: { type: string(255), notnull: true, unique: true }
    email: { type: string(255), notnull: true, unique: true }
    password: { type: string(40), notnull: true }

VirtualConferenceRoom:
  columns:
    adminId: { type: integer, notnull: true }
    name: { type: string(255), notnull: true }
    startDate: { type: timestamp, notnull: true }
    duration: { type: integer, notnull: true }
  relations:
    Admin:
      class: User
      foreign: id
      local: adminId

VirtualConferenceRoomUser:
  columns:
    virtualConferenceRoomId: { type: integer, notnull: true }
    userId: { type: integer, notnull: true }
  relations:
    VirtualConferenceRoom:
      class: VirtualConferenceRoom
      foreign: id
      local: virtualConferenceRoomId
    User:
      class: User
      foreign: id
      local: userId

I've generated my model with symfony's built in command(symfony doctrine:build --model --sql)
When I try to get the conference rooms which are in progress at the moment I try it like this:

// In the project/lib/model/doctrine/VirtualConferenceRoomTable.class.php which is extends from Doctrine_Table
public function getRoomsInProgressByUserId($userId)
{
    return Doctrine_Query::create()
                ->select('vcr.*')
                ->from('VirtualConferenceRoom vcr')
                ->innerJoin('vcr.VirtualConferenceRoomUser vcru')
                ->where('(vcr.StartDate < now()) and (vcr.StartDate + interval vcr.Duration minute > now()) and vcru.UserId = ?', array($userId))
                ->execute();
}

When I try to run my app I get an error message: "SQLSTATE[42S22]: Column not found: 1054 Unknown column 'vcr.Duration' in 'where clause'"
The generated sql command is:

" SELECT v.id AS v__id, v.userid AS v__userid, v.name AS v__name, v.startdate AS v__startdate, v.duration AS v__duration FROM virtual_conference_room v INNER JOIN virtual_conference_room_user v2 ON v.id = v2.virtualconferenceroomid WHERE (v.startdate < NOW() AND v.startdate + interval vcr.Duration minute > NOW() AND v2.userid = ?) - (2)"

The problem is the vcr.Duration section. If I change my function from:

->where('(vcr.StartDate < now()) and (vcr.StartDate + interval vcr.Duration minute > now()) and vcru.UserId = ?', array($userId))

to:

->where('(vcr.StartDate < now()) and (vcr.StartDate + interval v.Duration minute > now()) and vcru.UserId = ?', array($userId))

it works perfectly.

I hope my report is useful and clear and i can help you to improve this great ORM
Best wishes: Tibor Erdész <erdeszt@gmail.com>



 Comments   
Comment by Tibor Erdész [ 03/Feb/11 ]

Just figured out that I can use


notations and I fixed my report.





[DC-943] Memory leaks in find* magic methods owing to failure to free queries Created: 03/Dec/10  Updated: 03/Dec/10

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

Type: Bug Priority: Minor
Reporter: Garpini Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 0
Labels: None

Attachments: Text File doctrine_query_patch.patch    

 Description   

The following methods on Doctrine_Table create Doctrine_Query objects that are never free()d:
findAll()
findBySql()
findByDql()
findBy()
findOneBy()



 Comments   
Comment by Garpini [ 03/Dec/10 ]

The attachment contains a patch that addresses the memory leak by free()ing the query objects generated before returning the results of the query.





[DC-896] MySQL Limit implementation leads to incorrect results if the original where clause contains an "OR" operator Created: 20/Oct/10  Updated: 20/Oct/10

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

Type: Bug Priority: Minor
Reporter: James Pakele Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Doctrine + MySQL



 Description   

If adding the Where In clause for the subquery "limit" implementation, the original where clause needs to be isolated with parenthesis. In my sample where clause the first character is in fact an opening parenthesis and the last character is in fact a closing parenthesis, however, there are a bunch of conditions and parenthesis between them as well that may contain "OR" operators. When this is combined with the Where In clause generated by the Pager tool's "limit" implementation for MySQL it leads to incorrect results.

Here is my "Before" Where condition:

WHERE c.id IN ('25', '31', '6', '28', '30', '16', '32', '26', '22', '14') AND (a2.data_item_id = ? AND a2.value = ?) AND (a3.data_item_id = ? AND a3.value = ?) OR (a4.data_item_id = ? AND a4.value = ?) AND (a5.data_item_id = ? AND a5.value = ?)

And the "After" Where condition
WHERE c.id IN ('25', '31', '6', '28', '30', '16', '32', '26', '22', '14') AND ((a2.data_item_id = ? AND a2.value = ?) AND (a3.data_item_id = ? AND a3.value = ?) OR (a4.data_item_id = ? AND a4.value = ?) AND (a5.data_item_id = ? AND a5.value = ?))

Fix:

Line 1305 in Query.php:
$where = ($where == '' || (substr($where, 0, 1) === '(' && substr($where, -1) === ')'))
? $where : '(' . $where . ')';

I ended up just adding the following just after the above statement in the code:
if (!empty($limitSubquerySql) && !empty($where))

{ $where = '(' . $where . ')'; }




[DC-855] Notify via Exception when a feature is specifically not supported for compatability Created: 02/Sep/10  Updated: 02/Sep/10

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

Type: Improvement Priority: Minor
Reporter: Jacob Mather Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 0
Labels: None
Environment:

N/A



 Description   

For situations like with http://www.doctrine-project.org/jira/browse/DC-646 would it be possible to look at throwing an exception with an explanation of why it does not work, rather than simply let the query fail because of intentionally incorrect query generation?

Granted, the fix is relatively simple (just select and delete the results, instead), but if I don't know why the wrong query is being made, I don't know to change my approach rather than simply assume I'm doing something wrong.






[DC-852] CLONE -Fix returned type value : SQL integers to PHP integers when getting a value from the database. Created: 01/Sep/10  Updated: 17/Apr/14

Status: Open
Project: Doctrine 1
Component/s: Attributes, Data Fixtures, Native SQL, Query, Record
Affects Version/s: 1.2.3
Fix Version/s: None

Type: Improvement Priority: Minor
Reporter: Enrico Stahn Assignee: Jonathan H. Wage
Resolution: Unresolved Votes: 0
Labels: None


 Description   

Hi Jon,

I have a request for you to improve Doctrine. When declaring a column as an integer, it seems that Doctrine returns a string when getting the value of that column. For example, if I have a "status" column, which can take 0, 1 or 2 as its value, Doctrine will return these values as string, which is not really logical and returning the good PHP type is the goal of an ORM. I'm fond of the triple equal symbol to test a value and I did not understand why this did not work at start :

// in my myModel class
class myModel extends Doctrine_Record
{
const STATUS_VALIDATED = 1;

public function isValidated()

{ return self::STATUS_VALIDATED === $this->getStatus(); }

}

That's why getStatus() gives me a string instead of an integer, whereas the column is declared as an integer in my schema. I'm forced to cast myself the returned value of getStatus() in my model.



 Comments   
Comment by Enrico Stahn [ 01/Sep/10 ]

Proposal for a solution: http://github.com/estahn/doctrine1/compare/master...DC-852

Comment by Jonathan H. Wage [ 01/Sep/10 ]

So if you enable this attribute you live with the fact that casting a string to an integer that is longer than php max integer will give weird results?

Comment by Enrico Stahn [ 02/Sep/10 ]

i'm far from happy with this solution. if an integer that is greater than php max int gets casted it will be casted into a value of type double. if you enable that attribute only values that could successfully casted into an integer will be casted otherwise an exception will be thrown.





[DC-811] calculateQueryCacheHash causes a fatal error when used in combination with output buffering Created: 06/Aug/10  Updated: 24/Aug/10

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

Type: Bug Priority: Minor
Reporter: Carl Alexander Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 0
Labels: None

Attachments: Text File query_abstract.patch    

 Description   

When using output buffering in combination with a doctrine caching driver. var_export causes a fatal error as described here:

http://marc.info/?l=phpdoc&m=114122422804066
http://php.net/manual/en/function.var-export.php

Here is the proposed patch.



 Comments   
Comment by Jonathan H. Wage [ 24/Aug/10 ]

This seems to break the test suite in several places so it cannot be applied. If you can provide a patch that passes the test suite we can discuss applying the patch.





[DC-796] Correct way to count all files from a table ? Created: 22/Jul/10  Updated: 22/Jul/10

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

Type: Improvement Priority: Minor
Reporter: Pablo Mateos Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Debian Lenny / MySQL 5.1



 Description   

If I execute this to get the amount of records in a table I get the correct result, but it takes too long and consumes a lot of resources as if I get all the fields from table:

Doctrine::getTable('Recurso')>findAll()>count();

Am I doing it wrong?, The only other option I've found is to use DSQL and execute a query like "SELECT count from recurso".

Thank you in advance.

Pablo.






[DC-771] fetchOne return boolean Created: 29/Jun/10  Updated: 29/Jun/10

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

Type: Bug Priority: Minor
Reporter: Thomas Tourlourat - Armetiz Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 0
Labels: None


 Description   

Doctrine_Query::fetchOne could return false..

It a "get" function, so the function should return "null" or an "object", but a boolean isn't a good value..






[DC-354] Using BETWEEN operator with MySQL fails Created: 11/Dec/09  Updated: 08/Jun/10

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

Type: Bug Priority: Minor
Reporter: Matt McKeon Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 0
Labels: None
Environment:

PHP 5.2.10
MySQL 5.1.37



 Description   

When running this bit of code the query fails with the error "Unknown aggregate alias: interval", and calling $q->getSql() doesn't return anything.

{{$q = Doctrine_Query::create()
...
->where('u.birthday BETWEEN date_sub(now(), interval 7 day) AND NOW()');}}

I've tried different variations in the where() clause with no luck. I am using all the other MySQL functions in another query here:
$q->where('date(birthday) = date_sub(curdate(), interval 1 day)')
so I don't believe that they are the problem.

I would expect the BETWEEN operator to be recognized and work appropriately.



 Comments   
Comment by Jonathan H. Wage [ 08/Jun/10 ]

BETWEEN is mysql specific, no?





Generated at Fri Aug 29 20:21:05 UTC 2014 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.