CODEWITHSUNDEEP
symfonydoctrine-ormrepositoryentity
user2016626
user2016626

Reputation: 1

Symfony2 & Doctrine2: Custom Entity Repository Query to Retrieve Single Result if Joined Table has No Associated Rows

I have two entities/tables, one for Counties and one for Cities. A particular county has a OneToMany relationship to cities and I am trying to make a custom query in the Entity Repository to query a County based on it's ID and return that County and the Cities corresponding to it.

My query currently seems to work great if the county has cities assigned, but if it does not have any cities yet, Doctrine gives me a "Unable to find County entity. " Exception.

I believe there is a logical error in my query, but I am having a hard time re-writing it to return solely the County by ID if no cities are associated with it.

My Query:

class CountyRepository extends EntityRepository
{
  public function findOneByIdJoinedToCities($id)
  {
    $qb = $this->createQueryBuilder('c')
               ->addSelect('p')
               ->join('c.cities', 'p')
               ->where('p.county = :id')
               ->setParameter('id', $id)
               ;

    $query = $qb->getQuery();

    try {
        return $query->getSingleResult();
    } catch (\Doctrine\ORM\NoResultException $e){
        return null;
    }
  }
}

How could I change the above code to still give back a single result for County if no Cities have been assigned to it yet?

Thanks for the help!

Upvotes: 0

Views: 4079

Answers (1)

Lighthart
Lighthart

Reputation: 3656

Basic SQL question: use a left join. eg:

    $qb = $this->createQueryBuilder('c')
               ->addSelect('p')
               ->leftJoin('c.cities', 'p')
//               ^^^^^^^^
               ->where('p.county = :id')
               ->setParameter('id', $id)
               ;

Upvotes: 1

Related Questions