phpsymfonydoctrine-ormmany-to-many

Query m:n relationship for n = null - returns Lexer error


Building a query to select M entities that have no corresponding N entities returns an error with the following query:

return $this->getEntityManager()
                ->createQuery(
                        'SELECT p FROM VolVolBundle:Opportunity p '
                        . 'LEFT JOIN VolVolBundle:Volunteer v'
                        . 'WHERE v.id is null   '
                        . 'ORDER BY p.organization ASC'
                );

returns the error:

Expected Doctrine\ORM\Query\Lexer::T_WITH, got 'v'

Yet the following SQL statement returns a non-empty resultset:

select o.id from opportunity o
left outer join opportunity_volunteer ov on o.id = ov.opportunity_id
left outer join volunteer v on ov.volunteer_id = v.id
where ov.id is null;

where opportunity_volunteer is the linking table

Opportunity entity relationship definition

/**
 * @var \Doctrine\Common\Collections\Collection
 *
 * @ORM\ManyToMany(targetEntity="Volunteer", inversedBy="opportunities", cascade={"persist"})
 * @ORM\JoinTable(name="opportunity_volunteer",
 *      joinColumns={@ORM\JoinColumn(name="opportunity_id", referencedColumnName="id")},
 *      inverseJoinColumns={@ORM\JoinColumn(name="volunteer_id", referencedColumnName="id")}
 *      ))
 */
protected $volunteers;

public function addVolunteer(\Vol\VolBundle\Entity\Volunteer $volunteer) {
    $volunteer->addOpportunity($this);
    array_push($volunteers, $volunteer);
}

public function getVolunteers() {
    return $this->volunteers;
}

Volunteer entity relationship definition

/**
 * @var \Doctrine\Common\Collections\Collection
 *
 * @ORM\ManyToMany(targetEntity="Opportunity", mappedBy="volunteers")
 */
protected $opportunities;

public function addOpportunity(\Vol\VolBundle\Entity\Opportunity $opportunity) {
    array_push($opportunities, $opportunity);
}

public function getOpportunities() {
    return $this->opportunities;
}

Solution

  • The eventual solution was to create a 1:n:1 relationship, eliminating the "invisible" entity in the middle. This enabled adding fields to the relationship. So in addition to assuring each line ended with a space character before concatenation the query needed to be rewritten. It now finds the null results as desired:

        return $this->getEntityManager()
            ->createQuery(
                    'SELECT p FROM VolVolBundle:Opportunity p '
                    . 'LEFT JOIN VolVolBundle:OpportunityVolunteerEmail e '
                    . 'with p = e.opportunity '
                    . 'where e.opportunity is null'
                    . 'ORDER BY p.organization ASC'
            )->getResult();