I have a database defined as (simple recursive many-to-many relation:
WorkItem:
columns:
type: { type: enum, length: 10, values: [Product, UserStory,
TestSuite, TestCase, Bug] }
WorkItemRelation:
columns:
parent_id: { type: integer, notnull: true }
child_id: { type: integer, notnull: true }
relations:
Parent: { class: WorkItem, local: parent_id, foreign: id,
foreignAlias: Children }
Child: { class: WorkItem, local: child_id, foreign: id,
foreignAlias: Parents }
And I need to make a query that "selects from WorkItem every record of
certain type that does not have a parent of certain type" and that can
be written in SQL like:
SELECT wi.* FROM work_item wi WHERE wi.id NOT IN
(SELECT r.child_id id FROM work_item wi INNER JOIN work_item_relation
r ON r.parent_id = wi.id WHERE wi.TYPE IN ('ParentType'))
AND wi.TYPE IN ('RecordType')
I simply cannot write this expression with doctrine. My best try was:
$subQ = Doctrine_Query::create()
->select('WorkItem.id')
->from('WorkItem, WorkItem.Children')
->whereIn('WorkItem.type', array('ParentType'));
which results in "SELECT w.id AS w__id FROM work_item w LEFT JOIN
work_item_relation w2 ON w.id = w2.parent_id WHERE (w.type IN (?))"
and finally
$q = Doctrine_Query::create()
->from('WorkItem')
->andWhere('WorkItem.id NOT IN (' . $subQ->getDql() .
')')
->andWhereIn('WorkItem.type', array('RecordType'));
which generates a malformed SQL query "SELECT w.id AS w__id, w.name AS
w__name, w.type AS w__type, w.creator_id AS w__creator_id, w.owner_id
AS w__owner_id, w.created_at AS w__created_at, w.updated_at AS
w__updated_at, w.deleted_at AS w__deleted_at, w.version AS w__version,
w.slug AS w__slug FROM work_item w WHERE (w.id NOT IN (SELECT w.id AS
w__id FROM LEFT JOIN work_item_relation w2 ON w.id = w2.parent_id
WHERE (w.type IN (?))) AND w.type IN (?))"
Now questions:
1) how do I get an INNER JOIN instead of LEFT JOIN in $subQ?
2) where has the missing table name gone in the FROM LEFT JOIN part of
the generated SQL for $q?
3) Is there a way to nest queries not using the getDql method?
--
If you want to report a vulnerability issue on symfony, please send it to
security at symfony-project.com
You received this message because you are subscribed to the Google
Groups "symfony users" 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.com/group/symfony-users?hl=en
