title: Doctrine 2: fetch all records with or without match records in a one to many association in single table inheritance
body: <p>I am trying to find the total count of listings in each categories base on listing location</p>
  
  <p>What I am trying to do is if a user from United States visits the site only records from that location are shown. Also the category widget on the sidebar should take into account the total number of listing under each category in that location. If the visitor clicks on Chicago, The widget should recalculate to reflect the location 'Chicago'</p>
  
  <p>with the code below</p>
  
  <pre><code>    public function findCategoriesWithTotalListing($location)
  {
      $builder = $this-&gt;_em-&gt;createQueryBuilder();
  
      $builder-&gt;select('c, count(l.id) AS num')
         -&gt;from('Bundle\AdvertBundle\Entity\Category', 'c')
         -&gt;leftJoin('c.listings','l')
         -&gt;leftJoin('l.country','co')
         -&gt;leftJoin('l.state','st')
         -&gt;leftJoin('l.city','ci');
  
      $builder-&gt;groupBy('c.id');
  
      $result = $builder-&gt;getQuery()-&gt;getArrayResult();
      print_r($result);
      exit;
  }
  </code></pre>
  
  <p>I am able to retrieve all categories with total count of each listing without the location param.</p>
  
  <p>But if I introduce the location param, only those categories that have listings in that location are retrieved.</p>
  
  <pre><code>    public function findCategoriesWithTotalListing($location)
  {
      $builder = $this-&gt;_em-&gt;createQueryBuilder();
  
      $builder-&gt;select('c, count(l.id) AS num')
         -&gt;from('Bundle\AdvertBundle\Entity\Category', 'c')
         -&gt;leftJoin('c.listings','l')
         -&gt;leftJoin('l.country','co')
         -&gt;leftJoin('l.state','st')
         -&gt;leftJoin('l.city','ci');
  
      $orx = $builder-&gt;expr()-&gt;orX();
      $orx-&gt;add($builder-&gt;expr()-&gt;like("co.slug", ':location'));
      $orx-&gt;add($builder-&gt;expr()-&gt;like("st.slug", ':location'));
      $orx-&gt;add($builder-&gt;expr()-&gt;like("ci.slug", ':location'));
  
      $builder-&gt;andWhere($orx);
      $builder-&gt;groupBy('c.id');
      $builder-&gt;setParameter('location',$location);
  
      $result = $builder-&gt;getQuery()-&gt;getArrayResult();
      print_r($result);
      exit;
  }
  </code></pre>
  
  <p>I want all categories </p>
  
  <p>What am I possibly doing wrong here</p>
  
  <p>Thanks</p>
  
  <p>Entities below</p>
  
  <p>Category Entity</p>
  
  <pre><code>&lt;?php
  
  namespace Bundle\AdvertBundle\Entity;
  
  use Bundle\FrameworkBundle\Entity\BaseEntity;
  use Bundle\FrameworkBundle\Entity\DocumentInterface;
  use Doctrine\ORM\Mapping as ORM;
  
  /**
  * Category
  *
  * @ORM\Table(name="listings_categories")
  * @ORM\Entity(repositoryClass="Bundle\AdvertBundle\Repository\CategoryRepository")
  */
  class Category extends BaseEntity implements DocumentInterface
  {
  
  /**
   * @var integer
   *
   * @ORM\Column(name="id", type="integer", precision=0, scale=0, nullable=false, unique=false)
   * @ORM\Id
   * @ORM\GeneratedValue(strategy="IDENTITY")
   */
  private $id;
  
  /**
   * @var string
   *
   * @ORM\Column(name="title", type="string", length=255, precision=0, scale=0, nullable=false, unique=false)
   */
  private $title;
  
  /**
   * @var string
   *
   * @ORM\Column(name="slug", type="string", length=255, precision=0, scale=0, nullable=false, unique=false)
   */
  private $slug;
  
  /**
   * @var string
   *
   * @ORM\Column(name="summary", type="text", precision=0, scale=0, nullable=false, unique=false)
   */
  private $summary;
  
  /**
   * @var integer
   *
   * @ORM\Column(name="parent", type="integer", precision=0, scale=0, nullable=false, unique=false)
   */
  private $parent;
  
  /**
   * @var \Doctrine\Common\Collections\Collection
   *
   * @ORM\OneToMany(targetEntity="ListingItem", mappedBy="category")
   */
  private $listings;
  
  /**
   * @var string
   *
   * @ORM\Column(name="thumbnail", type="string", length=255, precision=0, scale=0, nullable=true, unique=false)
   */
  private $thumbnail;
  
  /**
   * @var string
   *
   * @ORM\Column(name="form_template", type="string", length=255, precision=0, scale=0, nullable=true, unique=false)
   */
  private $formTemplate;
  
  
  private $children;
  
  
  private $totalChildren;
  
  private $totaListing;
  
  
  /* Getters and Setters*/
  
  
  }
  </code></pre>
  
  <p>Listing Entity</p>
  
  <pre><code>&lt;?php
  namespace Bundle\AdvertBundle\Entity;
  
  use Doctrine\ORM\Mapping as ORM;
  use Bundle\FrameworkBundle\Entity\DocumentInterface;
  use Bundle\FrameworkBundle\Entity\BaseEntity;
  
  
  /**
  * @ORM\Entity (repositoryClass="Bundle\AdvertBundle\Repository\ListingItemRepository")
  * @ORM\Table(name="listings")
  * @ORM\InheritanceType("SINGLE_TABLE")
  * @ORM\DiscriminatorColumn(name="listing_type", type="string")
  * @ORM\DiscriminatorMap({
   "forsale" = "ForsaleItem", 
    "service" = "ServiceItem",
   "job" = "JobItem",
   "realestate" = "RealEstateItem",
   "land" = "LandItem",
  "house" = "HouseItem"
  })
  */
  abstract class ListingItem extends BaseEntity
  {
  /**
   * @var integer
   *
   * @ORM\Column(name="id", type="integer", precision=0, scale=0, nullable=false, unique=false)
   * @ORM\Id
   * @ORM\GeneratedValue(strategy="IDENTITY")
   */
  protected $id;
  
  /**
   * @var string
   *
   * @ORM\Column(name="title", type="string", length=255, precision=0, scale=0, nullable=false, unique=false)
   */
  protected $title;
  
  /**
   * @var string
   *
   * @ORM\Column(name="slug", type="string", length=255, precision=0, scale=0, nullable=false, unique=false)
   */
  protected $slug;
  
  
  /**
   * @var string
   *
   * @ORM\Column(name="price", type="decimal", precision=0, scale=2, nullable=false, unique=false)
   */
  protected $price;
  
  
  /**
   * @var string
   *
   * @ORM\Column(name="description", type="text", precision=0, scale=0, nullable=false, unique=false)
   */
  protected $description;
  
  /**
   * @var string
   *
   * @ORM\Column(name="thumbnail", type="string", length=255, precision=0, scale=0, nullable=true, unique=false)
   */
  protected $thumbnail;
  
  /**
   * @var string
   *
   * @ORM\Column(name="status", type="string", length=100, precision=0, scale=0, nullable=false, unique=false)
   */
  protected $status;
  
  /**
   * @var \DateTime
   *
   * @ORM\Column(name="date_created", type="datetime", precision=0, scale=0, nullable=true, unique=false)
   */
  protected $dateCreated;
  
  /**
   * @var \DateTime
   *
   * @ORM\Column(name="date_approved", type="datetime", precision=0, scale=0, nullable=true, unique=false)
   */
  protected $dateApproved;
  
  /**
   * @var \Bundle\AdvertBundle\Entity\Category
   *
   * @ORM\ManyToOne(targetEntity="Bundle\AdvertBundle\Entity\Category", inversedBy="listings")
   * @ORM\JoinColumns({
   *   @ORM\JoinColumn(name="category_id", referencedColumnName="id", nullable=true)
   * })
   */
  protected $category;
  
  /**
   * @var \Bundle\UserBundle\Entity\User
   *
   * @ORM\ManyToOne(targetEntity="Bundle\UserBundle\Entity\User", inversedBy="listings")
   * @ORM\JoinColumns({
   *   @ORM\JoinColumn(name="user_id", referencedColumnName="id", nullable=true)
   * })
   */
  protected $user;
  
  /**
   * @var string
   *
   * @ORM\Column(name="name", type="string", length=255, precision=0, scale=0, nullable=false, unique=false)
   */
  protected $name;
  
  /**
   * @var string
   *
   * @ORM\Column(name="email", type="string", length=255, precision=0, scale=0, nullable=false, unique=false)
   */
  protected $email;
  
  /**
   * @var string
   *
   * @ORM\Column(name="contact_number", type="string", length=255, precision=0, scale=0, nullable=false, unique=false)
   */
  protected $contactNumber;
  
  /**
   * @var \Bundle\LocationBundle\Entity\Country
   *
   * @ORM\ManyToOne(targetEntity="Bundle\LocationBundle\Entity\Country")
   * @ORM\JoinColumns({
   *   @ORM\JoinColumn(name="country_id", referencedColumnName="id", nullable=true)
   * })
   */
  private $country;
  
  /**
   * @var \Bundle\LocationBundle\Entity\State
   *
   * @ORM\ManyToOne(targetEntity="Bundle\LocationBundle\Entity\State")
   * @ORM\JoinColumns({
   *   @ORM\JoinColumn(name="state_id", referencedColumnName="id", nullable=true)
   * })
   */
  private $state;
  
  /**
   * @var \Bundle\LocationBundle\Entity\City
   *
   * @ORM\ManyToOne(targetEntity="Bundle\LocationBundle\Entity\City")
   * @ORM\JoinColumns({
   *   @ORM\JoinColumn(name="city_id", referencedColumnName="id", nullable=true)
   * })
   */
  private $city;
  
  
  protected $dateformat = 'd-M-Y';
  
  }
  </code></pre>
  
  <p>Location Entity
      
  
  <pre><code>use Doctrine\ORM\Mapping as ORM;
  
  /**
  * @ORM\Entity (repositoryClass="Bundle\LocationBundle\Repository\LocationRepository")
  * @ORM\Table(name="locations")
  * @ORM\InheritanceType("SINGLE_TABLE")
  * @ORM\DiscriminatorColumn(name="location_type", type="string")
  * @ORM\DiscriminatorMap({
  "country" = "Country", 
  "state" = "State",
  "city" = "City"
  })
  */
  abstract class Location
  {
  /**
   * @var integer
   *
   * @ORM\Column(name="id", type="integer", precision=0, scale=0, nullable=false, unique=false)
   * @ORM\Id
   * @ORM\GeneratedValue(strategy="IDENTITY")
   */
  protected $id;
  
  /**
   * @var string
   *
   * @ORM\Column(name="name", type="string", length=255, precision=0, scale=0, nullable=false, unique=false)
   */
  protected $name;
  
  /**
   * @var string
   *
   * @ORM\Column(name="country_code", type="string", length=3, precision=0, scale=0, nullable=true, unique=false)
   */
  protected $countryCode;
  
  /**
   * @var string
   *
   * @ORM\Column(name="slug", type="string", length=255, precision=0, scale=0, nullable=false, unique=false)
   */
  protected $slug;
  
  /**
   * @var integer
   *
   * @ORM\Column(name="geoname_id", type="integer", precision=0, scale=0, nullable=false, unique=false)
   */
  protected $geonameId;
  
  /**
   * @var string
   *
   * @ORM\Column(name="lng", type="string", length=255, precision=0, scale=0, nullable=false, unique=false)
   */
  
  protected $lng;
  
  /**
   * @var string
   *
   * @ORM\Column(name="lat", type="string", length=255, precision=0, scale=0, nullable=false, unique=false)
   */
  protected $lat;
  
  
  /**
   * @var \Doctrine\Common\Collections\Collection
   *
   * @ORM\OneToMany(targetEntity="Bundle\LocationBundle\Entity\Location", mappedBy="parent")
   */
  protected $children;
  
  /**
   * @var \Bundle\LocationBundle\Entity\Location
   *
   * @ORM\ManyToOne(targetEntity="Bundle\LocationBundle\Entity\Location", inversedBy="children")
   * @ORM\JoinColumns({
   *   @ORM\JoinColumn(name="parent_id", referencedColumnName="id", nullable=true)
   * })
   */
  protected $parent;
  
  /**
   * @var string
   *
   * @ORM\Column(name="currency_symbol", type="string", length=100, precision=0, scale=0, nullable=true, unique=false)
   */
  protected $currencySymbol;
  
  /* getters and setters*/
  }
  </code></pre>
  
  <p>Country Entity
     
  
  <pre><code>namespace Bundle\LocationBundle\Entity;
  
  use Bundle\FrameworkBundle\Entity\DocumentInterface;
  use Doctrine\ORM\Mapping as ORM;
  
  /**
   * Country
   *
   * @ORM\Table(name="locations")
   * @ORM\Entity(repositoryClass="Bundle\LocationBundle\Repository\LocationRepository")
   */
  class Country extends Location
  {
  
  /**
   * Set countryCode
   *
   * @param string $countryCode
   * @return Country
   */
  public function setCountryCode($countryCode)
  {
      $this-&gt;countryCode = $countryCode;
  
      return $this;
  }
  
  /**
   * Get countryCode
   *
   * @return string 
   */
  public function getCountryCode()
  {
      return $this-&gt;countryCode;
  }
  }
  </code></pre>
  
  <p>State and city entites are just like the country</p>