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