找到两个Doctrine集合的交集

Question

I am using Doctrine and have the following SQL tables:

• entity: id (pk)
• x_series: id (pk), entity_id (fk references entity.id), name (string), position (int)
• y_series: id (pk), entity_id (fk references entity.id), name (string), position (int)
• x_y_intersect: x_series_id/y_series_id (composite pk and fk to x/y_series), name (string)

Given ['entityId'=>123, 'xPosition'=>3, 'yPosition'=>5], I wish to retreive the intercecting object.

One option is to do so using SQL (or DQL) (can be optimized, but written as is for clarity):

SELECT xyi.*
FROM entities e
INNER JOIN x_series xs ON xs.entity_id=e.id
INNER JOIN y_series ys ON ys.entity_id=e.id
INNER JOIN x_y_intersect xyi ON xyi.x_series_id=xs.id AND xyi.y_series_id=ys.id
WHERE e.id=:entityId AND xs.position=:xPosition AND ys.position=:yPosition;

EDIT. Appears that DQL cannot be used as while I didn't mention because I incorrectly felt unimportant, entities is an inherited class and all other inherited classes are left joined.

As an alternative solution, is there a good OOP approach.

$entity=$this->read($id);
$xSeries=$entity->getXseries();
$ySeries=$entity->getYseries();
$xNode=$xSeries->offsetGet($xPosition);
$yNode=$ySeries->offsetGet($yPosition);

//How should this be accomplished?
$intersect=$this->getXYnodeIntersect($entity, $xNode, $yNode);