Doctrine 1

Query with a leftJoin() + where(NOT IN) + limit() generate wrong SQL alias in the NOT IN part

Details

  • Type: Bug Bug
  • Status: Resolved Resolved
  • Priority: Critical Critical
  • Resolution: Fixed
  • Affects Version/s: 1.2.2
  • Fix Version/s: 1.2.4
  • Component/s: Query
  • Labels:
    None
  • Environment:

Description

I have a simple case with 3 Classes. Contact, Phone and Email. A Contact can many many phones, but only one email.

When doing this simple query:
$query = Doctrine_Query::create()->from('Contact c');
$query->leftJoin('c.Phones p ON c.id = p.contact_id');
$query->where('c.id NOT IN (SELECT Email.contact_id FROM Email)');
$query->execute();
Every thing went fine.

But when adding a limit() condition like:
$query->limit(20);
$query->execute();

The SQL generated query is not valid. There is a problem with the alias used in the NOT IN subquery. This query is generated like this:
WHERE c2.id NOT IN (SELECT e2.contact_id AS e2__contact_id FROM email e)
There is a mix between alias e2 and e

I have been trying to debug, but I didn't understand what was going wrong. The problem seems to happend in the class Doctrine_Query between line 1486 and 1554, but this part is obscur to me.

I attach to this ticket a valid TestCase

Thanks for support

Activity

Ascending order - Click to sort in descending order
Hide
Permalink
David Jeanmonod added a comment -

TEXT VERSION OF THE TEST CASE

<?php

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:@localhost/test_doctrine');
echo "Connection is set up\n";

class Contact extends Doctrine_Record {
public function setTableDefinition()

{ $this->setTableName('contact'); $this->hasColumn('name', 'string', 300, array('type' => 'string', 'length' => 300)); }

public function setUp()

{ $this->hasMany('Phone as Phones', array('local' => 'id', 'foreign' => 'contact_id')); $this->hasOne('Email as Email', array('local' => 'id', 'foreign' => 'contact_id')); }

}
class Phone extends Doctrine_Record {
public function setTableDefinition()

{ $this->setTableName('phone'); $this->hasColumn('contact_id', 'integer', null, array('type' => 'integer')); }

public function setUp()

{ $this->hasOne('Contact', array('local' => 'contact_id', 'foreign' => 'id', 'onDelete' => 'CASCADE')); }

}
class Email extends Doctrine_Record {
public function setTableDefinition()

{ $this->setTableName('email'); $this->hasColumn('contact_id', 'integer', null, array('type' => 'integer')); }

public function setUp()

{ $this->hasOne('Contact', array('local' => 'contact_id', 'foreign' => 'id')); }

}
echo "Classes Contact, Phone and Email are defines\n";

try

{Doctrine::dropDatabases();}

catch(Exception $e){} // Drop if exist
Doctrine::createDatabases();
Doctrine::createTablesFromArray(array('Contact', 'Phone', 'Email'));
echo "Databases tables are create\n";

$query = Doctrine_Query::create()->from('Contact c');
$query->leftJoin('c.Phones p ON c.id = p.contact_id');
$query->where('c.id NOT IN (SELECT Email.contact_id FROM Email)');
$query->limit(20);
try {
$query->execute();
echo "TEST: Doctrine LEFTJOIN + NOT IN + LIMIT is OK\n";
}
catch (Exception $e){
echo "TEST: Doctrine LEFTJOIN + NOTIN + LIMIT is NOT working\n Alias in the NOT IN part are wrong, we get e2 and e\n\nDetail of the error:\n ", $e->getMessage(), "\n";
}

Show
David Jeanmonod added a comment - TEXT VERSION OF THE TEST CASE <?php 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:@localhost/test_doctrine'); echo "Connection is set up\n"; class Contact extends Doctrine_Record { public function setTableDefinition() { $this->setTableName('contact'); $this->hasColumn('name', 'string', 300, array('type' => 'string', 'length' => 300)); } public function setUp() { $this->hasMany('Phone as Phones', array('local' => 'id', 'foreign' => 'contact_id')); $this->hasOne('Email as Email', array('local' => 'id', 'foreign' => 'contact_id')); } } class Phone extends Doctrine_Record { public function setTableDefinition() { $this->setTableName('phone'); $this->hasColumn('contact_id', 'integer', null, array('type' => 'integer')); } public function setUp() { $this->hasOne('Contact', array('local' => 'contact_id', 'foreign' => 'id', 'onDelete' => 'CASCADE')); } } class Email extends Doctrine_Record { public function setTableDefinition() { $this->setTableName('email'); $this->hasColumn('contact_id', 'integer', null, array('type' => 'integer')); } public function setUp() { $this->hasOne('Contact', array('local' => 'contact_id', 'foreign' => 'id')); } } echo "Classes Contact, Phone and Email are defines\n"; try {Doctrine::dropDatabases();} catch(Exception $e){} // Drop if exist Doctrine::createDatabases(); Doctrine::createTablesFromArray(array('Contact', 'Phone', 'Email')); echo "Databases tables are create\n"; $query = Doctrine_Query::create()->from('Contact c'); $query->leftJoin('c.Phones p ON c.id = p.contact_id'); $query->where('c.id NOT IN (SELECT Email.contact_id FROM Email)'); $query->limit(20); try { $query->execute(); echo "TEST: Doctrine LEFTJOIN + NOT IN + LIMIT is OK\n"; } catch (Exception $e){ echo "TEST: Doctrine LEFTJOIN + NOTIN + LIMIT is NOT working\n Alias in the NOT IN part are wrong, we get e2 and e\n\nDetail of the error:\n ", $e->getMessage(), "\n"; }
Hide
Permalink
will ferrer added a comment -

Hi David

I had a problem with subqueries in the which I worked around by including real sql in the subquery with a prefix of SQL:.

My bug occurred trying to run this code:

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

However this code works fine for me now (though make sure you have the latest svn build of doctrine because there were some patches that helped this work):

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

Notice the use of SQL: in the subquery.

Here is the bug:

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

Also worth noting that I am getting that sql by building another query and then running the getSqlQuery method to return the sql I am then using in the other query.

Hope that helps.

Will Ferrer

Show
will ferrer added a comment - Hi David I had a problem with subqueries in the which I worked around by including real sql in the subquery with a prefix of SQL:. My bug occurred trying to run this code: $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); However this code works fine for me now (though make sure you have the latest svn build of doctrine because there were some patches that helped this work): $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'); Notice the use of SQL: in the subquery. Here is the bug: http://www.doctrine-project.org/jira/browse/DC-692 Also worth noting that I am getting that sql by building another query and then running the getSqlQuery method to return the sql I am then using in the other query. Hope that helps. Will Ferrer

People

  • Assignee:
    Guilherme Blanco
    Reporter:
    David Jeanmonod
Vote (1)
Watch (2)

Dates

  • Created:
    Updated:
    Resolved: