#1523: Table alias problem with pagination system and EXISTS query part
-----------------------------------+----------------------------------------
Reporter: mm | Owner: romanb
Type: defect | Status: new
Priority: critical | Milestone:
Component: Query/Hydration | Version: 1.0.2
Keywords: EXISTS, pager, alias | Has_test: 0
Mystatus: Pending Core Response | Has_patch: 0
-----------------------------------+----------------------------------------
'''Introduction'''
System is working on PHP 5.2.5 and Postgresql db 8.3.3. Below is a simple
test query to show how the problem looks like, additionally Doctrine_Pager
is also in use. Defect is concentrated around EXISTS part and making
correct aliases in subquery when we re using pager (now they re same as
outer query and this is the problem).
'''Query''' in code
{{{
$query = Doctrine_Query::create()
->select('t.*')
->from('Tow t')
->leftJoin('t.Zam zp')
->where('EXISTS (SELECT tt.id FROM Tow tt WHERE tt.removed = \'f\'
AND tt.id = t.id)')
->orderby('t.name ASC');
}}}
'''Actual''' sql builded
{{{
SELECT "t"."id" AS "t__id", "t"."code" AS "t__code", "t"."name" AS
"t__name"
FROM "tow" "t"
LEFT JOIN "zam" "z" ON "t"."id" = "z"."tow_id"
WHERE "t"."id"
IN (SELECT "doctrine_subquery_alias"."id" FROM
(
SELECT DISTINCT "t2"."id", "t2"."name"
FROM "tow" "t2"
LEFT JOIN "zam" "z2" ON "t2"."id" = "z2"."tow_id" WHERE
EXISTS (
SELECT "t2"."id" AS "t2__id" FROM "tow" "t2" WHERE
("t2"."removed" = 'f' AND "t2"."id" = "t2"."id"))
ORDER BY "t2"."name" ASC LIMIT 30
) AS doctrine_subquery_alias) AND
EXISTS (SELECT "t2"."id" AS "t2__id" FROM "tow" "t2" WHERE ("t2"."removed"
= 'f' AND "t2"."id" = "t"."id"))
ORDER BY "t"."name" ASC
}}}
'''Expected behaviour''' and sql code
Every alias in EXISTS part should be changed to make build query right,
now they re out of control. Probably reason of this is that exists part is
parsed as subquery, copy of alias array exists in new object (there is no
link), parent object query doesnt know the about changes in aliases.
{{{
SELECT "t"."id" AS "t__id", "t"."code" AS "t__code", "t"."name" AS
"t__name"
FROM "tow" "t"
LEFT JOIN "zam" "z" ON "t"."id" = "z"."tow_id"
WHERE "t"."id"
IN (SELECT "doctrine_subquery_alias"."id" FROM
(
SELECT DISTINCT "t2"."id", "t2"."name"
FROM "tow" "t2"
LEFT JOIN "zam" "z2" ON "t2"."id" = "z2"."tow_id" WHERE
EXISTS (
SELECT "t3"."id" AS "t3__id" FROM "tow" "t3" WHERE
("t3"."removed" = 'f' AND "t3"."id" = "t2"."id"))
ORDER BY "t2"."name" ASC LIMIT 30
) AS doctrine_subquery_alias) AND
EXISTS (SELECT "t4"."id" AS "t4__id" FROM "tow" "t4" WHERE ("t4"."removed"
= 'f' AND "t4"."id" = "t"."id"))
ORDER BY "t"."name" ASC
}}}
This is a possible solution (alias setting) only, but illustrate the
problem.
--
Ticket URL: <http://trac.doctrine-project.org/ticket/1523>
Doctrine <http://www.phpdoctrine.org>
PHP Doctrine Object Relational Mapper
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"doctrine-svn" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.co.uk/group/doctrine-svn?hl=en-GB
-~----------~----~----~----~------~----~------~--~---
