Details
-
Type:
Bug
-
Status:
Open
-
Priority:
Major
-
Resolution: Unresolved
-
Affects Version/s: 1.2.2
-
Fix Version/s: None
-
Component/s: Query
-
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
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:
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